Hibernate version: 2.1.1 and 2.1.7c
Name and version of the database you are using: Postgresql 7.4.1
Is there a limit to how many levels you can nest a subquery in HQL? I have this HQL query:
Code:
FROM EncounterCard AS enc
WHERE enc.patientId = ?
AND enc.encId =
(SELECT MAX(sub3.encId)
FROM EncounterCard AS sub3
WHERE sub3.masterId = enc.masterId
AND sub3.messageSequence =
(SELECT MAX(sub2.messageSequence)
FROM EncounterCard AS sub2
WHERE sub2.masterId = enc.masterId
AND sub2.messageTimeStamp =
(SELECT MAX(sub1.messageTimeStamp)
FROM EncounterCard AS sub1
WHERE sub1.masterId = enc.masterId)))
ORDER BY enc.encounterTimeStamp DESC
When I pass it a parameter of 'pid-1-1', according to QueryTranslator's debug the generated SQL is:
Code:
select encounterc0_.encid as encid,
encounterc0_.patientid as patientid,
encounterc0_.encdate as encdate, encounterc0_.masterid as masterid,
encounterc0_.msgtimestamp as msgtime12_,
encounterc0_.msgsequence as msgsequ13_
from encounters encounterc0_
where (encounterc0_.patientid='pid-1-1' )
AND(encounterc0_.encid=
(select MAX(encounterc1_.encid)
from encounters encounterc1_
where (encounterc1_.masterid=encounterc0_.masterid )
AND(encounterc1_.msgsequence=
(select MAX(encounterc0_.msgsequence)
from encounters encounterc0_
where (encounterc0_.masterid=encounterc0_.masterid )
AND(encounterc0_.msgtimestamp=
(select MAX(encounterc0_.msgtimestamp)
from encounters encounterc0_
where (encounterc0_.masterid=encounterc0_.masterid )))))))
order by encounterc0_.encdate DESC;
Notice how after the first subquery 'encounterc0_' is reused instead of 'encounterc2_' or 'encounterc3_'. Is there a nesting level setting that I'm missing?
If it matters, the types for the above fields are:
Code:
encId : long
patientId : string
masterId : string
messageSequence : long
messageTimeStamp : timestamp
encounterTimeStamp : timestamp
Thanks for any leads.
Login
FAQ
Search