Case in Select statement returns an error-CodePudding

SELECT 
    CONCAT('C','~') AS "1", FFIITMN,
    CASE WHEN FFIITMN IN (SELECT FGDITMN FROM S2151BDW.PWRDTA.FGDPRPIP WHERE FGDCMPN = '  1' THEN '1' ELSE '0' END) AS "16"
FROM 
    S2151BDW.PWRDTA.FFIITMAP --Item master file
    JOIN S2151BDW.PWRDTA.FFJITMBP ON FFJITMN = FFIITMN AND FFJCMPN = FFICMPN --Item balance file
    JOIN S2151BDW.PWRDTA.FFBCLSAP ON FFBCLSN = FFJCLSN AND FFBCMPN = FFICMPN --Item class file

WHERE 
    FFICMPN = '  1' 
AND
    FFIITMN = '    365725'

Returns the error:

Comparison operator IN not valid.. SQLCODE=-115, SQLSTATE=42601, DRIVER=3.69.56

Is it possible to use a case statement in select to check to see if the item number is in another table?

CodePudding user response：

You've got mis-matched brackets - the closing bracket should be at the end of the inner SELECT query, not at the end of the CASE block:

CASE WHEN FFIITMN IN (SELECT FGDITMN FROM S2151BDW.PWRDTA.FGDPRPIP WHERE FGDCMPN = '  1') THEN '1' ELSE '0' END AS "16"