Splitting 1 record into multiple record based on Quantity
Posted: Thu Aug 20, 2009 8:04 am
I have an urgent requirement where i have to split a record based on the value present in one column called 'Quantity'.
e.g
Input
---------
col1 Quantity Price
---- -------- -----
1 2 p1
2 5 p2
Output
--------
col1 Quantity Price
---- -------- -----
1 1 p1/2
1 1 p1/2
2 1 p2/5
2 1 p2/5
2 1 p2/5
2 1 p2/5
2 1 p2/5
What is happening here , is if there is a product sold 2 times , we need to insert two entry of that product in the target and the price value of each entry would be Price/Quantity .
I don't know how to accomplish this task in Datastage , or through SQl Query .
If anybody having any idea , kindly help .
e.g
Input
---------
col1 Quantity Price
---- -------- -----
1 2 p1
2 5 p2
Output
--------
col1 Quantity Price
---- -------- -----
1 1 p1/2
1 1 p1/2
2 1 p2/5
2 1 p2/5
2 1 p2/5
2 1 p2/5
2 1 p2/5
What is happening here , is if there is a product sold 2 times , we need to insert two entry of that product in the target and the price value of each entry would be Price/Quantity .
I don't know how to accomplish this task in Datastage , or through SQl Query .
If anybody having any idea , kindly help .