Page 1 of 1
Grouping/Looping in datastage
Posted: Thu Aug 29, 2013 7:04 am
by priyankalaisatwar
hi,
My input is (db2 tables after joins)
Code: Select all
MATERIAL_No PLANT VENDOR_CD FLAG
1009417 FR02 40010458 N
1009417 FR02 40020110 Y
1009417 FR02 40002482 N
I need output like-
For same MaterialNumber and same plant Where Flag =y, I want to populate its respective Vendor code in FIX_VENDOR column for all rows where Material_number=1009417 and Plant=FR02
Output:-
Code: Select all
MATERIAL_No PLANT VENDOR_CD FLAG FIX_VENDOR_CODE
1009417 FR02 40010458 N 40020110
1009417 FR02 40020110 Y 40020110
1009417 FR02 40002482 N 40020110
Please help me how i will do that.
Posted: Thu Aug 29, 2013 7:13 am
by chulett
Welcome aboard.
I cleaned up your examples using '
Code: Select all
' tags to preserve the whitespace you need so the columns line up. By default the forum software removes all 'extra' whitespace from a post. FYI.
That being said, I don't see a need for any looping or grouping - this looks like a classic 'fork join' to me. You can do an exact search here for those two words to get lots of examples but basically you want to split (fork) the job stream sending all of the 'Y' records down one branch and all records down the other than then join them back together on their business key(s) after that. Each record will pick up the proper 'Y' value.
Posted: Thu Aug 29, 2013 7:22 am
by priyankalaisatwar
Can you please just give eample for fork join. How to design it?
Posted: Thu Aug 29, 2013 7:40 am
by chulett
Sorry but could you please do the Exact Search here that I mentioned? It's all out there, don't see a need to type it all up yet again.
Posted: Thu Aug 29, 2013 9:27 pm
by SURA
I haven't tried with fork join either, but your example seems simple i guess.
Take two out put from your source and 1 with all outputs and the other with a where FLAG = Y . Use LKUP where clause for reference and bring the columns out. Use the TFM to populate the addition column value.
Posted: Thu Aug 29, 2013 10:49 pm
by SURA
Or you can use a SQL Query which i have written in SQL Server
Code: Select all
select
A.MATERIAL_No
,A.PLANT
,A.VENDOR_CD
,A.FLAG
, B.VENDOR_CD as FIX
from fjoin A,fjoin B
where A.MATERIAL_No = B.MATERIAL_No and
B.VENDOR_CD in ( select VENDOR_CD from fjoin where flag = 'Y')
Herewith the output data.
Code: Select all
MATERIAL_No PLANT VENDOR_CD FLAG FIX
1009417 FR02 40010458 N 40020110
1009417 FR02 40020110 Y 40020110
1009417 FR02 40002482 N 40020110
1009418 FR03 50000000 Y 50000000
1009418 FR03 50000001 N 50000000
Posted: Fri Aug 30, 2013 12:16 am
by priyankalaisatwar
Thanks, I will try this