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.
