| [not] exists [message #382789] |
Mon, 27 October 2008 14:30  |
 Tom Eugelink
Messages: 817
Registered: July 2009 |
Senior Member |
|
|
In an effort to create a certain JPQL I referred to the original Toplink JPA manuals. In the "JPQL Language Reference" it says:
----
10.2.5.13. JPQL Exists Expressions
An EXISTS expression is a predicate that is true only if the result of the subquery consists of one or more values and that is false otherwise. The syntax of an exists expression is
exists_expression ::= [NOT] EXISTS (subquery)
The use of the reserved word OF is optional in this expression.
Example:
SELECT DISTINCT auth FROM Author auth
WHERE EXISTS
(SELECT spouseAuthor FROM Author spouseAuthor WHERE spouseAuthor = auth.spouse)
The result of this query consists of all authors whose spouse is also an author.
----
So I took the example JPQL and replace the tables resulting in:
SELECT DISTINCT auth FROM Buyorder auth WHERE EXISTS (SELECT spouseAuthor FROM Delivery2Buyorder spouseAuthor WHERE spouseAuthor.iBuyorder = auth)
However, EclipseLink 1.0.1M2 makes this SQL out of it:
Call: SELECT DISTINCT t0.buyordernr, t0.order_addressnr, t0.delivery_addressnr, t0.pickup_addressnr, t0.contactnr, t0.currencynr, t0.enteredby_empnr, t0.relationnr, t0.delivery_relationnr, t0.stage, t0.buydate, t0.reason, t0.mededeling, t0.delivery_date, t0.weight, t0.lazylock, t0.financialcomplete, t0.freightbill, t0.bill, t0.freightletter, t0.freightmoney, t0.dwhmodified, t0.dwhby, t0.transport_relationnr, t0.packaging, t0.inserted FROM buyorder t0 WHERE EXISTS (SELECT ? FROM buyorder t2, delivery2buyorder t1 WHERE ((t1.buyordernr = t2.buyordernr) AND (t0.buyordernr = t2.buyordernr)))
bind => [1]
Note the bind and the question mark in the SQL. Naturally the database stumbled over this:
Internal Exception: java.sql.SQLException: A syntax error has occurred.
Error Code: -201
Not good. What is wrong?
Tom
|
|
|
|
|
|
|
Powered by
FUDForum. Page generated in 1.56298 seconds
] 
Search
Help
Register
Login
Home
