Andy K Andy K - 2 months ago 6
SQL Question

sqlserver - case in the where

My aim is to have the equivalent of this

SELECT CFE_DIR_PMOR.col1,CFE_DIR_PMOR.col2,CFE_DIR_PMOR.col3
FROM CFE_DIR_PMOR INNER JOIN CFE_DIR_PPHY
WHERE <cdt1> AND <cdt2> ... AND <cdt5>
AND CFE_DIR_PMOR.GESTDEL IS NULL
AND CFE_DIR_PPHY.GESTDEL = '1'
UNION ALL
SELECT CFE_DIR_PMOR.col1,CFE_DIR_PMOR.col2,CFE_DIR_PMOR.col3
FROM table1
WHERE <cdt1> AND <cdt2> ... AND <cdt5>
AND CFE_DIR_PMOR.GESTDEL = '1'
AND CFE_DIR_PPHY.GESTDEL IS NULL
UNION ALL
SELECT CFE_DIR_PMOR.col1,CFE_DIR_PMOR.col2,CFE_DIR_PMOR.col3
FROM table1
WHERE <cdt1> AND <cdt2> ... AND <cdt5>
AND CFE_DIR_PMOR.GESTDEL = '1'
AND CFE_DIR_PPHY.GESTDEL = '1'
AND CFE_DIR_PMOR.rep_legal= '1'
UNION ALL
SELECT CFE_DIR_PMOR.col1,CFE_DIR_PMOR.col2,CFE_DIR_PMOR.col3
FROM table1
WHERE <cdt1> AND <cdt2> ... AND <cdt5>
AND CFE_DIR_PMOR.GESTDEL = '1'
AND CFE_DIR_PPHY.GESTDEL = '1'
AND CFE_DIR_PMOR.rep_legal != 1
AND CFE_DIR_PPHY.ORDRE = '00'


As you can see, it is burdensome and a CASE in the WHERE can be a good solution. So this is what I did

SELECT CFE_DIR_PMOR.col1,CFE_DIR_PMOR.col2,CFE_DIR_PMOR.col3
FROM table1
WHERE <cdt1> AND <cdt2> ... AND <cdt5>
AND
CASE WHEN CFE_DIR_PMOR.GESTDEL IS NULL AND CFE_DIR_PPHY.GESTDEL = '1' THEN 1
WHEN CFE_DIR_PMOR.GESTDEL = '1' AND CFE_DIR_PPHY.GESTDEL IS NULL THEN 1
ELSE
CASE WHEN CFE_DIR_PMOR.GESTDEL = '1' AND CFE_DIR_PPHY.GESTDEL = '1' AND CFE_DIR_PMOR.rep_legal= 1 THEN 1
WHEN CFE_DIR_PMOR.GESTDEL = '1' AND CFE_DIR_PPHY.GESTDEL = '1' AND CFE_DIR_PMOR.rep_legal != 1 AND CFE_DIR_PPHY.ORDRE = '00' THEN 1
ELSE 0
END
END =1


Question:


  • Is my case done correctly? if no, are there anything missing



Thanks

Answer

Use OR

SELECT CFE_DIR_PMOR.col1,CFE_DIR_PMOR.col2,CFE_DIR_PMOR.col3
FROM CFE_DIR_PMOR INNER JOIN CFE_DIR_PPHY 
ON CFE_DIR_PMOR.[Something] = CFE_DIR_PPHY.[Something]
WHERE <cdt1> AND <cdt2> ... AND <cdt5>
AND ((CFE_DIR_PMOR.GESTDEL IS NULL
    AND CFE_DIR_PPHY.GESTDEL = '1')
OR (CFE_DIR_PMOR.GESTDEL = '1'
    AND CFE_DIR_PPHY.GESTDEL IS NULL )
OR (CFE_DIR_PMOR.GESTDEL = '1'
    AND CFE_DIR_PPHY.GESTDEL = '1'
    AND CFE_DIR_PMOR.rep_legal= '1')
OR (CFE_DIR_PMOR.GESTDEL = '1'
    AND CFE_DIR_PPHY.GESTDEL = '1'
    AND CFE_DIR_PMOR.rep_legal != 1
    AND CFE_DIR_PPHY.ORDRE = '00'))
Comments