I wonder if anyone can tell me how to use a function such as to_char in Oracle in a native SQL query?

I want to execute the following query:

Select sum(t.personligtmeddelandetyp) as PERSONLI2_0_, to_char(t.skapatdatum, 'YYYY') as AVSENDARE0_, to_char(t.skapatdatum, 'MM') as MOTTAGARE0_ from personligtmeddelandesok t group by to_char(t.skapatdatum, 'YYYY'), to_char(t.skapatdatum, 'MM')

It works fine when a execute it in a SQL*plus.

When I use session.createSQLQuery with the following input

session.createSQLQuery("Select sum({t}.personligtmeddelandetyp) as {t.antal}, to_char({t}.skapatdatum, 'YYYY') as {t.ar}, to_char({t}.skapatdatum, 'MM') as {t.manad} from personligtmeddelandesok {t} group by to_char({t}.skapatdatum, 'YYYY'), to_char({t}.skapatdatum, 'MM')", "t", DialogenStatistikAntalPerManadArTO.class);

I get a SQLException with a message that a column name is illegal, since I have tried the same sql query in Sql*plus I know that tere is nothing wrong with the column names, but i suspect that it has to do with the function to_char. I know that according to the JDBC spec scalar functions should be indicated with {fn to_char(t.skapatdatum, 'YYYY')} but how do I accomplish that when hibernate expect everything between {} to be a table alias. I have tried this in normal HQL as well but there I get errors because of the scalar function in the select part of the query.

Hope that someone can tell me if my query is possible.

You can't, please look (and vote) this issue on Jira:
http://opensource.atlassian.com/projects/hibernate/secure/ViewIssue.jspa?key=HB-673
If you need alternative ways, look at my previous posts.
Ciao.
L.