IN logic in Datastage

[skp]

Hi All,

I have a parameter prmDivisionCode with values ('Z5','FG','3H','8M'),This parameter values will increase or decrease every day.

In the Datastage I want a logic to check Division code in the above parameter having listed values.

This is same as IN clause in SQL,how can we handle this in datastage.

Can any one help me .

Thanks in advance.

[ssnegi]

From a row generator generate a row with the values
'Z5','FG','3H','8M' as a varchar fields col1,col2,col3,col4
Then in a transformer put the prmDivisionCode into separate fields
suppose 'Z5','FG' as col1 and col2. col3 and col4 are null if no value is received. if another value is received suppose 'AB' in col3.
add join stage and join using full outer join. left row generator link, right transformer link.
join keys col1,col2,col3,col4.
Then in transformer compare the values for the 4 columns.
ex : if left.col1 = right.col1 or isnull(right.col1) then 1 else 2
do this for all the 4 columns.
so col1,col3,col4 will have values 1 and col3 will have value 2.
So we can tell if the parameter is a subset of the whole list of values from the row generator.

[ray.wurlod]

I think ssnegi has missed the vital point that the list will be dynamic.

[ray.wurlod]

You could probably use Transformer looping to process this. Initialize a stage variable to the number of comma-delimited elements in the parameter value. The loop exit condition compares @ITERATION system variable with this value. Use the Field() function to extract each value and then perform the test for equality.

[chulett]

Isn't this simply an Index() function call?

[yugee]

If these values are dynamic, better use lookup. Keep these values in either a file or table (depends on your access) then you won't have hassle of changing the datastage job every time these values change.

[chulett]

The values are in a job parameter according to the original post, so there's no need to change the job for a values change.