[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