Wednesday, December 22, 2010

Generate intermediate values using subquery factoring

Today I wrote a query generate missing values for example table bank which contained data as show below
ACCNO    DOD              AMT
100          02-JAN-10     2000
200          10-JAN-10     1000
100          08-JAN-10     4000



If you observe for accno 100 there are no entries after 02-JAN-2010 till 08-JAN-2010 and amt in this account will be 2000 till 07-JAN-2010 but if we require to generate intermediate values then following query is written to generate values 



with date_range as
(
select max(dod) maxdt,min(dod) mindt from bank
),
date_seq as
(
select mindt+level-1 final_date from date_range
connect by level  <= (maxdt-mindt)+1
)
select to_number( substr(
            max( case when accno is not null
                      then to_char(date_seq.final_date,'yyyymmdd')||accno
                  end ) over (order by date_seq.final_date)
           , 9 ) ) accno,date_seq.final_date
,to_number( substr(
            max( case when amt is not null
                      then to_char(date_seq.final_date,'yyyymmdd')||amt
                  end ) over (order by date_seq.final_date)
           , 9 ) ) amt
from bank,date_seq where date_seq.final_date=bank.dod(+) order by final_date