Script categories

Thursday, 22 September 2011

Using NULLIF with Analytic functions

-- No zeros wanted in output, instead, must always get the previously non-zero value
with t as
(
select 'a' col1, 1 col2 from dual union all
select 'b', 5 from dual union all
select 'c', 0 from dual union all
select 'd', 0 from dual union all
select 'e', 3 from dual union all
select 'f', 8 from dual union all
select 'g', 0 from dual
)
--
select col1
      ,last_value(nullif(col2,0) ignore nulls)  over (order by col1) col2
from t
order by col1
/

C       COL2
- ----------
a          1
b          5
c          5
d          5
e          3
f          8
g          8 

No comments:

Post a Comment