Hi,

I am having a problem executing an SQLQuery, with Hibernate 3.05. This is a (simplified) code fragment that exhibits the problem:

String SQL = "SELECT RN, TASK_ID AS {UT.TASK_ID}, USER_ID AS {UT.USER_ID}, EVENT_GROUP AS {UT.EVENT_GROUP} FROM (SELECT ROW_NUMBER() OVER (ORDER BY T.COMPLETION_DT) AS RN, T.TASK_ID AS TASK_ID, T.USER_ID AS USER_ID, E.EVENT_GROUP AS EVENT_GROUP FROM USER_TASKS T, EVENT E WHERE E.EVENT_ID=T.EVENT_ID) WHERE RN > 0 and RN <= 50";
SQLQuery lQuery = pHibernateSession.createSQLQuery(lSQL);
lQuery.addEntity("UT", <BusinessObject>.class);
return lQuery.list();

When I execute this, the following exception is thrown:

org.hibernate.QueryException: No column name found for property [TASK_ID] [SELECT RN, TASK_ID AS {UT.TASK_ID}, USER_ID AS {UT.USER_ID}, EVENT_GROUP AS {UT.EVENT_GROUP} FROM (SELECT ROW_NUMBER() OVER (ORDER BY T.COMPLETION_DT) AS RN, T.TASK_ID AS TASK_ID, T.USER_ID AS USER_ID, E.EVENT_GROUP AS EVENT_GROUP FROM USER_TASKS T, EVENT E WHERE E.EVENT_ID=T.EVENT_ID) WHERE RN > 0 and RN <= 50]
at org.hibernate.loader.custom.SQLQueryParser.resolveProperties(SQLQueryParser.java:195)
at org.hibernate.loader.custom.SQLQueryParser.substituteBrackets(SQLQueryParser.java:131)
at org.hibernate.loader.custom.SQLQueryParser.process(SQLQueryParser.java:76)
at org.hibernate.loader.custom.SQLCustomQuery.<init>(SQLCustomQuery.java:160)
at org.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:144)

I haven't included the Hibernate mapping file, but as you would
expect there is a property called TaskId mapped to a column named
TASK_ID in that mapping file.

I think the problem is related to the use of the {} notation in the SQL statement, and the nested
SELECT. Am I using the brackets incorrectly, or is there a limitation in the Hibernate SQL parser ?

Many thanks in advance
Julian