Re: [eclipselink-users] LEFT OUTER JOIN not being honored

[James Sutherland <jamesssss@xxxxxxxxx>]

Make sure you are setting your database platform to JavaDB or Derby
("eclipselink.target-database"), it looks like you are using the
SybasePlatform?  The outer-join should be printed in the from clause for
Derby.

Also try the latest version, as there my be an issue that you seem to be
seeing that was fixed.



cmathrusse wrote:
> 
> I modified my JPQL query to the following:
> <BR /><BR />
> <code>
> select o 
> <BR />
> from NotificationDefinition o 
> <BR />LEFT OUTER JOIN o.sieve s 
> <BR />where ((o.resource = :resource and o.severity is null) or
> (o.resource is null and o.severity = :severity)) 
> <BR />and ((s.include = true and s.organization = :organization) or
> (s.include = false and s.organization <> :organization) or (s is null))
> </BR />
> </code>
> <BR /><BR />
> Which produces what looks to be more accurate SQL, but now I get a syntax
> error on the generated SQL:
> <<BR /><BR />
> <code>
> SELECT
>   t0.id
> , t0.mod_date
> , t0.create_date
> , t0.version
> , t0.sieve
> , t0.mod_by
> , t0.resource_id
> , t0.severity
> , t0.distribution_id
> <BR />
> FROM
>   notification_definition t0
> LEFT OUTER JOIN distribution_sieve t1
> ON
>   (
>     t1.id = t0.sieve
>   )
> <BR />
> WHERE
>   (
>     (
>       (
>         (
>           t0.resource_id = 'PRS_004'
>         )
>       AND
>         (
>           t0.severity IS NULL
>         )
>       )
>     OR
>       (
>         (
>           t0.resource_id IS NULL
>         )
>       AND
>         (
>           t0.severity = 'INFO'
>         )
>       )
>     )
> <BR />
>   AND
>     (
>       (
>         (
>           (
>             t1.include = 1
>           )
>         AND
>           (
>             t1.organization = 'AMER'
>           )
>         )
>       OR
>         (
>           (
>             t1.include = 0
>           )
>         AND
>           NOT
>           (
>             (
>               t1.organization = 'AMER'
>             )
>           )
>         )
>       )
>     OR
>       t0.sieve =* NULL
>     )
>   )
> </code>
> <BR /><BR />
> It appears that JavaDB does not support the (<code>t0.sieve =*
> NULL</code>) syntax.
> <BR /><BR />
> Is this a bug?
> <BR /><BR />
> Thanks for the help...
> 


-----
---
http://wiki.eclipse.org/User:James.sutherland.oracle.com James Sutherland 
http://www.eclipse.org/eclipselink/
 EclipseLink ,  http://www.oracle.com/technology/products/ias/toplink/
TopLink 
Wiki:  http://wiki.eclipse.org/EclipseLink EclipseLink , 
http://wiki.oracle.com/page/TopLink TopLink 
Forums:  http://forums.oracle.com/forums/forum.jspa?forumID=48 TopLink , 
http://www.nabble.com/EclipseLink-f26430.html EclipseLink 
Book:  http://en.wikibooks.org/wiki/Java_Persistence Java Persistence 
-- 
View this message in context: http://www.nabble.com/LEFT-OUTER-JOIN-not-being-honored-tp23341277p23373228.html
Sent from the EclipseLink - Users mailing list archive at Nabble.com.