Calculating value based on parent/child relations for a column..
621112Feb 2 2008 — edited Feb 3 2008Hi Friends,
I have a requirement thus,
sample table, parts,
ppart cpart qty
990 1234 200
100 100_1 150
100 100_2 2
100_1 120 100
100_1 121 200
100_2 130 50
where qty is a number and the rest are varchar fields. Here the ppart 100 is a parent value for cpart values 100_1 and 100_2. So, I need to multiply the qty value for a child with the parent's qty values. The final result would look like,
ppart cpart qty
990 1234 200
100 100_1 150
100 100_2 2
100_1 120 15000
100_1 121 30000
100_2 130 100
I have only a basic understanding of SQL but I couldnt figure out a way for this. My best try was to join the table with itself and equate the ppart and cpart columns to retrieve the qty. However, the actual table has about 50 million records and I guess my idea is not going to please my DBA! I would appreciate if you could suggest better ideas.
Thanks.