JPQL - (JPA) ORA-00920: invalid relational operator

Question

List<InvTrnTransactionHeader> list = em.createQuery("SELECT p FROM InvTrnTransactionHeader p WHERE p.warehouse.warehouseCode IN :warehouseCode And (p.issueToWarehouseId IN :issuedWhId OR :issuedWhId is null ) And ( p.invTrnTransactionHeaderPK.transactionId IN :transactionCode OR :transactionCode is null ) And (p.vendorId IN :vendorId OR :vendorId is null) And p.invTrnTransactionHeaderPK.transactionDocumentDate >=:fromDate And p.invTrnTransactionHeaderPK.transactionDocumentDate <=:toDate And (p.invTrnTransactionHeaderPK.transactionDocumentNumber >=:fromDocument  OR :fromDocument is null) And (p.invTrnTransactionHeaderPK.transactionDocumentNumber <=:toDocument  OR :toDocument is null) And (p.vendorInvoiceNumber >=:fromInvoice  OR :fromInvoice is null) And (p.vendorInvoiceNumber <=:toInvoice  OR :toInvoice is null)")
           .setParameter("warehouseCode", warehouseCode)
           .setParameter("issuedWhId", issuedWhId)
            .setParameter("transactionCode", transactionId)
            .setParameter("vendorId", vendorId)
            .setParameter("fromDate", fromDate)
            .setParameter("toDate", toDate)
            .setParameter("fromDocument", fromDocument)
            .setParameter("toDocument", toDocument)
            .setParameter("fromInvoice", fromInvoiceNum)
            .setParameter("toInvoice", toInvoiceNum)
            .getResultList();

Query working if it's the null case... else when to fill all list parameters the query not working ... any help?

I am adding it right now... i should check all parameters not empty right ? — AbouLkhair, Feb 27 '17 at 11:25
You should check all parameters relative to a list, and which are you using with the keyword "IN" that are not empty, if they are empty dont add to the query — cralfaro, Feb 27 '17 at 13:00
could you please paste the SQL sentence? Maybe i can see something wrong — cralfaro, Feb 27 '17 at 15:58

score 1 · Accepted Answer · edited May 23 '17 at 11:53

1

your problem is when you are using

WHERE p.warehouse.warehouseCode IN:warehouseCode

Any of the IN operators, then if the variable warehouseCode is empty, JPA translate the SQL to IN () without elements and is a wrong SQL structure.

To fix it, you need to be sure the attribute warehouseCode, and all other are you using in the IN condition are not empty

Update:

Also have a look at this link Prepared statement IN clause you need to use the setArray() method in place of setParameter() and use an Array

edited May 23 '17 at 11:53

Community

1
1

answered Feb 27 '17 at 09:57

cralfaro

5,822
3
20
30

I already making that in my bean but that didn't solve the problem – AbouLkhair Feb 27 '17 at 11:34
use the property of show_sql=true, and you will see how the sql is build, then you can execute and check the problem, I got the same and was that. But now could be different, check better the SQL sentence – cralfaro Feb 27 '17 at 12:58

JPQL - (JPA) ORA-00920: invalid relational operator

1 Answers1