Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, karthick.

Asked: June 09, 2009 - 2:18 am UTC

Last updated: June 10, 2009 - 12:06 pm UTC

Version: 10g

Viewed 1000+ times

You Asked

This type of question comes up quite often in oracle forum.

Below is the test script


create table test(col_1 number, col_2 number,col_3 number);

insert into test values (1,1,4);
insert into test values (1,2,null);
insert into test values (1,3,null);
insert into test values (1,4,null);
insert into test values (1,5,5);
insert into test values (1,6,null);
insert into test values (2,1,2);
insert into test values (2,2,null);
insert into test values (2,3,7);
insert into test values (2,4,null);
insert into test values (3,5,null);
insert into test values (3,6,null);
insert into test values (4,1,null);
insert into test values (4,2,null);
insert into test values (4,3,null);


The data looks like this

SQL> select * from test
  2  /

     COL_1      COL_2      COL_3
---------- ---------- ----------
         1          1          4
         1          2
         1          3
         1          4
         1          5          5
         1          6
         2          1          2
         2          2
         2          3          7
         2          4
         3          5

     COL_1      COL_2      COL_3
---------- ---------- ----------
         3          6
         4          1
         4          2
         4          3



and the expected output is, i have done with MODEL but looking for a solution without that which is compatible in 9i .

SQL> select col_1, col_2, col_3
  2    from (select row_number() over(order by col_1, col_2) rno,
  3                 col_1, col_2, col_3
  4            from test)
  5    model
  6    return updated rows
  7    dimension by (rno)
  8    measures(col_1,col_2,col_3)
  9    rules update
 10    (
 11     col_3[any]= case when nvl(col_3[cv()],0) = 0 then nvl(col_3[cv()-1],0) else col_3[cv()] end
 12    )
 13  /

     COL_1      COL_2      COL_3
---------- ---------- ----------
         1          1          4
         1          2          4
         1          3          4
         1          4          4
         1          5          5
         1          6          5
         2          1          2
         2          2          2
         2          3          7
         2          4          7
         3          5          7

     COL_1      COL_2      COL_3
---------- ---------- ----------
         3          6          7
         4          1          7
         4          2          7
         4          3          7

15 rows selected.


that is replace the null values in col_3 with the previous not null value order by col_1 and col_2

Thanks,
Karthick.

and Tom said...

ops$tkyte%ORA10GR2> select col_1, col_2, col_3, <b>last_value(col_3 ignore nulls) over (order by col_1, col_2) lv</b>
  2    from test
  3   order by col_1, col_2
  4  /

     COL_1      COL_2      COL_3         LV
---------- ---------- ---------- ----------
         1          1          4          4
         1          2                     4
         1          3                     4
         1          4                     4
         1          5          5          5
         1          6                     5
         2          1          2          2
         2          2                     2
         2          3          7          7
         2          4                     7
         3          5                     7
         3          6                     7
         4          1                     7
         4          2                     7
         4          3                     7

15 rows selected.

Rating

  (8 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

caveat utilitor: user beware (or be aware) of LAST_VALUE

Duke Ganote, June 09, 2009 - 1:16 pm UTC

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1822122724803#tom1616395700346143909
Tom Kyte
June 10, 2009 - 8:42 am UTC

but - that is what actually makes this work?

Explicitly...

Duke Ganote, June 09, 2009 - 1:19 pm UTC

[Tom's] Followup April 13, 2009 - 12pm US/Eastern:
"when you use an order by with the analytic - and you do not specify a window (rows or range), the default window is the CURRENT ROW and everything PRECEDING it.

"So, the last_value would always be the CURRENT row without a window clause."

Tom Kyte
June 10, 2009 - 8:42 am UTC

and last value with the ignore nulls option would be.... the last row that contained a non-null value - it is precisely that fact that makes this "work" :)

versions ...

Gabe, June 10, 2009 - 12:50 am UTC 

"ignore nulls" won't work in 9i ... maybe something like:

select col_1, col_2, col_3, max(col_3) over (partition by grp order by col_1, col_2)
from
(
select col_1, col_2, col_3, sum(nvl(abs(col_3),0)) over (order by col_1, col_2) grp
from   test
)
order by 1,2


Tom Kyte
June 10, 2009 - 12:03 pm UTC

well, it was a 10g question....

but, you can use what I call a carry down technique in 9i and before:


ops$tkyte%ORA10GR2> select col_1, col_2, col_3,
  2         to_number( substr(max(new_data) over (order by col_1, col_2, rowid), 11)) newer_data
  3    from (
  4  select col_1, col_2, col_3,
  5         case when col_3 is not null
  6              then to_char(row_number() over (order by col_1, col_2, rowid),'fm0000000000') || to_char(col_3)
  7          end new_data
  8    from test
  9         )
 10  /

     COL_1      COL_2      COL_3 NEWER_DATA
---------- ---------- ---------- ----------
         1          1          4          4
         1          2                     4
         1          3                     4
         1          4                     4
         1          5          5          5
         1          6                     5
         2          1          2          2
         2          2                     2
         2          3          7          7
         2          4                     7
         3          5                     7
         3          6                     7
         4          1                     7
         4          2                     7
         4          3                     7

15 rows selected.



the 'trick' is to encode the data in a string that sorts (so max() works) and then substr your data back out.

Solution compatable in 9i

karthick pattabiraman, June 10, 2009 - 2:23 am UTC

>>and the expected output is, i have done with MODEL but looking for a solution without that which is compatible in 9i

IGNORE NULLS is not available in 9i

silly season

Gabe, June 10, 2009 - 2:24 am UTC 

Actually my sum/nvl/abs construct for groups is rather silly since a value of 0 will trip it ... and what if col_3 is not numeric? Maybe something with a better chance:

select col_1, col_2, col_3, max(col_3) over (partition by grp order by col_1, col_2) lv
from (
select col_1, col_2, col_3, max(ind) over (order by col_1, col_2) grp
from (
select col_1, col_2, col_3
      ,case when col_3 is not null then row_number() over (order by col_1, col_2) end ind
from test t
))
order by 1,2

Talking about col_3=0 here is a test case for which the model solution fails:

begin
insert into test values (0,0,null);
insert into test values (1,1,-4);
insert into test values (1,2,null);
insert into test values (1,3,null);
insert into test values (1,4,null);
insert into test values (1,5,4);
insert into test values (1,6,null);
insert into test values (2,1,0);
insert into test values (2,2,null);
insert into test values (2,3,-2);
insert into test values (2,4,null);
insert into test values (3,5,null);
insert into test values (3,6,null);
insert into test values (4,1,null);
insert into test values (4,2,null);
insert into test values (4,3,null);
end;




Tom Kyte
June 10, 2009 - 12:06 pm UTC

see above, I use row_number to "tag" data and encode it - making it sortable - lets you just use two layers of analytics to implement "last value, ignore nulls"

indeed the season

Gabe, June 10, 2009 - 2:41 am UTC


Well, if col_3 is numeric, maybe avoid the 3rd analytic pass and just work around the col_3=0 problem:

select col_1, col_2, col_3, max(col_3) over (partition by grp order by col_1, col_2) lv
from
(
select col_1, col_2, col_3, sum(nvl(abs(decode(col_3,0,1,col_3)),0)) over (order by col_1, col_2) grp
from   test
)
order by 1,2

see first question in November/December 2006 issue of Oracle Magazine

Duke Ganote, June 10, 2009 - 2:55 pm UTC

https://asktom.oracle.com/magazine-archive.htm

carrydown, the simple-minded approach dissected

Duke Ganote, June 15, 2009 - 7:33 pm UTC


select r#
, col_3
, r#_carrydown
, max(case when r# = r#_carrydown then col_3 end)
OVER (partition by r#_carrydown) col_3_carrydown
from (
select max(case when col_3 is not null then r# end)
OVER (order by r# asc) r#_carrydown
, r#
, col_3
from (
select row_Number() over
(order by col_1, col_2) r#
, col_3
from test
)
)
d1:cidw\cidwwrite> /

R# COL_3 R#_CARRYDOWN COL_3_CARRYDOWN
----- ---------- ------------ ---------------
1 4 1 4
2 1 4
3 1 4
4 1 4
5 5 5 5
6 5 5
7 2 7 2
8 7 2
9 7 9 7
10 9 7
11 9 7
12 9 7
13 9 7
14 9 7
15 9 7

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Analytics

Analytic SQL got you confused? Check out Connor McDonald's complete video course.