Union and outer joins in filter condition

newbe · **Joined:** Mon Sep 18, 2006 5:21 am **Posts:** 6

Hibernate version: 3.1.3

Hi,
I am trying to use Hibernate's filters. I have condition like:

Code:

myId in (select id from A union select id from B)

unfortunately in runtime if I apply this filter I am getting sql like:

Code:

select ... where table_.myId in (select id from A a table_.union select id from B b)

Simply Hibernate treats keyword "union" as a name of column in table instead as a keyword and prefixes it with "table_.".

Similar situation is if I have two outer joins in filter's condition. Hibernate is generateng sql like:

Code:

select ... from A a outer join ..., table_.B table_.b outer join ...

again tebale names of table and alias in second outer join are treated as a name of column table and are prefixed with "table_.".

Does Hibernate support "union" and multiply outer joins in filter's condition's subquery?

regards
John

kas · **Joined:** Mon May 03, 2004 11:53 pm **Posts:** 7

See Hibernate Users Faq - Advanced Problems at: http://www.hibernate.org/117.html#A20.

Does HQL support UNION (or INTERSECT)?
Not at this time. You may use a native SQL query for this.

kas · **Joined:** Mon May 03, 2004 11:53 pm **Posts:** 7

Hibernate is plannig to add support for unions in 3.2.1. They just released 3.2.0 GA on Monday 10.16.2006. See the following link related to unions:

http://opensource.atlassian.com/project ... 0?page=all

You can use unions in native sql.