I need to do something really weird, which is to create fake records in a view to fill the gap between posted dates of product prices.
Actually, my scenario is a little bit more complicated than that, but I've simplified to products/dates/prices.
Let's say we have this table:
create table PRICES_TEST
(
PRICE_DATE date not null,
PRODUCT varchar2(13) not null,
PRICE number
);
alter table PRICES_TEST
add constraint PRICES_TEST_PK
primary key (PRICE_DATE, PRODUCT);
With these records:
insert into PRICES_TEST values (date'2012-04-15', 'Screw Driver', 13);
insert into PRICES_TEST values (date'2012-04-18', 'Screw Driver', 15);
insert into PRICES_TEST values (date'2012-04-13', 'Hammer', 10);
insert into PRICES_TEST values (date'2012-04-16', 'Hammer', 15);
insert into PRICES_TEST values (date'2012-04-19', 'Hammer', 17);
selecting records will return me this:
PRICE_DATE PRODUCT PRICE
------------------------- ------------- ----------------------
13-Apr-2012 00:00:00 Hammer 10
16-Apr-2012 00:00:00 Hammer 15
19-Apr-2012 00:00:00 Hammer 17
15-Apr-2012 00:00:00 Screw Driver 13
18-Apr-2012 00:00:00 Screw Driver 15
Assuming today is Apr 21 2012, I need a view that shall repeat each price every day until a new price is posted. Like this:
PRICE_DATE PRODUCT PRICE
------------------------- ------------- ----------------------
13-Apr-2012 00:00:00 Hammer 10
14-Apr-2012 00:00:00 Hammer 10
15-Apr-2012 00:00:00 Hammer 10
16-Apr-2012 00:00:00 Hammer 15
17-Apr-2012 00:00:00 Hammer 15
18-Apr-2012 00:00:00 Hammer 15
19-Apr-2012 00:00:00 Hammer 17
20-Apr-2012 00:00:00 Hammer 17
21-Apr-2012 00:00:00 Hammer 17
15-Apr-2012 00:00:00 Screw Driver 13
16-Apr-2012 00:00:00 Screw Driver 13
17-Apr-2012 00:00:00 Screw Driver 13
18-Apr-2012 00:00:00 Screw Driver 15
19-Apr-2012 00:00:00 Screw Driver 15
20-Apr-2012 00:00:00 Screw Driver 15
21-Apr-2012 00:00:00 Screw Driver 15
Any ideas how to do that? I cannot really use other auxiliary tables, triggers nor PL/SQL programming, I really need to do this using a view.
I think this can be done using oracle analytics, but I'm not familiar with that. I tried to read this http://www.club-oracle.com/articles/analytic-functions-i-introduction-164/ but I didn't get it at all.
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.price
from ranges r
inner join PRICES_TEST p on r.mindate = p.price_date and r.product = p.product
order by r.product, r.thedate
mindate retrieves the earliest possible date in the data setdates generates a calendar of dates from earliest possible date to today.productdates cross joins all possible products with all possible datesranges determines which price date applied at each dateinner join condition