I am using hibernate 2.1 with Oracle 8.1.7. I want to count the various types of values in a column (called status), but one of the values is null. A simple "...count(status) group by status" will not work because the null are not compared and counted. If I run a the following query, I get the expected results:
select count((nvl(status, 1)), status from mytable group by status;

When I put in simple HQL it say undefined alias nvl. Then I tried to use the native SQL with the aliases, hibernate complains that the command is not properly terminated. The query that I have in the code is:

hibSess.createSQLQuery("select count(nvl({st}.status, 1)), " +
"{st}.status from " + sourceRow.getTableName() +
" as {st} group by {st}.status ", "st", obj.getClass());

The query that is generated does not run. The "as" is superflous. When I remove that from the query hibernate says "invalid column name".

I want to do this simple query and I am making it difficult for myself as I am don't seem to be on the right track. Any help will be appreciated.

Thanks!