If the expression is a collection of mutiple columns or literals ,The solution is to combination is converting all items to varchar and concatinate them into
one string expression.Concatination we have to do this on both side (OUTER and INNER Query ) of the IN operator.
Examples for the same are given below.
SELECT * FROM EMPLOYEE WHERE CAST(EMPNAME+CAST(SALARY AS VARCHAR(100)) AS VARCHAR(200))
IN
( SELECT CAST(EMPNAME+CAST(SALARY AS VARCHAR(200)) AS VARCHAR(200)) FROM EMPLOYEE )
SELECT * FROM EMPLOYEE WHERE CAST(EMPNAME+ SPACE(1) + CAST(SALARY AS VARCHAR(100)) AS VARCHAR(200))
IN
( SELECT CAST(EMPNAME+ SPACE(1) +CAST(SALARY AS VARCHAR(100)) AS VARCHAR(200)) FROM EMPLOYEE )
SELECT * FROM EMPLOYEE WHERE CAST(EMPNAME+ ‘|’ +CAST(SALARY AS VARCHAR(100)) AS VARCHAR(200))
IN
( SELECT CAST(EMPNAME+ ‘|’ +CAST(SALARY AS VARCHAR(100)) AS VARCHAR(200)) FROM EMPLOYEE )
If you have any other solution , please let me know. We all are here to learn.
Pawan Kumar
Pawankkmr@hotmail.com