Hibernate version:
3.0.5
Mapping documents:
Not meaningfull
Code between sessionFactory.openSession() and session.close():
SessionFactory sf = config.buildSessionFactory();
Session hibSession = sf.openSession();
String HQL4Tranlsate = "SELECT coalesce(sum(a.id), 0) FROM table1 AS a";
ClassicQueryTranslatorFactory cqt = new ClassicQueryTranslatorFactory();
QueryTranslator qt = cqt.createQueryTranslator(HQL4Tranlsate, new HashMap(), (SessionFactoryImplementor)sf);
qt.compile(new HashMap(), false );
String result = qt.getSQLString();
System.out.println(result);
hibSession.createQuery(HQL4Tranlsate).list();

Full stack trace of any exception that occurs:
No exception
Name and version of the database you are using:
Oracle 9.0.2.0
The generated SQL (show_sql=true):
See below
Debug level Hibernate log excerpt:

The problem is that the result of the QueryTransator is different from the SQL I get executing the query. In fact from the above "System.out.println(result);" i get:
select coalesce(sum(tstdacmain0_.id), 0) as col_0_0_ from tst_main tstdacmain0_

while if I execute the query I see:

Hibernate: select nvl(sum(tstdacmain0_.id), 0) as col_0_0_ from tst_main tstdacmain0_

Why there is this difference? Did I use the wrong method to translate HQL to SQL?
In my real case I have to use the QueryTranslator to build a part of a query which will be completed with another part in SQL (I can't write the whole query in HQL since Hibernate won't compile it), so, in few words I really need to have the correct translation usually made by Hibernate.
Thankfull for helps
Alessandro Rizzi

I wrote this message for anyone who will have the same problem as me:

My mistake was the use of old "classic" instead of AST. If you change the first line of code with:

ASTQueryTranslatorFactory cqt = new ASTQueryTranslatorFactory();

Everything will work.

Sorry for bothering the team with this question.

Ragards
Alessandro