Duplicating records to fill gap between dates

You can create a row generator statement using the CONNECT BY LEVEL syntax, cross joined with the distinct products in your table, and then outer join that to your prices table. The final touch is to use the LAST_VALUE function and IGNORE NULLS to repeat the price until a new value is encountered, and since you wanted a view, with a CREATE VIEW statement:

create view dense_prices_test asselect    dp.price_date  , dp.product  , last_value(pt.price ignore nulls) over (order by dp.product, dp.price_date) pricefrom (      -- Cross join with the distinct product set in prices_test      select d.price_date, p.product      from (            -- Row generator to list all dates from first date in prices_test to today            with dates as (select min(price_date) beg_date, sysdate end_date from prices_test)            select dates.beg_date + level - 1 price_date             from dual            cross join dates            connect by level <= dates.end_date - dates.beg_date + 1            ) d      cross join (select distinct product from prices_test) p     ) dpleft outer join prices_test pt on pt.price_date = dp.price_date and pt.product = dp.product;

I think I have a solution using an incremental approach toward the final result with CTE's:

with mindate as(  select min(price_date) as mindate from PRICES_TEST),dates as(  select mindate.mindate + row_number() over (order by 1) - 1 as thedate from mindate,    dual d connect by level <= floor(SYSDATE - mindate.mindate) + 1),productdates as(  select p.product, d.thedate  from (select distinct product from PRICES_TEST) p, dates d),ranges as(  select    pd.product,    pd.thedate,    (select max(PRICE_DATE) from PRICES_TEST p2     where p2.product = pd.product and p2.PRICE_DATE <= pd.thedate) as mindate    from productdates pd)select     r.thedate,    r.product,    p.pricefrom ranges rinner join PRICES_TEST p on r.mindate = p.price_date and r.product = p.productorder by r.product, r.thedate

• mindate retrieves the earliest possible date in the data set
• dates generates a calendar of dates from earliest possible date to today.
• productdates cross joins all possible products with all possible dates
• ranges determines which price date applied at each date
• the final query links which price date applied to the actual price and filters out dates for which there are no relevant price dates via the inner join condition

Demo: http://www.sqlfiddle.com/#!4/e528f/126

I made a few changes to Wolf's excellent answer.

I replaced the subquery factoring (WITH) with a regular subquery in the connect by. This makes the code a little simpler. (Although this type of code looks weird at first either way, so there may not be a huge gain here.)

Most significantly, I used a partition outer join instead of a cross join and outer join. Partition outer joins are also kind of strange, but they are meant for exactly this type of situation. This makes the code simpler, and should improve performance.

select    price_dates.price_date    ,product    ,last_value(price ignore nulls) over (order by product, price_dates.price_date) pricefrom(    select trunc(sysdate) - level + 1 price_date    from dual    connect by level <= trunc(sysdate) -        (select min(trunc(price_date)) from prices_test) + 1) price_datesleft outer join prices_test    partition by (prices_test.product)    on price_dates.price_date = prices_test.price_date;