Siva Academy

Youtube.com/SivaAcademy  Oracle SQL  |  PLSQL  |  SQL PLSQL Interview Questions  |  PLSQL Scenario based Interview Questions  |  Subscriber Questions & Answers

  |  SQL practical Question  |  SQL Performance Tuning  |  New Features  |  

  Trigger     |    View  

Saturday, September 14, 2019

Expand and Fill the missing dates with last not null value



Input and Expected Output:


















Data Setup:

create table tx_data(dt date, amnt number);

insert into tx_data values(to_date('20-JUL-2019','DD-MON-YYYY'),100);
insert into tx_data values(to_date('25-JUL-2019','DD-MON-YYYY'),200);
insert into tx_data values(to_date('30-JUL-2019','DD-MON-YYYY'),300);

commit;

Query:

with d1 as (
select min(dt) mi,max(dt) mx, max(dt)-min(dt) num
from tx_data),
dt_range as (select level-1+mi dt
from d1
connect by level <=num+1)
select dt_range.dt,
       last_value(amnt ignore nulls) over(order by dt_range.dt) amnt
from dt_range left outer join tx_data
on (dt_range.dt = tx_data.dt)

order by 1;

2 comments:

  1. Hi Siva Sir,
    There's a materialized view which should be refreshed every one hour as per the below two commands
    REFRESH ON DEMAND
    TO_START(.......) ...SYSDATE +1/24
    but when we query the below to check refresh time..

    Select * from all_mviews;

    It's not getting refresh every one hour.
    Note : All mviews updating as per the referesh time

    Is there any disk space issue?
    What might be the reason?

    Kindly help!

    ReplyDelete
  2. Sir my self ujjwal I'm from Lucknow ...I want to learn ETL(Informatica)...pls call me 8400646059.... Mail id : ujjwal18@gmail.com

    ReplyDelete