Picking up rate for all dates from few day’s rate

I want to discuss an important query just now. I have done today it. Suppose, I have a rate_info table like this

dt	rate
01/01/2011	24.50
5/01/2011	23.45
15/01/2011	20.34
25/01/2011	28.22
10/02/2011	15.25

Now I have to calculate rate for all dates. The rate will be same until the rate has been changed. Then the query will be like this:

select a.*,(select rate from rate_info where dt=(select max(dt) from rate_info where dt <=a.dt_new))rate_new from
(select to_char((select min(dt) from rate_info)+(level – 1),’DD-MON-RRRR’)dt_new from dual
    connect by level <= ((select max(dt) from rate_info) – (select min(dt) from rate_info)+1))a

If  a single day contain multiple rate with different version like this:

dt	rate	Version
01/01/2011	24.50	1
5/01/2011	23.45	1
15/01/2011	20.34	1
25/01/2011	28.22	1
10/02/2011	15.25	1
10/02/2011	13.24	2

Then the rate with maximum version will be picked up and query will be like this:
select a.*,(select rate from rate_info where dt=(select max(dt) from rate_info where dt <=a.dt_new)
and version =(select max(version) from rate_info where dt=(select max(dt) from rate_info where dt<=a.dt_new )
))rate_new from
(select to_char((select min(dt) from rate_info)+(level – 1),’DD-MON-RRRR’)dt_new from dual
connect by level <= ((select max(dt) from rate_info) – (select min(dt) from rate_info)+1))a

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)