CODEWITHSUNDEEP
sqlsql-server
Andy K
Andy K

Reputation: 5044

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:

Thanks

Upvotes: 0

Views: 59

Answers (4)

Sreenivas Pallavarapu
Sreenivas Pallavarapu

Reputation: 11

  **Combined common filters from the last two case statements**
  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.[Column] = CFE_DIR_PPHY.[Matching Column]
    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') 
                0R
                (CFE_DIR_PMOR.rep_legal != 1
                    AND CFE_DIR_PPHY.ORDRE = '00')
            )
        )
    )

Upvotes: 1

EoinS
EoinS

Reputation: 5482

Using some logic to reduce the number of ORs which can slow a query way down

SELECT CFE_DIR_PMOR.col1,CFE_DIR_PMOR.col2,CFE_DIR_PMOR.col3
    FROM Table1
    WHERE <cdt1> AND <cdt2> ... AND <cdt5>
     AND
     (cast((isnull(@id2,'')+isnull(@id,'')) as int)=1)
     OR
     ((CFE_DIR_PMOR.GESTDEL = '1'AND CFE_DIR_PPHY.GESTDEL = '1')
       AND
     (CFE_DIR_PMOR.rep_legal= '1' OR CFE_DIR_PPHY.ORDRE = '00'))

Is Table1 the same as the Inner Join you want perform?

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269953

Why would you want case in the where, when or would do:

SELECT CFE_DIR_PMOR.col1,CFE_DIR_PMOR.col2,CFE_DIR_PMOR.col3
FROM CFE_DIR_PMOR INNER JOIN
     CFE_DIR_PPHY 
     ON ??
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
        . . .
      );

In addition, if you are using JOIN, you should have an ON clause. I'm not sure which conditions go there.

Upvotes: 2

JohnHC
JohnHC

Reputation: 11205

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'))

Upvotes: 4

Related Questions