Archives of the TeradataForum

Message Posted: Thu, 04 Aug 2011 @ 13:50:41 GMT

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

Hi,

I have this requirement to replace NULL values of a particular column with the previous NOT NULL value in the same column ( when the table is sorted/partitioned on a particular column).

E.g. If the column has values like this -

     Col1

     1
     2
     ?
     ?
     8
     ?
     ?
     ?
     4
     ?

I want the column to be updated like this (a new column, say Col2, can also be created if updating is not possible) -

     Col1

     1
     2
     2   - Previously NULL

     2   - Previously NULL
     8
     8   - Previously NULL

     8   - Previously NULL
     8   - Previously NULL
     4

     4   - Previously NULL

I am using the following code to generate a new column -

     create table temp_DB.adas_tmp1_div7_03 as ( sel
                 a.*,
            case when a.act_oh_invnt is null
                 then (cast(sum(case when a.act_oh_invnt is null
                                     then 0 else a.act_oh_invnt end)
             over (partition by ftyirlsku order by wk_no rows
                   between 1 preceding and current row) as decimal(15,2)))
                             else a.act_oh_invnt end as aoh_temp
                 from temp_DB.adas_tmp1_div7_02 a) with data primary index (ftyirlsku,wk_no);

But the problem is this code is failing when there are more than 2 consecutive NULL's.

Kindly help.

Thanks,

Debojit.

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