condition on outer join

gajini · **Posted:** Tue May 30, 2006 4:18 pm

Need help with Hibernate? Read this first:
http://www.hibernate.org/ForumMailingli ... AskForHelp

Hibernate version: 3.1

Hi,

I have problem in executing the qry with condition on outer join.
Below is the qry i am trying to execute. Getting error in 3rd line.

from table1 as tbl1
inner join t1.table2 as tbl2
left outer join tbl1.table3 as tbl3 with (tbl3.dt=12 and tbl2.cd=abc)

[without tbl2 properties, its working]

Any help/suggestions/alternate ways needed seriously.

Thanks
gajini

jbosseur · **Posted:** Tue May 30, 2006 5:45 pm

gajini wrote:

from table1 as tbl1
inner join t1.table2 as tbl2
left outer join tbl1.table3 as tbl3 with (tbl3.dt=12 and tbl2.cd=abc)

Shouldn´t this be:

Code:

 from table1 as tbl1
 inner join tbl1.table2 as tbl2 WITH (tbl2.cd='abc')
 left outer join tbl1.table3 as tbl3 with (tbl3.dt=12) 

changed t1 to tbl1 and with to table 2
maybe.....??????

pepelnm · **Posted:** Wed May 31, 2006 3:29 am

When you upgrade your hibernate version (3.1.2 or greater) you will need something like:

Code:

from 
   table1 as tbl1
   inner join tbl1.table2 as tbl2
   left outer join tbl1.table3 as tbl3 
      with tbl3.dt = 12
where
   tbl3 is null
   or
   tbl2.cd = abc

gajini · **Posted:** Wed May 31, 2006 10:27 am

ok, let's have this qry. how to do this ?

from table1 as tbl1
inner join tbl1.table2 as tbl2
left outer join tbl1.table3 as tbl3 with ((tbl3.dt=12 and tbl2.cd=abc) or (tbl3.dt=13 and tbl2.cd<>abc))

pepelnm · **Posted:** Wed May 31, 2006 6:35 pm

Code:

from
   table1 as tbl1
   inner join tbl1.table2 as tbl2
   left outer join tbl1.table3 as tbl3
where
   tbl3 is null
   or
   (tbl3.dt = 12 and tbl2.cd = abc) or (tbl3.dt = 13 and tbl2.cd <> abc)

does it works?