CODEWITHSUNDEEP
t-sql
swade1987
swade1987

Reputation: 1673

How to improve a case statement that uses two columns

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?

Upvotes: 31

Views: 135777

Answers (2)

Bassam Mehanni
Bassam Mehanni

Reputation: 14944

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.

Upvotes: 56

JNK
JNK

Reputation: 65147

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.

Upvotes: 9

Related Questions