Eclipse Community Forums: EclipseLink » null in or part of where clause

Help

Home

Home » Eclipse Projects » EclipseLink » null in or part of where clause

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

null in or part of where clause [message #388103]

Thu, 21 May 2009 14:21

Ben Anderson

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

Report message to a moderator

Re: null in or part of where clause [message #388116 is a reply to message #388103]

Mon, 25 May 2009 17:29

James Sutherland

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

Report message to a moderator

Re: null in or part of where clause [message #388433 is a reply to message #388116]

Tue, 26 May 2009 14:31

Ben Anderson

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 = ?)

Report message to a moderator

Previous Topic:	Horizontal partitions/Shards with EclipseLinks and JPA 2.0
Next Topic:	left join implementation for informix problem

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 25 00:20:38 GMT 2024

.:: Contact :: Home ::.

Breadcrumbs

Sign up to our Newsletter