Hi, my problem is with subqueries. I want execute a subselect in the select clause. In SQL the sentence would be:

select
ee.nombre,ee.ape1,ee.ape2,
(select count(e.id_estado_emp) from itdk_empleado e
where e.ID_estado_emp=ee.ID_estado_emp
group by e.id_estado_emp)
from itdk_empleado ee

Well, this sentence in Oracle works fine, but in Hibernate i build the hql in this way:

hsql.append("select e.nombre as {empleado.nombre},e.ape1 as {empleado.ape1},e.ape2 as {empleado.ape2},(select count(ee.id_estado_emp) from ITDK_EMPLEADO ee ");

hsql.append("where ee.id_estado_emp=e.id_estado_emp group by ee.id_estado_emp) from ITDK_EMPLEADO e ");

and i use the createSQLQuery method to use SQL Native.

The generated SQL for Hibernate works fine in Oracle, but Hibernate give me the exception above.

Can i execute subqueries with this version of Hibernate?

Some help about this problem?

Thanks.
Hibernate version:
Hibernate 2.1.7
Mapping documents:

Code between sessionFactory.openSession() and session.close():

Full stack trace of any exception that occurs:
11:09:37,356 WARN [JDBCExceptionReporter] SQL Error: 17006, SQLState: null
11:09:37,356 ERROR [JDBCExceptionReporter] Nombre de columna no válido
11:09:37,387 WARN [JDBCExceptionReporter] SQL Error: 17006, SQLState: null
11:09:37,403 ERROR [JDBCExceptionReporter] Nombre de columna no válido
Name and version of the database you are using:

The generated SQL (show_sql=true):
11:16:03,072 INFO [STDOUT] Hibernate: select * from ( select e.nombre as NOMBRE0_,e.ape1 as APE10_,e.ape2 as APE20_,(select count(ee.id_estado_emp) from ITDK_EMPLEADO ee where ee.id_estado_emp=e.id_estado_emp group by ee.id_estado_emp) from ITDK_EMPLEADO e where 1=1 order by e.nombre,e.ape1,e.ape2 ) where rownum <= ?

Debug level Hibernate log excerpt:

I think you want something like this. Notice that I added an alias after the subselect. While Oracle would run the query, because there was no alias that mapped to an entity property, Hibernate has no idea what to do with the returned value.

select e.nombre as {empleado.nombre}, e.ape1 as {empleado.ape1}, e.ape2 as {empleado.ape2}, (select count(ee.id_estado_emp) from ITDK_EMPLEADO ee where ee.id_estado_emp=e.id_estado_emp group by ee.id_estado_emp) AS {empledao.xxxx} from ITDK_EMPLEADO e

Good luck,
Curtis ...