DataStage Job - Error in compiling job at transformer stage

[AthiraM]

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

[loveojha2]

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

[chulett]

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

[AthiraM]

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

[chulett]

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

[ray.wurlod]

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.

Code: Select all

If svOrderType = 'R' then 'Reallocation Order' Else If svOrderType Matches "1N" Then "New Car Order" Else "Gap Car Order"