0

Hi,

Can anyone help me with my CASE statement update on a temp table.

In my report I have the following case statement;

'STATUS' = CASE
WHEN customer = 'BUS' AND country = 'GB' AND field_1 = 'X' THEN 'UK_BUS'
WHEN customer = 'BUS' AND country <> 'GB' AND field_1 = 'X' THEN 'FOREIGN_BUS'
WHEN customer = 'PRI' AND country = 'GB' AND field_1 = 'X' THEN 'UK_PRI'
WHEN customer = 'PRI' AND country<> 'GB' AND field_1 = 'X' THEN 'FOREIGN_PRI'
ELSE 'OTHER_CUST_TYPE'
END

I am trying to do a new report which uses a temp table.
The select query dumps all the rows into a temp table and creates a blank column for STATUS
I then need to update the STATUS for each row by testing other columns and assigning the status depending on certain conditions as above.

I have tried;
UPDATE #TEMP_TABLE
SET STATUS =
CASE WHEN customer = 'BUS' AND country = 'GB' AND field_1 = 'X' THEN 'UK_BUS'
ELSE 'OTHER_CUST_TYPE'
END

Which works ok.

But if I add a second 'CASE' line it errors.

My question is, what is the correct SQL code to use to update my temp table in the same way as my 'STATUS' = CASE example works?

Thanks.

2
Contributors
1
Reply
11
Views
4 Years
Discussion Span
Last Post by IIM
This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.