SQL to Hibernate Query Language (HQL) - missing IN

Question

Desired SQL statement to convert

select * from tableA where columnA is not null 
order by cast(columnA as int), columnB
union all
select * from app_data_program where columnA is null order by columnB

My HQL attempt:

From TableA a where a.columnA is not null 
order by cast(a.columnA as int), a.columnB 
union all TableA b where b.columnA is not null order by b.columnB

When I converted the HQL to SQL to test as a result, i get the following:

SQL Error: Missing IN or OUT parameter at index:: 1

You have a "?" in your query where a parameter should go. Are you setting that parameter before trying to execute your statement? — Becuzz, Dec 04 '13 at 21:30
What DB are you using? Googling that error looks like it is some version of Oracle. (Google also gives back a bunch of links that might be worth looking into.) — Becuzz, Dec 04 '13 at 21:45
Maybe this can help: http://stackoverflow.com/questions/18257757/how-to-execute-query-with-union-in-hibernate — user1807337, Dec 04 '13 at 21:45

score 0 · Accepted Answer · answered Dec 05 '13 at 08:26

0

HQL doesn't allow use UNION ALL sql construct.

You must execute two different queries and then you can merge the results of them.

So you'll have:

First query:

From TableA a where a.columnA is not null 
order by cast(a.columnA as int), a.columnB

Second query:

TableA b where b.columnA is not null order by b.columnB

answered Dec 05 '13 at 08:26

Joe Taras

15,166
7
42
55

wont the merging create duplicate results? – bouncingHippo Dec 05 '13 at 14:13
Your query use UNION ALL (not discard duplicated). You can discard duplicated in a Java loop – Joe Taras Dec 05 '13 at 14:30

SQL to Hibernate Query Language (HQL) - missing IN

1 Answers1