Tuesday, July 1, 2014

Filling blank field with previous row value in PL/SQL

Suppose I have Table named emp like this
empno comm
033001 RUET
033002
033003
033004 KUET
033004
033005 CUET
Now I want to fill the blank cells with the previous value. Such as comm of 033002, 033003  will be RUET. comm of 033004 will be KUET. How can I do this? I had googled and found an interesting query.
1. I have to execute following query:
select empno,comm, lag(comm ignore nulls) over (order by empno)prev_comm from emp order by empno.
Then I get a view like this :
empno comm prev_comm
033001 RUET
033002
RUET
033003
RUET
033004 KUET RUET
033004
KUET
033005 CUET KUET


CUET
2. Suppose the previous view is represented by mm.
Then I have to write the following query on mm view.
select empno, case when comm is null then prev_comm else comm end as comm from mm.
Then we will get the desired view :
empno comm
033001 RUET
033002 RUET
033003 RUET
033004 KUET
033004 KUET
033005 CUET
So, the final query will be:
select empno, case when comm is null then prev_comm else comm end as comm from (select empno,comm, lag(comm ignore nulls) over (order by empno)prev_comm from emp order by empno)

No comments: