0
votes

Table:

col1         col2         col3
236          1234         lion
236          1234 
235          1023        
235          1234         
234          1234
232          1234
232          1234         tiger
231          1234
231          1234         cat

Aim to create a col4 and copy value from col3 to col4 if (with partition by on col2):

Wherever "cat" is present in col3, then check if col1+5 exists, if yes then value of col3 from "cat" row goes to that col4(whose col1 value is cat's "col1+5")

output:

 col1         col2         col3     col4
 236          1234         lion      
 236          1234                  cat
 235          1023
 235          1234         
 234          1234
 232          1234
 232          1234         tiger
 231          1234
 231          1234         cat
2

2 Answers

0
votes

You question is not well described, but looks like that is what you need:

with t(col1,col2,col3) as (
    select  236 ,1234,'lion'  from dual union all
    select  236 ,1234,''      from dual union all
    select  235 ,1023,''      from dual union all
    select  235 ,1234,''      from dual union all
    select  234 ,1234,''      from dual union all
    select  232 ,1234,''      from dual union all
    select  232 ,1234,'tiger' from dual union all
    select  231 ,1234,''      from dual union all
    select  231 ,1234,'cat'   from dual
)
select 
    t.*
   ,case
        when col3 is null then
            max(col3)over(partition by col2 order by col1 range between 5 preceding and 5 preceding) 
    end col4
from t
order by col1 desc,col2 asc,col3
/

Result:

      COL1       COL2 COL3  COL4
---------- ---------- ----- -----
       236       1234 lion
       236       1234       cat
       235       1023
       235       1234
       234       1234
       232       1234 tiger
       232       1234
       231       1234 cat
       231       1234

9 rows selected.
0
votes

If I understand correctly, you can use left join:

select t.*,
       (case when t.col3 is null then t5.col3 end) as col4
from t left join
     t t5
     on t5.col1 = t.col1 + 5 and t5.col2 = t.col2 and t5.col3 = 'cat'