how can i use both ternary operator and isnull function in SSIS?

user_14580226 0
Viewed 55

enter image description here

For this table, I have two null value in suburb and postcode. I want to change 'IsValid' column into "N" if the suburb, and postcode is "Null". otherwise the 'IsValid' is "Y"

Here's my expression:

LEN(LTRIM(RTRIM(School_Suburb))) == 0 || 
LEN(LTRIM(RTRIM(School_Postcode))) == 0 || 
ISNULL(School_Suburb) || 
ISNULL(School_Postcode) ? "N" : "Y"

and this is the error message.

enter image description here

So just wondering if anyone know which part I wrote it wrong? How can I change it?

this is the screenshot of derived column edit page enter image description here

conditional-statements ssis isnull
1 Answers

If you remove IsValid from the Expression and put it in the first column, currently "Derived Column 1".

The other change is that you will need to wrap the entirety of your logical ORs together with parentheses for

(LEN(LTRIM(RTRIM(School_Suburb))) == 0 || LEN(LTRIM(RTRIM(School_Postcode))) == 0 || ISNULL(School_Suburb) || ISNULL(School_Postcode)) ? "N" : "Y"

A good rule of thumb on expressions is that if it doesn't fit in the visible box, it's probably too long. Here, I'd break it out into three new columns split across two Derived Column Components.

enter image description here

Here I create two new columns IsSuburbInvalid and IsSchoolInvalid (although mislabled in the screenshot as Valid, not Invalid)

LEN(LTRIM(RTRIM(School_Suburb))) == 0 || ISNULL(School_Suburb)
LEN(LTRIM(RTRIM(School_Postcode))) == 0 || ISNULL(School_Postcode)

In the next Derived Column (has to be a separate component since it will use the new columns we just created), I create IsValid with a much easier to debug logic of

IsSchoolInvalid || IsSuburbInvalid ? "N" : "Y"
user_1819650
Related