Hibernate version:
3.0

Hi There,

I am generating an SQL query in two ways

1. Using a Criteria - works fine.
2. Using a native SQL query using createSQLQuery - does not work.
Using this approach I get the error:

[OraDriver] Column not found <ID>.

This is the code that executes the statement and then gets the results:


Note that the query passed to the createSQLQuery is exactly the same as below except it has named params instead of the ? i.e. I dont use object notation just plain old SQL. The query works fine if the innermost select uses select * from A instead of naming the columns (which I have to do for other reasons).

BOTH SQL STATEMENTS ARE EXACTLY THE SAME as proven by the hibernate debug output. The statement also runs fine in toad, no errors. Here is the statement:

select * from ( select row_.*, rownum rownum_ from ( select this_.ID as ID240_0_, this_.DESCRIPTION as DESCRIPT3_240_0_, this_.A_TYPE_ID as A4_240_0_, this_.ASSIGNED_DATE as ASSIGNED5_240_0_, this_.ASSIGNED_USER_ID as ASSIGNED6_240_0_, this_.CLEARED_TIME as CLEARED7_240_0_, this_.D_SOURCE_ID as DATA8_240_0_, this_.E_R_LOSS as ESTIMATED9_240_0_, this_.FROM_TIME as FROM10_240_0_, this_.PARENT_ID as PARENT11_240_0_, this_.REPORTED_TIME as REPORTED12_240_0_, this_.SEVERITY as SEVERITY240_0_, this_.STATUS as STATUS240_0_, this_.TO_TIME as TO15_240_0_, this_.O_R_LOSS as ORIGINAL16_240_0_, this_.FILE_CAUSED as FILE17_240_0_, this_.FILE_CLEARED as FILE18_240_0_, this_.EXPECTED_TIME as EXPECTED19_240_0_, this_.ACTUAL as ACTUAL240_0_, this_.HISTORICAL as HISTORICAL240_0_, this_.SECOND_FILE_CAUSED as SECOND22_240_0_, this_.FIRST_ACTUAL_VALUE as FIRST23_240_0_, this_.SECOND_ACTUAL_VALUE as SECOND24_240_0_, this_.M_P_ID as MONITORED25_240_0_, this_.THRESHOLD_DESCRIPTION as THRESHOLD26_240_0_, this_.CLASS as CLASS240_0_ from RO_A this_ where this_.STATUS in (?, ?) order by this_.E_R_LOSS asc ) row_ ) where rownum_ <= ? and rownum_ > ?


Any ideas????

thanks a lot,

- Paul.

So you're sure you have a table called RO_A that has a column called ID?

yes of course.

I think the problem could be that I dont specify the like of:

{a.id} and {a.description} etc...

as placeholders. Instead I directly generate the SQL and send it into the query directly.

of course - if you don't specify aliases via either {} or using the same names as the one used in the mapping how is hibernate going to know that ID240_0_ is the alias *you* want to use ?

to be honest I find the documentation really confusing on the native SQL queries.

My table has a CLASS column since there are sublcasses etc.. I have not specificed in my query, using the correct alias notation, that I want to retrieve this column however now I get the error message:

[OraDriver] Column not found. <CLASS240_0_>

The bit between the < and > looks like an auto generated alias so how do I fix this?

thanks a lot.

I presume I have to add something like this to my query:

this_.CLASS as CLASS240_0_

Incidentally the only reason I need to do all this is because hibernate performs the unique result calculation AFTER the results have been retrieved from the database. I need to use pagination and hibernate quite simply DOES not support pagination where a one-to-many relationship exists between classes...very annoying.

Which part ?

This one ?

Note: if you list each property explicitly, you must include all properties of the class and its subclasses!

Which states you need to include *all* properties which also means the *discriminator* which is marked up via {[aliasname].class} which is also listed in the docs.

I'm not saying the docs are perfect just that patches are very welcome to make stuff clearer if some parts are missing.

...and you do know that in later hibernate 3's the alias injection is not needed at all as long as you return the same column names you have used to to map the classes with.[/i]