Nested IF ELSE in a derived column

Question

I have the following logic to store the date in BI_StartDate as below:

If UpdatedDate is not null then BI_StartDate=UpddatedDate
ELSE BI_StartDate takes EntryDate value , if the EntryDate is null then BI_StartDate=CreatedDate
If the CreatedDate IS NULL then BI_StartDate=GetDATE()

I am using a derived column as seen below:

ISNULL(UpdatedDateODS)  ? EntryDateODS : (ISNULL(EntryDateODS) ? CreatedDateODS :
(ISNULL(CreatedDateODS) ? GETDATE()  ))

I am getting this error:

The expression "ISNULL(UpdatedDateODS) ? EntryDateODS : (ISNULL(EntryDateODS) ? CreatedDateODS :(ISNULL(CreatedDateODS) ? GETDATE() ))" on "Derived Column.Outputs[Derived Column Output].Columns[Derived Column 1]" is not valid.

Does `BI_StartDate` means _"Date de bon d'intervention"_? – Ilyes Jun 20 '20 at 17:00 — Ilyes, Jun 20 '20 at 17:00

KeithL · Accepted Answer · 2020-02-19T20:03:45.213

2

You are looking the first non-null which is a coalesce which doesn't exist in SSIS Data Flow (derived Column).

I'd suggest a very simple script component:

Row.BIStartDate = Row.UpdateDate ?? Row.EntryDate ?? Row.CreatedDate ?? DateTime.Now;

This is the Input Columns Screen:

This is the Inputs and Outputs:

And then you add the above code to Row Processing section:

public override void Input0_ProcessInputRow(Input0Buffer Row)
    {
        /*
         * Add your code here
         */

        Row.BIStartDate = Row.UpdateDate ?? Row.EntryDate ?? Row.CreatedDate ?? DateTime.Now;
}

edited Feb 19 '20 at 20:03

answered Feb 19 '20 at 19:52

KeithL

5,348
3
19
25

I will be waiting thank you in advance. Can you explain more why it is not possible to use Derived column ? – Amira Bedhiafi Feb 19 '20 at 19:57
BTW -> The code will take the first non-null value as the result. You can rearrange the order, but that is what I got out of your if then else logic. – KeithL Feb 19 '20 at 21:32

score 1 · Answer 2 · answered Feb 20 '20 at 09:56

From syntax perspective, the nested if-else condition is not written well, since you have to make sure that all possible output should have the same data type, also you didn't mentioned the last "else" condition:

ISNULL(UpdatedDateODS)  ? EntryDateODS : (ISNULL(EntryDateODS) ? CreatedDateODS :
(ISNULL(CreatedDateODS) ? GETDATE() : **<missing>** ))

From logical perspective, you the expression may throw exception since you are using EntryDateODS column if ISNULL(UpdatedDateODS) is true, while you should check if EntryDateODS is not null before using it, I suggest that the expression is as following:

ISNULL(UpdatedDateODS)  ? UpdatedDateODS : (ISNULL(EntryDateODS) ? EntryDateODS :
(ISNULL(CreatedDateODS) ? CreatedDateODS : GETDATE()  ))

As mentioned above, if UpdatedDateODS , EntryDateODS, CreatedDateODS and GETDATE() don't have the same data type then you should cast to a unified data type as example:

ISNULL(UpdatedDateODS)  ? (DT_DATE)UpdatedDateODS : (ISNULL(EntryDateODS) ? (DT_DATE)EntryDateODS :
(ISNULL(CreatedDateODS) ? (DT_DATE)CreatedDateODS : (DT_DATE)GETDATE()  ))

Nested IF ELSE in a derived column

2 Answers2