Using subquery in FROM for HQL

Question

I have following SQL that is doing what I want (simplified):

SELECT tcd.Name, MAX(if(tcd.Name = 'Some Name', tcr.Value, NULL)) as 'Value'
FROM TestCaseData tcd, TestCaseDataResult tcr, PVRTable pvr,
(select MAX(sel.PK) as PK from TestCaseDataResult sel GROUP BY sel.PVR_FK, sel.TCD_FK) selector
WHERE tcr.PK = selector.PK AND tcr.TCD_FK = tcd.PK AND tcr.PVR_PK = pvr.PK
GROUP BY pvr.PK
ORDER BY pvr.PK

Selector table is needed because the set can have several records with the same sel.PVR_FK, sel.TCD_FK, and in that case the MAX(...) expression will select the bigger value, but I need to have the value with bigger PK instead (last value)

I want to have HQL instead of SQL. I'm trying to avoid using raw SQL in the code for the obvious reasons.

Looks like HQL does not support subqueries in the FROM. I feel like I can rewrite this so it could work without subquery but with self-join, but I'm struggling to find a correct solution.

I am also quite worried about the performance, so I do not want to have subqueries in SELECT due to inefficiency.

M0rtiis · Accepted Answer · 2015-08-07 14:52:17Z

1

I believe u can try this

SELECT tcd.Name, MAX(if(tcd.Name = 'Some Name', tcr.Value, NULL)) as 'Value')
FROM TestCaseData tcd, TestCaseDataResult tcr, PVRTable pvr
WHERE 
    tcr.PK IN(select MAX(PK) from TestCaseDataResult GROUP BY PVR_FK, TCD_FK) 
    AND tcr.TCD_FK = tcd.PK 
    AND tcr.PVR_PK = pvr.PK
GROUP BY pvr.PK
ORDER BY pvr.PK

http://docs.jboss.org/hibernate/core/3.3/reference/en/html/queryhql.html#queryhql-subqueries

edited Aug 7, 2015 at 14:52

answered Aug 7, 2015 at 14:38

M0rtiis

3,7611 gold badge17 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Archeg Over a year ago

Will select MAX(tcr.PK) as PK from TestCaseDataResult sel GROUP BY sel.PVR_FK, sel.TCD_FK be executed N times in case of this? I'm trying to avoid this

M0rtiis Over a year ago

answer updated. Nope. It have no relation to other tables and in join-context it will be some kind of static. sorry for English

Collectives™ on Stack Overflow

Using subquery in FROM for HQL

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related