title: Duplicating records to fill gap between dates
body: <p>I need to do something really weird, which is to create fake records <strong>in a view</strong> to fill the gap between posted dates of product prices.</p>
  
  <p>Actually, my scenario is a little bit more complicated than that, but I've simplified to products/dates/prices.</p>
  
  <p>Let's say we have this table:</p>
  
  <pre><code>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);
  </code></pre>
  
  <p>With these records:</p>
  
  <pre><code>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);
  </code></pre>
  
  <p>selecting records will return me this:</p>
  
  <pre><code>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                     
  </code></pre>
  
  <p>Assuming today is Apr 21 2012, I need <strong>a view</strong> that shall repeat each price every day until a new price is posted. Like this:</p>
  
  <pre><code>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                     
  </code></pre>
  
  <p>Any ideas how to do that? I <strong>cannot</strong> really use other auxiliary tables, triggers nor PL/SQL programming, I really need to do this using <strong>a view</strong>.</p>
  
  <p>I think this can be done using oracle analytics, but I'm not familiar with that. I tried to read this <a href="http://www.club-oracle.com/articles/analytic-functions-i-introduction-164/" rel="nofollow">http://www.club-oracle.com/articles/analytic-functions-i-introduction-164/</a> but I didn't get it at all.</p>