| null in or part of where clause [message #388103] |
Thu, 21 May 2009 10:21  |
Eclipse User |
|
|
|
Hi,
I'm having a tricky time getting a query to work the way I would like.
The query is pretty simple:
select o
from ConveyanceMake as o
where ( o.owner is null or o.owner.id = 9 )
order by o.code
This returns no results. However, when I remove the second half of my
where clause, it does return results:
select o
from ConveyanceMake as o
where ( o.owner is null ) order by o.code
So, that made me think that the issue is with null making the entire where
statement null because I've encountered the same thing in sql. However,
according to this documentation:
http://java.sun.com/j2ee/tutorial/1_3-fcs/doc/EJBQL5.html#72 299
the first query should always be a superset of the second query. Am I
reading this wrong or is this not the right documentation to be looking
at? And as a side note, I'm wondering what the best way to rewrite my
query is.
Thanks much,
Ben
|
|
|
|
| Re: null in or part of where clause [message #388433 is a reply to message #388116] |
Tue, 26 May 2009 10:31  |
| Eclipse User |
|
|
|
Thanks for the reponse, James. 2 follow up questions.
1) Is that the right thing to do? The way I read the jpa spec, I wouldn't
expect an outer join to be necessary in ejb-ql (although I understand why
it is in sql).
2) Your solution doesn't work for oracle. Any idea why not?
The exception message:
Internal Exception: java.sql.SQLSyntaxErrorException: ORA-01719: outer
join operator (+) not allowed in operand of OR or IN
The relevant part of the sql:
(t1.OWNER_ID (+) = ? OR (t2.ID = ?)
|
|
|
Powered by
FUDForum. Page generated in 0.03619 seconds
Home