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?
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