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

Attachments

Library

Quick Reference