Use CASE in a WHERE clause

Question

Use CASE in a WHERE clause

Navigation

#1 by (4 votes)

1

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

The rule is, if the field is 'T' filtered by the Dt_Cad field, if the value is 'P' filtered by the Dt_emission field. Anyone know if this is possible?

Below my attempt to resolve

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)

sql-server where case

asked by anonymous 11.04.2018 / 20:19

1 answer

JavaScript development List two distinct tables and filter by date MySQL - PHP

score 4 · Accepted Answer

One workaround that does not involve CASE WHEN is to test Tp_Note in 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()))
       )

The CASE WHEN should be part of the logical expression and not "be the expression" logic. It would work well 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, it "belongs" to the expression (the final value of BETWEEN).