Re: interesting query problem, how to resolve? Analytic functions?

Anurag,

This database is 8.1.7.4.0.

You are right - my example negected the join between member and claim.

The real query that works is a little complex to use as an example, so I have isolated it to just the example tables you see here.

What do you think about the analytic approach? I was thinking something along the lines of this code, which I wrote about six months ago (sorry for the mess, just look at the case/when/row_number() parts):

', case'
' when row_number() over (partition by source.cycle_gid,
source.bskt_gid, source.drug_gid, source.rbate_lvl_id, source.tier_id'
' order by ((dn.rbat_access/dn.sum_mdq) * source.unit_qty) -
trunc((dn.rbat_access/dn.sum_mdq) * source.unit_qty,2) desc) /100'
' <='
' dn.rbat_access - sum(trunc((dn.rbat_access/dn.sum_mdq) *
source.unit_qty,2))'
' over (partition by source.cycle_gid, source.bskt_gid,
source.drug_gid, source.rbate_lvl_id, source.tier_id)'
' then trunc ((dn.rbat_access/dn.sum_mdq) * source.unit_qty,2) + .01'
' else trunc ((dn.rbat_access/dn.sum_mdq) * source.unit_qty,2)'
' end rbate_access'

Except, that is not exactly what this code does - this actually figures out the remaining money of a given set of rows (partition in the analytic) and distributes the leftover pennies based on the position of the rows in the partition.

What I think I would like to do is use analytic functions and a case statement to return a psuedocolumn value for the all the rows in a partition if any have a given value, and then using an outer select to filter those rows with the given psuedo column value. I am trying to figure out what function to use to assign values to all the rows in a partition based on one row.

Thanks,

Jack Received on Wed May 28 2003 - 10:59:55 CDT