DSXchange

Hi ,

The datastage job in figure shows compile time error at the transformer stage.

http://img444.imageshack.us/my.php?image=dsjobvc4.jpg

As in figure I have an input column cms_p11d.orderno which I map to two columns in the output table. One mapping of the input column is used with an if statement.

if cms_p11d.ORDERNO like 'R%' then 'Reallocation Order' else if cms_p11d.ORDERNO like 'S%' then 'STH Car Order' else if cms_p11d.ORDERNO like '0%' then 'New Car Order' else if cms_p11d.ORDERNO like '1%' then 'New Car Order' else if cms_p11d.ORDERNO like '2%' then 'New Car Order' else if cms_p11d.ORDERNO like '3%' then 'New Car Order' else if cms_p11d.ORDERNO like '4%' then 'New Car Order' else if cms_p11d.ORDERNO like '5%' then 'New Car Order' else if cms_p11d.ORDERNO like '6%' then 'New Car Order' else if cms_p11d.ORDERNO like '7%' then 'New Car Order' else if cms_p11d.ORDERNO like '8%' then 'New Car Order' else if cms_p11d.ORDERNO like '9%' then 'New Car Order' else 'Gap Car Order'

Removing the 'If' statement (as in figure below) allows me to compile and run the job. But its essential that I have the 'If' statement.

http://img183.imageshack.us/my.php?image=dsjob2mx6.jpg

Please suggest an alternative.

Thanks
Athira

We don't have Like in datastage instead use Matches. Take a look at the help for matching pattern creation.

Or put all that in the source query - if there is a source query, that is.

Hi,

Changed the query as follows and its working:

if Substrings(cms_p11d.ORDERNO,1,1) = 'R' then 'Reallocation Order' else if Substrings(cms_p11d.ORDERNO,1,1) = 'S' then 'STH Car Order' else if Substrings(cms_p11d.ORDERNO,1,1) = '0' then 'New Car Order' else if Substrings(cms_p11d.ORDERNO,1,1) = '1' then 'New Car Order' else if Substrings(cms_p11d.ORDERNO,1,1) = '2' then 'New Car Order' else if Substrings(cms_p11d.ORDERNO,1,1) = '3' then 'New Car Order' else if Substrings(cms_p11d.ORDERNO,1,1) = '4' then 'New Car Order' else if Substrings(cms_p11d.ORDERNO,1,1) = '5' then 'New Car Order' else if Substrings(cms_p11d.ORDERNO,1,1) = '6' then 'New Car Order' else if Substrings(cms_p11d.ORDERNO,1,1) = '7' then 'New Car Order' else if Substrings(cms_p11d.ORDERNO,1,1) = '8' then 'New Car Order' else if Substrings(cms_p11d.ORDERNO,1,1) = '9' then 'New Car Order' else 'Gap Car Order'

Thanks
Athira

Then time to mark the post as Resolved, it would seem.

How many times are you evaluating Substrings(cms_p11d.ORDERNO,1,1)?

Seems to me a more efficient approach would be a stage variable, to evaluate Left(cms_p11d.ORDERNO,1) just once would be far more efficient.

Use the stage variable name in your compound If..Then..Else expression.

The logic breaks down further.