row to long problem

[Sven Köhler]

hi,

it's me again with a "row to long"-problem, but i don't know why this happens, and it might even be BUG.

SELECT rowno, jh.id_jh, jh.name, jh.plz, ortl.ort, b.lvb, ort.id_bundesland, c.bundesland
FROM data_jh jh
LEFT JOIN data_orte ort ON (ort.id_ort=jh.id_ort)
LEFT JOIN data_orte_lang ortl ON (ortl.id_ort=ort.id_ort AND ortl.id_sprache=?)
LEFT JOIN base_landesverbände b ON (b.id_lvb=ort.id_lvb)
LEFT JOIN base_bundesländer_lang c ON (c.id_bundesland=ort.id_bundesland AND c.id_sprache=?)
WHERE
1: (lower(jh.name) LIKE ? OR lower(jh.ortsteil) LIKE ? OR lower(jh.kopfzeile) LIKE ? OR lower(ortl.ort) LIKE ?)
AND
2: (lower(jh.name) LIKE ? OR lower(jh.ortsteil) LIKE ? OR lower(jh.kopfzeile) LIKE ? OR lower(ortl.ort) LIKE ?)
AND
3: (lower(jh.name) LIKE ? OR lower(jh.ortsteil) LIKE ? OR lower(jh.kopfzeile) LIKE ? OR lower(ortl.ort) LIKE ?)
ORDER BY lower(c.bundesland), lower(jh.name)

I numbered three lines in the WHERE clause. If i delete the third condition, the statement works, if i add it again, it says "row too long".
I guess that SAPDB is putting the columns used in the WHERE clause into the result-table resulting in some kind of hidden columns, and the result-tables's rows are getting to long somehow.

The bug/problem is, that SAPDB seems to put the columns like lower(jh.name) into the result-table three times each.

i wasn't abled to reproduce the fact with smaller statements, so i guess it must be something about the above statement.

i also tried to explicitly select the columns into the result set, but statements like
SELECT lower(column) as test FROM table WHERE test like 'b%'
fail, because the column test cannot be accessed from the WHERE clause. Even HAVING failes.

So what am i supposed to do?

Thx
Sven


_______________________________________________
sapdb.general mailing list
[EMAIL PROTECTED]
http://listserv.sap.com/mailman/listinfo/sapdb.general