| use of a literal for EXISTS expressions [message #855832] |
Wed, 25 April 2012 02: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
|
|
|
] 
Search
Help
Register
Login
Home
