Unexpected results from IF THEN clause

Post questions here relative to DataStage Enterprise/PX Edition for such areas as Parallel job design, Parallel datasets, BuildOps, Wrappers, etc.

Moderators: chulett, rschirm, roy

Post Reply
jck77
Premium Member
Premium Member
Posts: 5
Joined: Tue Feb 07, 2017 5:47 pm

Unexpected results from IF THEN clause

Post by jck77 »

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
Juan
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

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
jck77
Premium Member
Premium Member
Posts: 5
Joined: Tue Feb 07, 2017 5:47 pm

Post by jck77 »

Hi Chulett,
thanks for reply

i am checking only non null string from multiple columns (A,B,C,..). If it is not null then evaluate to constant value

don't know if this answers to your question...
Juan
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

Sort of. Make sure you understand that NULL and an empty string are not the same thing. Have you tried using either the IsNull or IsNotNull functions as appropriate?
-craig

"You can never have too many knives" -- Logan Nine Fingers
jck77
Premium Member
Premium Member
Posts: 5
Joined: Tue Feb 07, 2017 5:47 pm

Post by jck77 »

Sure. tried IsNotNull then got same unexpected output.

how to evaluate multiple if---then in Transformer? maybe my syntax is wrong

i appreciate any help

Thanks.
Juan
FranklinE
Premium Member
Premium Member
Posts: 739
Joined: Tue Nov 25, 2008 2:19 pm
Location: Malvern, PA

Post by FranklinE »

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.
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
jck77
Premium Member
Premium Member
Posts: 5
Joined: Tue Feb 07, 2017 5:47 pm

Post by jck77 »

Hi Franklin, thanks for this new approach. i will it a try
Juan
UCDI
Premium Member
Premium Member
Posts: 383
Joined: Mon Mar 21, 2016 2:00 pm

Post by UCDI »

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.
asorrell
Posts: 1707
Joined: Fri Apr 04, 2003 2:00 pm
Location: Colleyville, Texas

Post by asorrell »

I suspect one of the fields columnA/B/C/D is CHAR. If it is, then your test will always return 'RT' because a CHAR field is fixed length and can never be empty. It will always be padded with $APT_PAD_CHAR (usually spaces, or Hex zeroes (0x0).
Andy Sorrell
Certified DataStage Consultant
IBM Analytics Champion 2009 - 2020
jck77
Premium Member
Premium Member
Posts: 5
Joined: Tue Feb 07, 2017 5:47 pm

Post by jck77 »

hi ,
as Franklin and UCDI suggested, i broke into single statement using stage variables. then i created a new column with conditions.
This works and is much simpler

Thanks to all
Juan
Post Reply