on 05-29-2008 12:30 PM
Hi,
I'm trying to use two "Member Of" expressions for a predicate, as in the following query:
SELECT si FROM SubIn si WHERE (:topicA MEMBER si.topics) OR (:topicB MEMBER si.topics)
The single items (topicA, topicB) which should be tested for membership in a collection are passed as parameters to the query and are proper entities. However, it seems that the constructed query contains syntax errors; I attached the "formatted" error message below. Please let me know if my formatting of the message is distracting and I can attach the plain message. Any hints as to why the final query is not well-formed would be greatly appreciated.
Cheers,
Felix
Caused by: com.sap.sql.log.OpenSQLException: The SQL statement
"SELECT ...
WHERE ((?) IN
(SELECT "A_1"."ID" FROM "TOPICS" "A_1", "J_SI_T" "A_2" WHERE "A_1"."ID" = "A_2"."T_ID" AND "SI"."ID" = "A_2"."SI_ID")
OR (?) IN
(SELECT "A_3"."ID" FROM "TOPICS" "A_3", "J_SI_T" "A_4" WHERE "A_3"."ID" = "A_4"."T_ID" AND "SI"."ID" = "A_4"."SI_ID"))"
contains the syntax error[s]:
- 1:251 - the IN predicate
? IN (SELECT "A_1"."ID" FROM "TOPICS" "A_1", "J_SI_T" "A_2" WHERE "A_1"."ID" = "A_2"."T_ID" AND "SI"."ID" = "A_2"."SI_ID")
contains a host variable (parameter marker) as the test value.
- 1:419 - the IN predicate
? IN (SELECT "A_3"."ID" FROM "TOPICS" "A_3", "J_SI_T" "A_4" WHERE "A_3"."ID" = "A_4"."T_ID" AND "SI"."ID" = "A_4"."SI_ID")
contains a host variable (parameter marker) as the test value.
Hi Felix,
thanks for spotting this issue: the generated SQL hits a limitation of Open SQL. I think we'd better generate an EXISTS-subquery instead of the IN-subquery.
As a workaround, I suggest that you use a native query instead:
... where exists (select TOPIC.ID
from TOPIC join J_SI_T on TOPIC.ID = J_SI_T.T_ID
where J_SI_T.SI_ID = SI.ID and TOPIC.ID = ?) or
exists (select TOPIC.ID
from TOPIC join J_SI_T on TOPIC.ID = J_SI_T.T_ID
where J_SI_T.SI_ID = SI.ID and TOPIC.ID = ?)
I think this could be simplified to
... where exists (select J_SI_T.SI_ID from where J_SI_T.SI_ID = SI.ID and J_SI_T.T_ID IN (?, ?))
We'll consider to fix this issue.
I hope this helps,
Adrian
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
90 | |
10 | |
10 | |
10 | |
7 | |
7 | |
6 | |
5 | |
4 | |
3 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.