Oracle Lag function with dynamic parameter

Question

I have a specific problem. I have a table that contains invalid values. I need to replace the invalid values ​​(here 0) with the previous value, which is greater than 0.

The difficulty is that I am not allowed to use update or insert (cursor and update will do this). Use my only way is to use the Select statement.

When I use the lag(col1, 1)- function in the case, I get only one column with the correct value.

select col1, col2 realcol2,  
(case 
  when col2 = 0 then 
    lag(col2,1,1) over (partition by col1 order by col1 )
  else
   col2
  end ) col2,     
col3 realcol3,
(case 
  when col3 = 0 then 
    lag(col3,1,1) over (partition by col1 order by col1 )
  else
   col3
  end ) col3
from test_table 

Content TEST_TABLE:

---------------------------
 Col1 | Col2 | Col3 | Col4
---------------------------
  A   |  0   |  1   |  5
  B   |  0   |  4   |  0
  C   |  2   |  0   |  0
  D   |  0   |  0   |  0
  E   |  3   |  5   |  0
  F   |  0   |  3   |  0
  G   |  0   |  3   |  1
  A   |  0   |  1   |  5
  E   |  3   |  5   |  0

Expected query result:

---------------------------
 Col1 | Col2 | Col3 | Col4
---------------------------
  A   |  0   |  1   |  5
  B   |  0   |  4   |  5
  C   |  2   |  4   |  5
  D   |  2   |  4   |  5
  E   |  3   |  5   |  5
  F   |  3   |  3   |  5
  G   |  3   |  3   |  1
  A   |  3   |  1   |  5
  E   |  3   |  5   |  5

Answer 1

SELECT col1,
       CASE col2 WHEN 0 THEN NVL( LAG( CASE col2 WHEN 0 THEN NULL ELSE col2 END ) IGNORE NULLS OVER ( ORDER BY NULL ), 0 ) ELSE col2 END AS col2,
       CASE col3 WHEN 0 THEN NVL( LAG( CASE col3 WHEN 0 THEN NULL ELSE col3 END ) IGNORE NULLS OVER ( ORDER BY NULL ), 0 ) ELSE col3 END AS col3,
       CASE col4 WHEN 0 THEN NVL( LAG( CASE col4 WHEN 0 THEN NULL ELSE col4 END ) IGNORE NULLS OVER ( ORDER BY NULL ), 0 ) ELSE col4 END AS col4
FROM   table_name;

Result

COL1       COL2       COL3       COL4
---- ---------- ---------- ----------
A             0          1          5 
B             0          4          5 
C             2          4          5 
D             2          4          5 
E             3          5          5 
F             3          3          5 
G             3          3          1 
A             3          1          5 
E             3          5          5

Answer 2

col0, , col1 (, A E).

MODEL . :

WITH t(col0, col1, col2, col3, col4) AS (
  SELECT 1, 'A', 0, 1, 5 FROM DUAL UNION ALL
  SELECT 2, 'B', 0, 4, 0 FROM DUAL UNION ALL
  SELECT 3, 'C', 2, 0, 0 FROM DUAL UNION ALL
  SELECT 4, 'D', 0, 0, 0 FROM DUAL UNION ALL
  SELECT 5, 'E', 3, 5, 0 FROM DUAL UNION ALL
  SELECT 6, 'F', 0, 3, 0 FROM DUAL UNION ALL
  SELECT 7, 'G', 0, 3, 1 FROM DUAL UNION ALL
  SELECT 8, 'A', 0, 1, 5 FROM DUAL UNION ALL
  SELECT 9, 'E', 3, 5, 0 FROM DUAL
)
SELECT * FROM t
MODEL
  DIMENSION BY (row_number() OVER (ORDER BY col0) rn)
  MEASURES (col1, col2, col3, col4)
  RULES (
    col2[any] = DECODE(col2[cv(rn)], 0, NVL(col2[cv(rn) - 1], 0), col2[cv(rn)]),
    col3[any] = DECODE(col3[cv(rn)], 0, NVL(col3[cv(rn) - 1], 0), col3[cv(rn)]),
    col4[any] = DECODE(col4[cv(rn)], 0, NVL(col4[cv(rn) - 1], 0), col4[cv(rn)])
  )

:

RN   COL1  COL2  COL3  COL4
1    A     0     1     5
2    B     0     4     5
3    C     2     4     5
4    D     2     4     5
5    E     3     5     5
6    F     3     3     5
7    G     3     3     1
8    A     3     1     5
9    E     3     5     5

SQLFiddle

MODEL ,

( , ), , , , , nop77svk ( LAST_VALUE() IGNORE NULLS) MT0 ( LAG() IGNORE NULLS). .

Answer 3

Assuming you need the previous values ​​according to your original data order (whatever that is), your query might look like this:

with preserve_the_order$ as (
    select X.*,
        rownum as original_order$
    from test_table X
)
select X.col1,
    nvl(last_value(case when col2 > 0 then col2 end) ignore nulls over (order by original_order$ rows between unbounded preceding and current row), col2) as col2,
    nvl(last_value(case when col3 > 0 then col3 end) ignore nulls over (order by original_order$ rows between unbounded preceding and current row), col3) as col3,
    nvl(last_value(case when col4 > 0 then col4 end) ignore nulls over (order by original_order$ rows between unbounded preceding and current row), col4) as col4
from preserve_the_order$ X
order by original_order$
;

Result:

COL1       COL2       COL3       COL4
---- ---------- ---------- ----------
A             0          1          5
B             0          4          5
C             2          4          5
D             2          4          5
E             3          5          5
F             3          3          5
G             3          3          1
A             0          1          5
E             3          5          5