Re: Decode a range?

You can use sign function:

select ...

      sum(decode(sign(p.period_key-19970629), -1, 0,
                 decode(sign(19970705-p.period_key), -1, 0,
                        p.period_key)) as week1,
      ...

Ken Leach a écrit dans le message <37DF99FC.3ABE553D_at_rs-net.com>...
>I am trying to make a normalized table horizontal..
>
>I have a HUGE query that UNIONS everthing, it works but has problems
>with parallel query... SO... I would like to make the same query work
>with decode or some other method than UNION...
>
>select i.upc_id, i.item_desc, i.item_key, i.category, i.brand,
> sum(decode(p.period_key,between 19970629 and
>19970705,p.total_dollars,0) as week1,
> sum(decode(p.period_key,between 19970706 and
>19970712,p.total_dollars,0) as week2
>from pos p, item_dim i
>where p.vendor_key = 38 and p.summary_key = 1 and p.retailer_key = 1
>and p.period_key >=1990629 and p.period_key <= 19970712
>and p.vendor_id = 2145
>and p.item_key = i.item_key
>group by i.upc_id, i.item_desc, i.item_key, i.category, i.brand
>
>This decode does NOT work... I do not know how to get the period between
>two values.. just a single value compare... I know there MUST be a
>little trick I am missing...
>
>Suggestions?
>
>-Ken
>
Received on Wed Sep 15 1999 - 08:37:46 CDT