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.
IN logic in Datastage
Moderators: chulett, rschirm, roy
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.
'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.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.