Databases
StackOverflow2013
Postsdbo
PODuplicating records to fill gap between dates
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PODuplicating records to fill gap between dates
    primarykey
    data
    text
    <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>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USBonifacio2
    1. USL. Holanda
    plurals
    1. This table or related slice is empty.
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. PODuplicating records to fill gap between dates
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PODuplicating records to fill gap between dates
      1. USJon Heller
      1. VTFavorite
    3. VO
      singulars
      1. PODuplicating records to fill gap between dates
      1. This table or related slice is empty.
      1. VTUpMod
    1. CONVM, i understand it now :) It will be possible to generate data with creative use of the `Dual` table.
      singulars
      1. PODuplicating records to fill gap between dates
      1. USmellamokb
    2. COI could assume that oracle analytics has it's own date dimension table to perform such function. Could you just create your own date dimension table?
      singulars
      1. PODuplicating records to fill gap between dates
      1. USChris Hayes
    3. COhere is an interesting article on creating a dynamic calandar view in tsql (yes, you want oracle but maybe it can be altered): http://sqlserverpedia.com/blog/sql-server-bloggers/tsql-tuesday-18-using-a-recursive-cte-to-create-a-calendar-table/
      singulars
      1. PODuplicating records to fill gap between dates
      1. USChris Hayes
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload