How to improve a case statement that uses two columns
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
.
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.