Derived Column Question

Question

Derived Column Question

Viewing 5 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Koen Verbeeck SSC Guru Points: 259197 More actions · Answer 1

What is EMPTY? Is it an empty string? A single space? Multiple spaces?

In the case it is an empty string, you better trim the column first and then check if the length of the string is equal to zero.

That way you will be 100% certain it is an empty string.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Phil Parkin SSC Guru Points: 247183 More actions · Answer 2

An alternative is to create a mapping table in SQL Server and use a LOOKUP to get the result you need. Much easier to maintain and handles easily the addition of more values.

Koen Verbeeck SSC Guru Points: 259197 More actions · Answer 3

Phil Parkin (1/15/2011)
An alternative is to create a mapping table in SQL Server and use a LOOKUP to get the result you need. Much easier to maintain and handles easily the addition of more values.

That is an excellent suggestion.

@jscot91: if you implement this suggestion, make sure that when you configure your lookup component you trap the "lookup no match output" and give some meaning to that.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

stricknyn SSCertifiable Points: 6560 More actions · Answer 4

I believe you are using AND qualifiers instead of OR. With AND all conditions must be true. Looks like you should be using an OR qualifier:

([Table1] == "Y" ? "YES" : ([Table1] == "N" ? "NO" [Table1] == " " ? "PENDING" :"")))

Should be:

([Table1] == "Y" ? "YES" || ([Table1] == "N" ? "NO" || [Table1] == " " ? "PENDING" :"")))

Thanks,

Strick

Koen Verbeeck SSC Guru Points: 259197 More actions · Answer 5

stricknyn (1/17/2011)
I believe you are using AND qualifiers instead of OR. With AND all conditions must be true. Looks like you should be using an OR qualifier:
([Table1] == "Y" ? "YES" : ([Table1] == "N" ? "NO" [Table1] == " " ? "PENDING" :"")))
Should be:
([Table1] == "Y" ? "YES" || ([Table1] == "N" ? "NO" || [Table1] == " " ? "PENDING" :"")))
\

By replacing the : with the ||, you are breaking the syntax of the SSIS IIF --> (boolean expression) ? expression_if_true : expression_if_false

The solution presented by Phil solves the problem more elegantly with high maintainability.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP