Unexpected results from IF THEN clause
Moderators: chulett, rschirm, roy
Unexpected results from IF THEN clause
Hello,
I am using 11.5 PX. When I try this IF THEN clause in Transformer stage:
if columnA <> '' then 'RT' else if columnB <> '' then 'RT' else if columnC <> '' then 'RT' else if columnD <> '' then 'RT' else if columnE <> '' then 'RC' else if columnF <> '' then 'RF' else ''
Unfortunately, this always returns 'RT' and never evaluates to other conditions.
Thanks for support.
Juan
I am using 11.5 PX. When I try this IF THEN clause in Transformer stage:
if columnA <> '' then 'RT' else if columnB <> '' then 'RT' else if columnC <> '' then 'RT' else if columnD <> '' then 'RT' else if columnE <> '' then 'RC' else if columnF <> '' then 'RF' else ''
Unfortunately, this always returns 'RT' and never evaluates to other conditions.
Thanks for support.
Juan
Juan
Out of curiosity, are you trying to check for "empty" columns and if so, what exactly does empty equate to in whatever you are checking? I'm wondering if you are looking for an empty string when they are (or can be) NULL instead... which are not equivalent.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
It depends on the priority of the conditions. If a condition you know to be true is not being evaluated, it's because a lower priority condition is true first.
Break out your logic into single statements, and do a final logic check based on the priority of the true conditions. Or, as I've found helpful in complex business requirements, consider setting a flag instead of setting the string value. Then check the flags for the final string you want to appear.
EDIT: Actually, the first problem could be that you're using negative logic. Instead of <> empty string, set flags for a column being empty. Negative logic is always tricky to get right.
Break out your logic into single statements, and do a final logic check based on the priority of the true conditions. Or, as I've found helpful in complex business requirements, consider setting a flag instead of setting the string value. Then check the flags for the final string you want to appear.
EDIT: Actually, the first problem could be that you're using negative logic. Instead of <> empty string, set flags for a column being empty. Negative logic is always tricky to get right.
Franklin Evans
"Shared pain is lessened, shared joy increased. Thus do we refute entropy." -- Spider Robinson
Using mainframe data FAQ: viewtopic.php?t=143596 Using CFF FAQ: viewtopic.php?t=157872
"Shared pain is lessened, shared joy increased. Thus do we refute entropy." -- Spider Robinson
Using mainframe data FAQ: viewtopic.php?t=143596 Using CFF FAQ: viewtopic.php?t=157872
Ssome things...
- if your data is not exactly matching, it will not be as expected. You may need to trim first, then check vs empty string.
- if your data type is char, not varchar, it will not be possible to ever be empty string even after a trim.
- if the first columns trigger RC, the latter conditions won't do anything due to else. Else only happens when the conditions are false. That is, to get RF, you need multiple false returns on the early conditions.
It looks like you could simplify this logic. Maybe not, it depends on what you really have and what you really want.
Consider putting 'a' if col a is true, 'b' if col b is true ... etc to debug your logic. Multiple conditions giving the same result is harder to debug. You can put it back once you fix it.
- if your data is not exactly matching, it will not be as expected. You may need to trim first, then check vs empty string.
- if your data type is char, not varchar, it will not be possible to ever be empty string even after a trim.
- if the first columns trigger RC, the latter conditions won't do anything due to else. Else only happens when the conditions are false. That is, to get RF, you need multiple false returns on the early conditions.
It looks like you could simplify this logic. Maybe not, it depends on what you really have and what you really want.
Consider putting 'a' if col a is true, 'b' if col b is true ... etc to debug your logic. Multiple conditions giving the same result is harder to debug. You can put it back once you fix it.