SQL code does not work when converted to JPQL code (ORA-00904 error code)

Thank you very much for looking at this post :). I have the following problem: I have a piece of SQL code that runs perfectly on the Oracle database. The problem comes when I try to run as JPQL code. I get the following error in the Eclipse IDE:

Internal Exception: java.sql.SQLSyntaxErrorException: ORA-00904: “THIRDTABLE”.”NAME”: invalid identifier.

The SQL that works is the following:

SELECT e.status_q,
       q.name,
       q.masterdatastatus_id
FROM firstTable e
JOIN secondTable q ON e.status_q = q.id
AND q.masterdatastatus_id = e.status_s
WHERE q.id != 7122
  AND e.qserie_id = 1296
  AND q.masterdatastatus_id not in
    (SELECT l1.status_s
     FROM thirdTable l1
     WHERE (l1.id,
            l1.name) not in
         (SELECT l2.id,
                 l2.name
          FROM thirdTable l2
          WHERE l2.status_s = 1))
  AND
    (SELECT count(*)
     FROM thirdTable l3
     WHERE l3.status_s = q.masterdatastatus_id) =
    (SELECT count(*)
     FROM thirdTable l4
     WHERE l4.status_s = 1)
  AND q.masterdatastatus_id in
    (SELECT x.status_s
     FROM thirdTable x
     LEFT JOIN thirdTable y ON x.id = y.id
     AND x.name = y.name
     WHERE y.id IS NOT NULL
       AND y.status_s = 1 )
ORDER BY q.id DESC;

This is the JPQL code that doesn’t work:

public static final String QUERY = "SELECT e.status, q.name, q.masterdatastatus.id FROM firstTableBE e JOIN secondTableBE q "
            + "ON e.status = q.id AND q.masterdatastatus.id = e.statusS WHERE "
            + "q.id != :STATUS_Q AND e.qSerieOid = :QSERIE_ID AND "
            + "q.masterdatastatus.id NOT IN (SELECT l1.status from ThirdTableBE l1 where (l1.oid, l1.name) NOT IN (SELECT l2.oid, l2.name from ThirdTableBE l2 where l2.status = 1)) AND "
            + "(SELECT COUNT(l3) from ThirdTableBE l3 where l3.status = q.masterdatastatus.id) = (SELECT COUNT(l4) from ThirdTableBE l4 where l4.status = 1) AND "
            + "q.masterdatastatus.id IN (SELECT x.status from ThirdTableBE x LEFT JOIN ThirdTableBE y on x.oid = y.oid and x.name = y.name where y.oid is not null and y.status = 1) "
            + "ORDER BY q.id DESC";

I guess the problem comes from this part of the code:

"q.masterdatastatus.id NOT IN (SELECT l1.status from ThirdTableBE l1 where (l1.oid, l1.name) NOT IN (SELECT l2.oid, l2.name from ThirdTableBE l2 where l2.status = 1))"

Are multiple expressions in subqueries not supported in JPQL? Is it the pair after the ‘where’ statement ? When I try with only one expression (with l1.oid for example), it works but the result is not the expected one. Also if I remove the code above, it works, but again, it’s not the result I want.

Thanks in advance for the help! 🙂

Answer

I solved the problem by running the SQL code with the createNativeQuery method from the entityManager. It seems that JPQL doesn’t really like pairs in the where statement. Maybe the JPA version I use does not support pairs (I use JPA 2.6.4). This is the code :

        Query query = entityManager
                .createNativeQuery("select e.status_q, q.name, q.masterdatastatus_id from firstTable e "
                        + "join secondTable q on e.status_q = q.id and q.masterdatastatus_id = e.status_s "
                        + " WHERE q.id != 7122 and e.qserie_id = 1296 and "
                        + "q.masterdatastatus_id not in (select l1.status_s from thirdTable l1 where (l1.id, l1.name) not in (select l2.id, l2.name from thirdTable l2 where l2.status_s = 1)) and "
                        + "(select count(*) from thirdTable l3 where l3.status_s = q.masterdatastatus_id) = (select count(*) from thirdTable l4 where l4.status_s = 1) and "
                        + "q.masterdatastatus_id in (select x.status_s from thirdTable x left join thirdTable y on x.id = y.id and x.name = y.name where y.id is not null and y.staatus_s = 1 ) "
                        + "order by q.id desc");
        List<Object> result = query.getResultList();

Thanks again Abenamor for the solution :).