CODEWITHSUNDEEP
sql-servercasewhere-clause
martin.softpro
martin.softpro

Reputation: 443

SQL Server CASE WHEN in WHERE clause

I have a query with some joins. One of the joins depends of another value.

This is my query:

SELECT * 
FROM CLIIRE A
LEFT JOIN CLIIOR B ON A.PTA = B.PTA AND A.ORD = B.ORD AND A.ITE = B.ITE
LEFT JOIN CLIORD C ON C.PTA = B.PTA AND C.ORD = B.ORD 
LEFT JOIN CLIPAC D ON C.OPA = D.ORI AND C.PAC = D.PAC
LEFT JOIN CLIREN E ON E.NRE = A.NRE  AND D.INS = E.INS AND A.CPT = E.CPT
LEFT JOIN 
    (SELECT * 
     FROM CLINOM 
     WHERE CLA = (CASE WHEN B.PFA = '88882' THEN 'MD' ELSE 'BU' END )) AS N ON B.PFA = N.COD
LEFT JOIN 
    (SELECT * 
     FROM CLIMED WHERE PRE = '') AS M ON B.PFA = M.MED
LEFT JOIN CLIPRF P ON B.PRF = P.PRF
WHERE 
    (D.INS LIKE 'OM%' OR D.INS LIKE 'SOL%') 
    AND E.NFA IN ('5188') 
    AND A.CPT IN ('fi', 'pi')
ORDER BY 
    E.NFA

But I'm getting error in the line:

LEFT JOIN (SELECT * FROM CLINOM WHERE CLA = (CASE WHEN B.PFA = '88882' THEN 'MD' ELSE 'BU' END )) AS N ON B.PFA=N.COD

Looks like SQL Server won't allow me to do that:

The multi-part identifier "B.PFA" could not be bound

The column B.PFA exists.

Can anyone help me? Thanks !

Upvotes: 1

Views: 102

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269803

The problem is that the outer query reference is not available in the subquery. Happily, though the subquery is not needed. Try this logic:

LEFT JOIN CLINOM N
ON P.FA = N.COD AND
   N.CLA = (CASE WHEN B.PFA = '88882' THEN 'MD' ELSE 'BU' END)

And, you can phrase this without the CASE. Assuming B.PFA is not NULL:

LEFT JOIN CLINOM N
ON P.FA = N.COD AND
   ((N.CLA = 'MD' AND B.PFA = '88882') OR 
    (N.CLA = 'BU' AND B.PFA <> '88882')
   )

If B.PFA could be NULL:

 LEFT JOIN CLINOM N
ON P.FA = N.COD AND
   ((N.CLA = 'MD' AND B.PFA = '88882') OR 
    (N.CLA = 'BU' AND (B.PFA <> '88882' OR B.PFS IS NULL))
   )

You can also use COALESCE() if you like.

Upvotes: 2

Related Questions