HQL Query Problem

[Kishen Singh Punjabi]

Hi,

select * from ( select distinct NEW com.acme.example.CustomerDetailsDTO(c.id, c.status, c.address,c.code,cl.logTime) from CustomerDetails c , CustomerLogs cl some where... ) cust left join ( select * from CompanyDetails cd where cd.code='dell' ) comp where comp.code=cust.code order by comp.code desc;

- This query works fine in sql but in HQL when I use session.createQuery(sqlQuery) , i get error saying unexpected token (

Thanks in Advance!!

[Rob Spoor]

HQL and JPQL are not the same as SQL. They are far more limited, and don't support everything that's supported in SQL. What you have there is one of those things. But you can use a native query combined with result set mappings to achieve the same thing.

[Dave Tolls]

Surely the SQL for this is:

SELECT <whatever columns>
FROM CustomerDetails c
     JOIN <of whatever sort>
     CustomerLogs cl ON <whatever join, presumably customer id>
     JOIN <of whatever sort>
     CompanyDetails cd ON cd.code = c.code
WHERE <the rest of your WHERE clauses>

And that should be easily translated to HQL.

I can't see anything in that query that requires a JOIN on two SELECT statements.