SQL Union in createSQLQuery

__newbie · **Joined:** Thu Aug 18, 2005 7:24 am **Posts:** 2

Hi, I'm trying to use createSQLQuery for doing a UNION SELECT but 'till now I have no success.

SQLQuery query = session.createSQLQuery("SELECT {t.*} FROM Test {t} UNION SELECT {n.*} FROM NewTable {n}");
query.addEntity("t", Test.class);
query.addEntity("n", NewTable.class);
List list = query.list();

Can anyone help me to figure out what's wrong?

Thanks

sven · **Posted:** Thu Aug 18, 2005 1:11 pm

What's your exception?
What's the SQL output?

Best regards
Sven

__newbie · **Joined:** Thu Aug 18, 2005 7:24 am **Posts:** 2

Hello this is the output... The strange about is that a time ago I tried the same SQL and it didn't bring me any error but the table was empty. Now that I add some data to one table the error comes back again.

Code:

Hibernate: SELECT t.id as id0_, t.name as name0_0_ FROM test t UNION SELECT n.id as id1_, n.name as name1_1_ FROM newtable n
org.hibernate.exception.GenericJDBCException: could not execute query
   at org.hibernate.exception.ErrorCodeConverter.handledNonSpecificException(ErrorCodeConverter.java:92)
   at org.hibernate.exception.ErrorCodeConverter.convert(ErrorCodeConverter.java:80)
   at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
   at org.hibernate.loader.Loader.doList(Loader.java:1560)
   at org.hibernate.loader.Loader.list(Loader.java:1540)
   at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:103)
   at org.hibernate.impl.SessionImpl.listCustomQuery(SessionImpl.java:1341)
   at org.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:151)
   at dao.TesteDAO.testeSQL(TesteDAO.java:168)
   at dao.TesteDAO.main(TesteDAO.java:204)
Caused by: java.sql.SQLException: Column 'id1_' not found.
   at com.mysql.jdbc.ResultSet.findColumn(ResultSet.java:2806)
   at com.mysql.jdbc.ResultSet.getInt(ResultSet.java:1551)
   at org.hibernate.type.IntegerType.get(IntegerType.java:26)
   at org.hibernate.type.NullableType.nullSafeGet(NullableType.java:77)
   at org.hibernate.type.NullableType.nullSafeGet(NullableType.java:68)
   at org.hibernate.loader.Loader.getKeyFromResultSet(Loader.java:722)
   at org.hibernate.loader.Loader.getRowFromResultSet(Loader.java:284)
   at org.hibernate.loader.Loader.doQuery(Loader.java:391)
   at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:210)
   at org.hibernate.loader.Loader.doList(Loader.java:1557)
   ... 6 more

sven · **Posted:** Thu Aug 18, 2005 2:58 pm

Which database are you using? Does it support UNION?
IMHO the SELECT statement looks fine.

Have you checked that these columns really exist in your tables?

Best regards
Sven