Use CASE in a WHERE clause

Question

Use CASE in a WHERE clause

I need to use a condition within a filter parameter in a report. Basically the rule is determined by a vachar field that its value is ' T 'or'P'. I tried a solution like below but it didn't work out very well.

The rule is, if the field is' T 'filter by field Dt_Cad, if the value is' P ' filter by field Dt_emissao. Does anyone know if this is possible?

Below my solution attempt

Thanks for everyone's help

WHERE   (CASE 
        WHEN NotFis.Tp_Nota='P' then
             NotFis.DT_Emissao between '2018-01-01'   and GETDATE()  
        ELSE NotFis.dt_CAD     between '2018-01-01'   and GETDATE()  
        END)

1

sql-server case where

Author: Guilherme Nascimento, 2018-04-11

Source

1 answers

score 4 · Accepted Answer

A workaround that doesn't involve CASE WHEN is to test Tp_note on WHERE, like this:

WHERE ( 
        (NotFis.Tp_Nota='P' AND (NotFis.DT_Emissao between '2018-01-01' AND GETDATE()))
        OR
        (NotFis.Tp_Nota='T' AND (NotFis.dt_CAD between '2018-01-01' AND GETDATE()))
       )

CASE WHEN should be part of the logical expression and not "be the logical expression". It would work fine with something like this:

WHERE NotFis.DT_Emissao BETWEEN  '2018-01-01' AND 
       (CASE  WHEN NotFis.Tp_Nota='P' THEN GETDATE()
              ELSE NotFis.dt_CAD    GETDATE()-30
        END)

In this example above, it is" part " of the expression (the final value of BETWEEN).