How to improve a case statement that uses two columns

Question

I have a table called Purchase which has a State column  where 1 is authorized  2 is completed  there are some other values too    I also have a Retailer table  which has a column RetailerProcessType  where 1 is one-step and 2 is two-step   I have the query below   CASE purc State     WHEN 1 THEN   AUTHORISED       WHEN 2 THEN   AUTHORISED       WHEN 4 THEN   AUTHORISED       ELSE   DECLINED    END                                                                       AS Autorised Decline Status    But what I need to do is as follows   WHEN STATE   2 AND RetailerProcessType   1 THEN   AUTHORISED   WHEN STATE   1 AND RetailerProcessType   2 THEN   PENDING   WHEN STATE   2 AND RetailerProcessType   2 THEN   AUTHORISED   ELSE   DECLINED     The only way I can think of doing this is having a massive IF statement around the query  one for a one-step retailer and another for a two-step  as my understanding is a WHEN clause cannot have an AND in it   However  this just seems long winded  anybody have any neater ideas

User · Answer

Just change your syntax ever so slightly:

CASE WHEN STATE = 2 AND RetailerProcessType = 1 THEN '"AUTHORISED"'
     WHEN STATE = 1 AND RetailerProcessType = 2 THEN '"PENDING"'
     WHEN STATE = 2 AND RetailerProcessType = 2 THEN '"AUTHORISED"'
     ELSE '"DECLINED"'
END

If you don't put the field expression before the CASE statement, you can put pretty much any fields and comparisons in there that you want. It's a more flexible method but has slightly more verbose syntax.

User · Answer

You could do it this way    -- Notice how STATE got moved inside the condition  CASE WHEN STATE   2 AND RetailerProcessType IN  1  2  THEN   AUTHORISED        WHEN STATE   1 AND RetailerProcessType   2 THEN   PENDING        ELSE   DECLINED   END   The reason you can do an AND here is that you are not checking the CASE of STATE  but instead you are CASING Conditions   The key part here is that the STATE condition is a part of the WHEN

[tsql] How to improve a case statement that uses two columns

Examples related to tsql