Page 1 of 1
IF then else not working in transformer
Posted: Tue Apr 22, 2008 2:42 am
by s_porkalai
Hi All ,
Please find the below job design
Code: Select all
sqfile -> sort_stage -> remove_dup -> transformer -> sqfile
And inside transformer i am using the following expression
Code: Select all
If (trim(link1.col1,' ','A')='msg1') then 'jb1'
else if ((trim(link1.col1,' ','A')='msg2') or (trim(link1.col1,' ','A')='msg3')) then 'jb2'
else 'Unknown'
The issue we are facing is, when we ran the job for the 1st few iterations the conditional statement given above is working fine.
But after running the same job for several iteration (upto 10 runs), the conditional statement is not working and
we are getting the output as 'Unknown' for all the incoming values(even if the incoming values are 'msg1' or 'msg2').
the length of link1.col1 is varchar(16).
Can anyone please help me to get rid of this strange error.
Posted: Tue Apr 22, 2008 2:53 am
by balajisr
Is there any difference between the iteration which worked and which did'nt. E.g Job Parameters etc..
Is there any pattern you can infer as to first X iterations works and last Y iterations does not work?
Posted: Tue Apr 22, 2008 3:28 am
by s_porkalai
No all the iterations are working with the same data.
even when i try to run the job in sequencer using start loop and end loop
ie
Code: Select all
startloop -> job-> endloop
| |
|---------------|
activity 'if condition' is working fine for first few iteration in loop but it is giving ' UNKNOWN' after several runs
Posted: Tue Apr 22, 2008 4:01 am
by OddJob
As a matterof good practice, you may want to consider putting your 'trim' in a Stage Variable e.g.
svTrimCol1 = trim(link1.col1,' ','A')
Field derivation is then:
If (svTrimCol1='msg1') then 'jb1'
else
if ((svTrimCol1='msg2') or (svTrimCol1='msg3')) then 'jb2'
else 'Unknown'
It's more effiecient to perform the derivation once in a stage variable.
To test what's really going on, send the rows down a new link from the transformer to a Peek stage. Pass the following rows:
link1.col1
svTrimCol1
You may also want to use squote instead:
squote(link1.col1)
squote(svTrimCol1)
This will help you identify any whitespace that's sometimes difficult to spot in the peek logs.
Check peek output to confirm you're getting what you expected.
Also, be careful of NULLs going to a stage variable, it won't work the way you're expecting!
Posted: Tue Apr 22, 2008 4:03 am
by OddJob
As a matterof good practice, you may want to consider putting your 'trim' in a Stage Variable e.g.
svTrimCol1 = trim(link1.col1,' ','A')
Field derivation is then:
If (svTrimCol1='msg1') then 'jb1'
else
if ((svTrimCol1='msg2') or (svTrimCol1='msg3')) then 'jb2'
else 'Unknown'
It's more effiecient to perform the derivation once in a stage variable.
To test what's really going on, send the rows down a new link from the transformer to a Peek stage. Pass the following rows:
link1.col1
svTrimCol1
You may also want to use squote instead:
squote(link1.col1)
squote(svTrimCol1)
This will help you identify any whitespace that's sometimes difficult to spot in the peek logs.
Check peek output to confirm you're getting what you expected.
Also, be careful of NULLs going to a stage variable, it won't work the way you're expecting!
Posted: Wed Apr 23, 2008 12:57 am
by s_porkalai
we have tried with all the options - trim ,peek, squote. but the result is same . and the data we view through peek state is correct only.
one intersting fact is when we use alpha function on link1.col1 it is giving result as 0, eventhough there is no non alphabet characters in the incoming data.
Posted: Wed Apr 23, 2008 1:59 am
by ray.wurlod
Could it be that there are some non-printable characters in your data?