SQL selecting all records with IN statement

Question

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

Spock · Accepted Answer · 2014-12-16T14:37:45.227

1

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

edited Dec 16 '14 at 14:37

answered Dec 16 '14 at 14:15

Spock

4,700
2
16
21

the comparison predicate >>? = '%'<< contains a host variable (parameter marker) on the left hand side – Timvdb92 Dec 16 '14 at 14:19
So should it be '%' = ?4, and '%' = ?5 instead? – Spock Dec 16 '14 at 14:26
Indeed, it seems to be working! Thanks Maybe you can change your answer in the correct syntax :-) – Timvdb92 Dec 16 '14 at 14:36

score 0 · Answer 2 · answered Dec 16 '14 at 14:18

0

Rather hard coding query, You might build query dynamically based on input. i.e. whichever input is present add only those criteria in where clause.

answered Dec 16 '14 at 14:18

Jitendra Vispute

709
8
18

Dynamically building the query will force a hard parse by the database. That is usually not desirable. – Frank Schmitt Dec 16 '14 at 14:19

SQL selecting all records with IN statement

2 Answers2