Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Thu, 04 Aug 2011 @ 21:04:46 GMT


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


Subj:   Re: Replacement of NULL value with previous NOT NULL value in the same column
 
From:   Dieter Noeth

debojit.pal wrote:

  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).  


In Standard SQL this would be the LAST_VALUE function.

In Teradata it's:

     select
        col1,
        min(col1) over (partition by pcol, dynamic_partition)
     from
       (
        select
          pcol,
          col1,
          sum(case when col1 is not null then 1 else 0 end)
          over (partition by pcol
                order by col1
                rows unbounded preceding) as dynamic_partition
        from tab
       ) as dt

In TD13.00 you could also use:

     select
        col1,
        min(col1)
        over (partition by pcol
              order by col1
              reset when col1 is not null
              rows unbounded preceding)
     from tab

Both version will show exactly the same explain, RESET WHEN is just shorter.


Dieter



     
  <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