Derived Column to handled mulitple conditions in CASE statement?

Question

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.

Hadi · Accepted Answer · 2022-01-25T11:16:05.250

1

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)

edited Jan 25 '22 at 11:16

answered Jan 25 '22 at 10:20

Hadi

36,233
13
65
124

the type of the column is tiny int – chaneb Jan 25 '22 at 10:53
@chaneb anything new? Why you have unchecked this answer? – Hadi Jan 25 '22 at 17:59
@chaneb the derived column output type is a `string` make sure you are not mapping it to a numeric destination column. – Hadi Jan 25 '22 at 19:04
@ Hadi just one more question plz. My destination column type is tinyint. I'm trying to put a DT_WSTR data into a tinyint data. How am I supposed to fix that? I tried casting it using DT_UI1 but it didn't work! – chaneb Jan 27 '22 at 17:08
@chaneb why you should do that. You should not edit your data since it well designed and stored. You should create another table (example: TypeReference(Id tinyint, Description VARCHAR(50)) and store all the string values that you are using in your CASE statement. Then you should join both tables once you need to retrieve the string values – Hadi Jan 27 '22 at 17:46
For example: `SELECT myTable.*, TypeReference.Description FROM myTable LEFT JOIN TypeReference ON myTable.Type = TypeReference.Id)` – Hadi Jan 27 '22 at 17:47
@chaneb if the purpose of creating the SSIS package is only retrieving those values then no need to create this package. – Hadi Jan 27 '22 at 17:49
@chaneb since your both questions are related. I added those comments to my answer on the other question. https://stackoverflow.com/questions/70852578/ole-db-destination-invalid-character-value-for-cast-specification – Hadi Jan 27 '22 at 18:19
1

I’m so thankful for everything you bring to the table. I truly appreciate your hard work – chaneb Jan 27 '22 at 22:36

Derived Column to handled mulitple conditions in CASE statement?

1 Answers1

Linked