Home Page for the TeradataForum

Archives of the TeradataForum

Message Posted: Thu, 04 Aug 2011 @ 20:16:56 GMT

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

Subj:   Re: Replacement of NULL value with previous NOT NULL value in the same column
From:   Curley, David


Try something like this for the query:

     sel lvl2.*,
     max(act_oh_invnt) over (partition by ftyirlsku, set_id)
     (-- lvl2 carry row number from is_first_of_set to all rows in set as
     sel lvl1.*,
     max(is_first_of_set) over (partition by ftyirlsku order by wk_no rows
     between unbounded preceding and current row) set_id
     (-- lvl1 row preceding 1 or more nulls is identified by row number
     sel a.*,
     case when a.act_oh_invnt is not null -- need to do this in case last row
     in partition has null act_oh_invnt
               max(act_oh_invnt) over (partition by ftyirlsku order by wk_no
                                       rows between 1 following and 1 following) is null
          then row_number() over (partition by ftyirlsku order by wk_no)
     from temp_DB.adas_tmp1_div7_02 a) lvl1) lvl2

Lvl1 checks each non-null act_oh_invnt to see if the next one is null. If so, the row is identified by its row number within the partition.

Lvl2 assigns that row number to all following null rows until a new row number is hit, calling that set_id

The outermost query gets the max act_oh_invnt over each ftyirlsku/set_id partition, which will be the only non-null act_oh_invnt in the partition.

Might be some typos or a step only partially implement, but ought to be enough to get you started.


  <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: 15 Jun 2023