In my case, only the first 2 parameters are mandatory. When the users hasn't given the rest of the parameters, I'm using the charachter '%' for the LIKE statement so all data will be shown. Any idea how to solve this for the IN statement? The solution param IN ('%') is not working => Null
Parameters:
java.sql.Timestamp, java.sql.Timestamp, java.lang.String, java.lang.String, java.lang.String, java.lang.String
Query:
SELECT *
FROM RC_Recall t INNER JOIN RC_RECALLSTORE s ON t.ID = s.RECALLID
WHERE t.creationdate >= ?1 AND t.enddate <= ?2 AND t.id LIKE ?3
AND t.afdeling IN (?4) AND s.storeid IN (?5)
ORDER BY ID DESC
the IN statement in SQL cannot accept a wild card character. You would have to add a OR to your query to get it to work...
select *
from RC_Recall t INNER JOIN RC_RECALLSTORE s ON t.ID = s.RECALLID
where t.creationdate >= ?1
AND t.enddate <= ?2
AND t.id LIKE ?3
AND ('%' = ?4 OR t.afdeling IN (?4))
AND ('%' = ?5 OR s.storeid IN (?5))
ORDER BY ID DESC
If you're using parameter position to pass parameters, you'll probably have to change it to
AND ('%' = ?4 OR t.afdeling IN (?5))
AND ('%' = ?6 OR s.storeid IN (?7))
Hope that helps