[thelist] SQL Question - 'Contingent Columns'
rudy
r937 at interlog.com
Tue Apr 16 12:47:01 CDT 2002
> The Case...When syntax does just that.
that's right, joshua
ron, try this -- it may not work (depending on what your column names
actually are) but it should give you an idea...
select p.PartNumber
, sum(p.Field_2 * r.mult2)
, sum(p.Field_3 * r.mult3)
, sum(p.Field_4 * r.mult4)
from ProductShipments p
, ( select Region
, case when Column = 'Field_2'
then 1 else 0 end as mult2
, case when Column = 'Field_3'
then 1 else 0 end as mult3
, case when Column = 'Field_4'
then 1 else 0 end as mult4
from UpdateStatus
where Timeperiod = sysdate -1 ) r
where p.Region = r.Region
group by p.PartNumber
you may need to fiddle with Timeperiod = sysdate -1
perhaps use TO_CHAR or TRUNC to get the comparison to work
start by running the nested select by itself, to make sure it's giving you
the appropriate 1's and 0's as multipliers in the right positions for each
region
rudy
More information about the thelist
mailing list