Eclipse Community Forums: EclipseLink » use of a literal for EXISTS expressions

Help

Home

Home » Eclipse Projects » EclipseLink » use of a literal for EXISTS expressions

Show: Today's Messages :: Show Polls :: Message Navigator

use of a literal for EXISTS expressions [message #855832]

Wed, 25 April 2012 06:20

Ari Meyer

Messages: 136
Registered: July 2009

Senior Member

Hi,

I have this delete statement, which failed to parse:

DELETE FROM RmsImportRun r
WHERE NOT EXISTS (SELECT 1 FROM RmsProjwbs pw WHERE pw.rmsImportRun = r)
AND NOT EXISTS (SELECT 1 FROM RmsTask t WHERE t.rmsImportRun = r)
AND NOT EXISTS (SELECT 1 FROM RmsTaskactv ta WHERE ta.rmsImportRun = r)
AND NOT EXISTS (SELECT 1 FROM RmsTaskmemo tm WHERE tm.rmsImportRun = r)
AND NOT EXISTS (SELECT 1 FROM RmsTaskrsrc tr WHERE tr.rmsImportRun = r)
AND NOT EXISTS (SELECT 1 FROM RmsUdfvalue u WHERE u.rmsImportRun = r)

javax.persistence.PersistenceException: Exception [EclipseLink-8025]
(Eclipse Persistence Services - 2.3.2.v20111125-r10461):
org.eclipse.persistence.exceptions.JPQLException
Exception Description: Syntax error parsing the query
....
line 3, column 28: unexpected token [1].
Internal Exception: NoViableAltException(95@[1292:16: ( DISTINCT )?])

When I change the '1' to the alias, it works fine:
DELETE FROM RmsImportRun r
WHERE NOT EXISTS (SELECT pw FROM RmsProjwbs pw WHERE pw.rmsImportRun = r)
AND NOT EXISTS (SELECT t FROM RmsTask t WHERE t.rmsImportRun = r)
AND NOT EXISTS (SELECT ta FROM RmsTaskactv ta WHERE ta.rmsImportRun = r)
AND NOT EXISTS (SELECT tm FROM RmsTaskmemo tm WHERE tm.rmsImportRun = r)
AND NOT EXISTS (SELECT tr FROM RmsTaskrsrc tr WHERE tr.rmsImportRun = r)
AND NOT EXISTS (SELECT u FROM RmsUdfvalue u WHERE u.rmsImportRun = r)

That said, I see this example in Pro JPA 2, p. 225:

SELECT e
FROM Employee e
WHERE EXISTS (SELECT 1
FROM Phone p
WHERE p.employee = e AND p.type = 'Cell')

This query returns all the employees who have a cell phone number. This
is also an example of a subquery that returns a collection of values.
The EXISTS expression in this example returns true if any results are
returned by the subquery. Returning the literal 1 from the subquery is a
standard practice with EXISTS expressions because the actual results
selected by the subquery do not matter; only the number of results is
relevant.

So shouldn't the first version with the literal also be valid?

Thanks,
Ari

Report message to a moderator

Re: use of a literal for EXISTS expressions [message #857390 is a reply to message #855832]

Thu, 26 April 2012 13:50

James Sutherland

Messages: 1939
Registered: July 2009
Location: Ottawa, Canada

Senior Member

SELECT 1 should be valid. Can you try the latest 2.4 milestone to see if the issue is fixed.
If not, please log a bug.

James : Wiki : Book : Blog : Twitter

Report message to a moderator

Re: use of a literal for EXISTS expressions [message #859518 is a reply to message #857390]

Fri, 27 April 2012 17:20

Ari Meyer

Messages: 136
Registered: July 2009

Senior Member

Thanks, James -- will do.
Ari

Report message to a moderator

Re: use of a literal for EXISTS expressions [message #870151 is a reply to message #857390]

Mon, 07 May 2012 04:40

Ari Meyer

Messages: 136
Registered: July 2009

Senior Member

You're right, James -- it works fine with the 2.4 milestone build. When
do you expect 2.4 to be released?

Also, minor FYI: http://www.eclipse.org/eclipselink/releases/ is out of
sync with http://www.eclipse.org/eclipselink/.

Thanks,
Ari

Report message to a moderator