Home Page for the TeradataForum
 

Archives of the TeradataForum

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


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


Subj:   Replacement of NULL value with previous NOT NULL value in the same column
 
From:   debojit.pal

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>>  
 
 
 
 
 
 
 
 
  
  Top Home Privacy Feedback  
 
 
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 27 Dec 2016