Archives of the TeradataForum
Message Posted: Thu, 04 Aug 2011 @ 13:50:41 GMT
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.
|Copyright 2016 - All Rights Reserved|
|Last Modified: 28 Jun 2020|