IN statement in SQL doesn’t accept a wild character (SAP)

SQL SAP HANA SAP SAP Basis

Check out the below SQL:

SELECT *

FROM Test1 t INNER JOIN TEST2 s ON t.ID = s.RID

WHERE t.sdate >= ?1 AND t.edate <= ?2 AND t.id LIKE ?3

AND t.afdeling IN (?4) AND s.sid IN (?5)

ORDER BY ID DESC

Parameters:

java.sql.Timestamp, java.sql.Timestamp, java.lang.String, java.lang.String, java.lang.String, java.lang.String

In this only 2 parameters are mandatory and when other parameters are not passed and I'm using the character '%' for the LIKE statement so all data will be shown. Any idea how to solve this in IN statement?

0 0

1 Answer

SAP Expert

Answered on 6th Dec, 2017

Note that IN statement in SQL doesn’t accept a wild character. You have to use OR/AND to make it work as mentioned below:

select *

from Test1 t INNER JOIN Test2 s ON t.ID = s.RID

where t.sdate >= ?1

AND t.edate <= ?2

AND t.id LIKE ?3

AND ('%' = ?4 OR t.afdeling IN (?4))

AND ('%' = ?5 OR s.sid IN (?5))

ORDER BY ID DESC

In case you are using parameter position to pass parameters, try changing it as below:

AND ('%' = ?4 OR t.afdeling IN (?5))

AND ('%' = ?6 OR s.sid IN (?7))

0 0

Would you like to add a better answer?

IN statement in SQL doesn’t accept a wild character (SAP)

Follow

Newsletter