union?

netexplode · **Joined:** Wed Sep 10, 2003 2:02 pm **Posts:** 2

What's the best way in hql to get the result similar to what union clause does in sql?

I apologize for not being specific. it's a bit complicated to explain exactly what i am trying to do, so i just want to see if there's a simple solution to to obtain union like results. if not, i'll try to elaborate.

Thanks

gavin · **Posted:** Fri Sep 12, 2003 2:14 pm

Hibernate does not do unions.

max · **Posted:** Sat Sep 13, 2003 3:56 am

gavin wrote:

Hibernate does not do unions.

...but he could try to use the new createSQLQuery() to do this - here you express you query in sql (mixed with HQL property references instead of raw table names) to get your union...it's worth a shot ;)

loverde · **Posted:** Tue Apr 06, 2004 5:28 pm

Are there any plans to support union in either HQL or Criteria?

The situation that I encountered where it would be nice to support unions is admittedly a DB2 problem (and if anyone has an alternate approach that works in DB2 please respond).

Basically, I have a table that supports two columns, one with a value input by the user, and another with an alternate value determined by application specific means. Something like:

Code:

create table SAMPLE_TABLE (
     VALUE_COL VARCHAR(64), ALT_VALUE_COL VARCHAR(64) 
);

And I want to do a query to find all rows with either the value or alternate value are equal to 'FOO'. That is:

Code:

SELECT * FROM SAMPLE_TABLE WHERE VALUE_COL = 'FOO' OR ALT_VALUE_COL = 'FOO'

In DB2, there are two indexes on the table, one on each column. The problem is that DB2 in its ultimate wisdom refuses to use the indexes. Instead it opts to do a full table scan (DB2 V7 on the mainframe).

If I change the query to:

Code:

SELECT * FROM SAMPLE_TABLE WHERE VALUE_COL = 'FOO' UNION SELECT * FROM SAMPLE_TABLE WHERE ALT_VALUE_COL = 'FOO'

Then DB2 does in fact use both indexes to perform the query. So I need to make this change. The workaround for right now is to just use a SQL query, but it would be much more convenient to do this in HQL and/or via Criteria.

Actually in this particular case Criteria would be ideal since the operation is part of a generic inquiry screen where this is just one of many possible input options. I had to rip out the Criteria implementation and replace it with code that manually builds the SQL and uses the SQL query instead.

If you do consider the change, please take into account the need to apply the ordering to the UNION and not to the individual component results. That is, something like:

Code:

(SELECT * FROM SAMPLE_TABLE WHERE VALUE_COL = 'FOO' UNION SELECT * FROM SAMPLE_TABLE WHERE ALT_VALUE_COL = 'FOO') ORDER BY VALUE_COL

(In quick fix mode on the project right now, or I'd consider enhancing Criteria and submitting the diffs).
[/code]

gavin · **Posted:** Wed Apr 07, 2004 3:38 am

Surely this can be fixed by creating a more appropriate index.