I've got a CASE
statement that works, but because I have to do it in SSIS, I am at a loss:
SELECT [BPCNUM_0], TYPE,
CASE
WHEN [TYPE]=1 THEN 'Normal'
WHEN [TYPE]=2 THEN 'Divers'
WHEN [TYPE]=3 THEN 'Intra-Société'
WHEN [TYPE]=4 THEN 'Prospect'
END AS TYPE
FROM table
As you can see, the case statement evaluates values in one column and renames them depending on what they are. when I tried this with SSIS, it didn't work
[TYPE] == 1 ? "Normal" : [TYPE] == 2 ? "Divers" : [TYPE] == 3 ? "Intra-Société" : [TYPE] == 4 ? "Prospect"
I tried also
[TYPE] == "1" ? "Normal" : [TYPE] == "2" ? "Divers" : [TYPE] == "3" ? "Intra-Société" : [TYPE] == "4" ? "Prospect"
but also it didn't work.
Your expression needs to add a final ELSE statement since it should know the value to assign in case non of the previous conditions is validated:
[TYPE] == 1 ? "Normal" :
[TYPE] == 2 ? "Divers" :
[TYPE] == 3 ? "Intra-Société" :
[TYPE] == 4 ? "Prospect" : ""
Which is equivalent to the following in SQL:
SELECT [BPCNUM_0], TYPE,
CASE
WHEN [TYPE]=1 THEN 'Normal'
WHEN [TYPE]=2 THEN 'Divers'
WHEN [TYPE]=3 THEN 'Intra-Société'
WHEN [TYPE]=4 THEN 'Prospect'
ELSE ''
END AS TYPE
FROM table
If you need to assign a NULL
value instead of an empty string, you can use the following expression (Assuming that the derived column type is DT_WSTR
):
[TYPE] == 1 ? "Normal" :
[TYPE] == 2 ? "Divers" :
[TYPE] == 3 ? "Intra-Société" :
[TYPE] == 4 ? "Prospect" : NULL(DT_WSTR,50)