Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » EclipseLink » null in or part of where clause
null in or part of where clause [message #388103] Thu, 21 May 2009 14:21 Go to next message
Ben Anderson is currently offline Ben AndersonFriend
Messages: 2
Registered: July 2009
Junior Member
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 #388116 is a reply to message #388103] Mon, 25 May 2009 17:29 Go to previous messageGo to next message
James Sutherland is currently offline James SutherlandFriend
Messages: 1939
Registered: July 2009
Location: Ottawa, Canada
Senior Member

You need to use an outer-join for this.

i.e.
select o from ConveyanceMake as o left outer join o.owner owner where (
owner is null or owner.id = 9 )


James : Wiki : Book : Blog : Twitter
Re: null in or part of where clause [message #388433 is a reply to message #388116] Tue, 26 May 2009 14:31 Go to previous message
Ben Anderson is currently offline Ben AndersonFriend
Messages: 2
Registered: July 2009
Junior Member
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 = ?)
Previous Topic:Horizontal partitions/Shards with EclipseLinks and JPA 2.0
Next Topic:left join implementation for informix problem
Goto Forum:
  


Current Time: Wed Nov 26 02:03:44 GMT 2014

Powered by FUDForum. Page generated in 0.02051 seconds
.:: Contact :: Home ::.

Powered by: FUDforum 3.0.2.
Copyright ©2001-2010 FUDforum Bulletin Board Software