Grouping/Looping in datastage

[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.

[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.

[priyankalaisatwar]

Can you please just give eample for fork join. How to design it?

[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.

[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.

[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

[priyankalaisatwar]

Thanks, I will try this