Archives of the TeradataForum

Message Posted: Mon, 10 Mar 2008 @ 22:36:38 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Hello,

I have a question on how to handle NULLs enclosed by real data in OLAP function, specifically cumulative sum.

Here is my original data:

     Transid     customer_id        amt
     176265745   5545645555156058   NULL
     176294417   5545645555156058   NULL
     176315567   5545645555156058   -42.51
     176353072   5545645555156058   NULL
     176356373   5545645555156058   -19.99

I use OLAP function to compute cumulative sum:

     SELECT
     -- Sender Gross CB
     zeroifnull(COUNT(amt) OVER (PARTITION BY s.customer_id
                                    ORDER BY s.transid ASC
                                       ROWS BETWEEN UNBOUNDED PRECEDING AND
     CURRENT ROW
                                  )
                )
                  AS cumulative_cnt
     ,zeroifnull(SUM(amt) OVER (PARTITION BY s. customer _id
                                   ORDER BY s.transid ASC
                                     ROWS BETWEEN UNBOUNDED PRECEDING AND
     CURRENT ROW
           )
          )
                  AS cumulative_amt

which produces the dataset below:

     Transid     customer_id        Cumulative_cnt   Cumulative_amt
     176265745   5545645555156058         0               0.00
     176294417   5545645555156058         0               0.00
     176315567   5545645555156058         1             -42.51
     176353072   5545645555156058         1             -42.51
     176356373   5545645555156058         2             -62.50

But I need to exclude a second from the bottom row with amt = NULL with transid = 176353072 but keep the transaction id in the result.

     Transid     customer_id        Cumulative_cnt   Cumulative_amt
     176265745    5545645555156058        0               0.00
     176294417    5545645555156058        0               0.00
     176315567    5545645555156058        1             -42.51
     176353072    5545645555156058        0                  0
     176356373    5545645555156058        2             -62.50

I achieve desired result by separating datasets based on IS NULL and IS NOT NULL, processing them separately and then UNION, but it seems not really elegant.

I am wondering is there a way to handle this logic inside OLAP function?

Thanks a lot in advance,

Peter.

	<Prev	Next>		<<First	<Prev	Next>	Last>>