Correlated subqueries in HQL Select Clause

Question

I am trying use correlated queries in HQL (in select clause) for the first time and I am having the problem:

My HQL Query is of type :

SELECT CMP.id ,CMP.Name, CMP.startDate, CMP.endDate, CMPTTL.impr, CMPTTL.cnImpr,(CMPTU.id.login   
        FROM Z CMPTU 
        WHERE CMPTU.id.cId=CMP.id AND CMPTU.id.login = '[email protected]')  
FROM X CMP, Y CMPTTL
WHERE CMP.Status = 'L' AND CMP.id = CMPTTL.Id (+)

As per the Hibernate Documentation "HQL subqueries can occur only in the select or where clauses.Even correlated subqueries (subqueries that refer to an alias in the outer query) are allowed. "

But when i execute my HQl I have this Error

"org.hibernate.QueryException: aggregate function expected before ( in SELECT"

I have seen the selectParser of hibernate and it expects a Aggregate function whenever "(" is encountered.

Please suggest

Thanks in Advance

are you using SQL or HQL? From what I know, you can't have (+) for outer join in your HQL. If you remove the subquery is the remaining part working fine? — ManuPK
@Manu PK : I m using HQL, (+) is accepted as i am using Oracle as a database, Yes If i remove subquery then my Query works fine — Amandeep
I have updated the answer, let me know if that is working? If it is working fine accept the answer as correct. — ManuPK

ManuPK ManuPK · Accepted Answer · 2012-02-21T16:17:02

The problem might be because, you are joining the Z and id multiple times in the same query. I have modified your sub-query to use an alias of the join. I could not test it but hope that works.

SELECT CMP.id ,CMP.Name, CMP.startDate, CMP.endDate,
 CMPTTL.impr, CMPTTL.cnImpr,
   (CMPTUid.login FROM Z CMPTU join CMPTU.id as CMPTUid 
        WHERE CMPTUid.cId=CMP.id AND CMPTUid.login = '[email protected]')  
FROM X CMP, Y CMPTTL
WHERE CMP.Status = 'L' AND CMP.id = CMPTTL.Id (+)

Correlated subqueries in HQL Select Clause

1 Answers