Intersect is not working in HQL

Question

Hai all I am using Hibernate, java and Sql server for creating an application. And while doing this I have come across a problem where Intersect and Union is not supported by HQL. For me the following code works in native SQL but it is not working when I am dealing with HQL.

SQL

SELECT *
FROM LAB_TEST_SERVICES lts
WHERE lts.inttestid IN
(SELECT lsm.inttestid 
    FROM LAB_SPECIMEN_MAPPING lsm
    WHERE lsm.status = 1
intersect
SELECT ltl.inttestid 
    FROM LAB_TEST_LOCATION ltl
    WHERE ltl.status = 1)

HQL

String hql="FROM TEST_SERVICES_POJO lts WHERE lts.inttestid IN (SELECT lsm.inttestid FROM SPECIMEN_MAPPING_POJO lsm WHERE lsm.status = 1 intersect SELECT ltl.inttestid FROM TEST_LOCATION_POJO ltl WHERE ltl.status =1)";

Can anyone help me with changing the SQL to HQL. Thanks in advance.

Answer 1

You must replace your INTERSECT instruction with two EXISTS clause in AND relation.

Try this:

SELECT *
FROM LAB_TEST_SERVICES_POJO lts
WHERE EXISTS
(SELECT lsm.inttestid 
    FROM LAB_SPECIMEN_MAPPING lsm
    WHERE lsm.status = 1
    AND lts.inttestid = lsm.inttestid)
AND EXISTS
(SELECT ltl.inttestid 
    FROM LAB_TEST_LOCATION ltl
    WHERE ltl.status = 1
    AND lts.inttestid = ltl.inttestid)