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?
Yes, you can evaluate different columns in the CASE statement.
Multiple conditions in CASE statementYou can evaluate multiple conditions in the CASE statement.
CASE statement in SQL and aggregate functions Aggregate functions in SQL Server perform calculations and return a single value. Examples of aggregate functions are MIN, MAX, COUNT, ABG and CHECKSUM. For this purpose, we use the COUNT aggregate function in SQL Server.
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With