Hello,

In my HQL query I have the following CASE statement inside an aggregate SUM function:




The problem is when hibernate generates the SQL code it removes all the white spaces from inside the case statement and generates the following:




Which of course causes the query to fail. I am using Hibernate 2 and PostgreSQL Dialect. Any help will be greatly appreciated. Thanks.

This is really bizarre, it looks like Hibernate (version 2 at least) will remove all white spaces from anything inside an aggregate function.

For example:



will output the SQL:




Can someone please verify this and let me know why does Hibernate do this and if there is any workaround around it ?

Thanks,

Anybody ?

Sorry If I am being impatient here but this is a major blocker for me and I hit the wall with it so I will really appreciate any help.

Thanks,

Are you sure you can use case in an aggregate function? It doesn't make much sense to me.


Farzad-

Yes you can. I run the SQL query against my database with no problems. As a matter of fact this is the preferred way of creating cross tab queries and pivot tables in SQL.

http://www.simple-talk.com/sql/t-sql-pr ... es-in-sql/

Thanks for the reference. One test you can do is to test the same thing with a native query and see how it reacts. Let me know the results.


Farzad-

I found the solution!

Its not the most elegant but it works.

Use a comment instead of whitespace to delimit your tokens. For example:



becomes



Its ugly but it works for now!

Bashar,

I have hit the wall too with crosstab queries! Would you be kind enough to explain how you achieved this?

Thanks
Samuel