SELECT clause in FROM clause in Hibernate

bvikram_singh · **Joined:** Sat Jul 30, 2005 7:44 am **Posts:** 15

Hi everybody!

I am using hibernate3.0 to retrieve data from database. But I am facing problem in using select clause in From clause. for example:

Code:

select 

sum(sTotal) + sum(Disc) as Total

From (
select disc.sDiscount as Disc from discount as disc,
select pos.Total as sTotal from PosTables as pos
) 

This query is not original one but it will use select clause in from clause, as it is given above. I have doubt tht this functionlity is not supported in hibernate.Please let me know whether such query is possible in HQL or not . Thanks in advance

pksiv · **Posted:** Thu Aug 11, 2005 9:28 am

Have you tried running this ? What results do you get ?

bvikram_singh · **Joined:** Sat Jul 30, 2005 7:44 am **Posts:** 15

Hi preston, thanks for replying I tried to run it but I am getting

unexpected token: select org.hibernate.hql.ast.QuerySyntaxError: unexpected token: select near line 1, column 31

Does hibernate query support select clause in from clause? any help will be greatly appreciated

arizzi · **Joined:** Thu Sep 04, 2003 10:43 am **Posts:** 61

Does hibernate query support select clause in from clause? any help will be greatly appreciated[/quote]

I've a similar problem and I workarounded this way:

String sqlQuery = "SELECT COUNT(*) FROM ( " +
myClass.translateHQL2SQL( innerQuery ) +
" )";

And then executed as a SQL query.

and to translate HQL to SQL you can you something like:

ASTQueryTranslatorFactory cqt = new ASTQueryTranslatorFactory();
QueryTranslator qt = cqt.createQueryTranslator(HQL, new HashMap(), (SessionFactoryImplementor)sf);
qt.compile(new HashMap(), false );
String result = qt.getSQLString();

Of course, I will appreciate to write it all in HQL, but Hibernate do not compile such a query.
Hope this helps.