Query Quandry.

Anyone got a neat solution for this in 10.2.0.1? There's a table which looks something like this:

hid t1 t2 t3 t4 mpc id

 51   1                   10   1
 51   6                   20   2
 51   4                   70   3
 53   1   34               3   4
 53   1    1              22   5
 53   6   34              20   6
 53   4   34              35   7
 53   4  841              20   8

It's the result of merging two sets of data (hid 51 & hid 53). The t1 - t4 columns are numbers which can be null, & the mpc column is a percentage.

The basic rules for the data are that hid53 rows will always have more of the 't' columns populated than the hid51 rows. So the hid51 rows could have data in t1, t2 & t3, in which case the hid53 rows will have data in all 4 of the 't' columns. Alternatively the hid51 may just have data in t1, & the hid53 could have data in t1 & t2, or t1, t2 & t3, or t1, t2, t3 & t4.

Whatever the combination, if a particular hid(n)/t(n) field has a value, all the other fields for that hid(n)/t(n) will also have values. I.e. if there was a number in one of the hid53 t3 fields in the example above, every hid53 row would have a value in the t3 column.

Also, any numbers in any of the hid51 rows will be repeated in the same column in at least one hid53 row. So the fact that there's a 1, 6 & 4 in the hid51 t1 column means there must also be a 1, 6 & 4 in the t1 column with a hid of 53. Similarly if there was a hid51 row with a t1 of 1 & a t2 of 8, there would also be at least one hid53 row with a t1 of 1 & a t2 of 8.

If you've followed this so far, congratulations - I'm impressed.

The result I need to get (which I'll stick in an array of pl/sql table), assuming the above example, is:

hid t1 t2 t3 t4 mpc

 53   1   34             1.2
 53   1    1             8.8 
 53   6   34              20
 53   4   34           44.55
 53   4  841           25.45

So basically it's combining the hid51 & hid53 rows together. However the percentage (the mpc column) needs to be recalculated to retain the same allocation found in the hid51 rows. So the row at id 1 in the example gets merged with the rows at id 4 & 5, as the value in the t1 column is the same. However the mpc for row 1 is 10, so the two mpc values for rows 4 & 5 need to be re-apportioned to total 10, i.e. (3/(3+22))*10=1.2 for row 4, & (22/(3+22))*10=8.8 for row 5.

I'm sure there must be a way to do this using analytics, but I've yet to delve into those, & unlikely to have the time to do so anytime soon.

-- 
Preston.