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
' 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.
-craig
"You can never have too many knives" -- Logan Nine Fingers
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.
Thanks
Ram
----------------------------------
Revealing your ignorance is fine, because you get a chance to learn.
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')
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
Thanks
Ram
----------------------------------
Revealing your ignorance is fine, because you get a chance to learn.