I am having a problem trying to work out the neatest solution for the following problem.
I have a table called Purchase which has a State column, where 1 is authorised, 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?