generate duplicate rows in Select statement in Oracle

Question

I have a select statement with more than 10 columns.I have to repeat the rows wherever the data is missing based on the date. The rows which are to be generated should have data from the preceding rows sorted by date ascending. The date range to be considered is based on grouping of id.

The date is actually a range from 15th March to 16th April,but for sample I have taken only limited rows.

For example the data is as shown below.

    ID  Date    Type    Code  Location
   ==== ======  ===     ====   ====
    1   15-Mar  TG       RET    X1
    1   17-Mar  GG       CAN    S2
    1   20-Mar  DTR      ISS    D2
    2   14-Apr  YT       RR     F2
    2   16-Apr  F        FC     F1

Excepted output:

    ID  Date    Type    Code  Location
    === ====    ====   ====  ======  
    1   15-Mar  TG      RET    X1
    *1  16-Mar  TG      RET    X1*
    1   17-Mar  GG      CAN    S2
    *1  18-Mar  GG      CAN    S2*
    *1  19-Mar  GG      CAN    S2*
    1   20-Mar  DTR     ISS    D2
    2   14-Apr  YT      RR     F2
    *2  15-Apr  YT      RR     F2*
    2   16-Apr  F       FC     F1

Answer 1

Here's a working example of a possible way to achieve your desired output. I'm utilizing Oracle's LAST_VALUE analytic function with the IGNORE NULLS option and ORDER BY clause.

Test data:

CREATE TABLE so123 (
  id NUMBER,
  d DATE,
  type VARCHAR2(10),
  code VARCHAR2(10),
  location VARCHAR2(10)
);

INSERT INTO so123 VALUES (1, DATE '2015-05-15', 'TG', 'RET', 'X1');
INSERT INTO so123 VALUES (1, DATE '2015-05-17', 'GG', 'CAN', 'S2');
INSERT INTO so123 VALUES (1, DATE '2015-05-20', 'DTR', 'ISS', 'D2');
INSERT INTO so123 VALUES (2, DATE '2015-04-14', 'YT', 'RR', 'F2');
INSERT INTO so123 VALUES (2, DATE '2015-04-16', 'F', 'FC', 'F1');

COMMIT;

The select itself:

WITH
  dmm AS (
    SELECT MIN(d) min_d, MAX(d) max_d FROM so123
  )
SELECT
    NVL(s.id, LAST_VALUE(s.id) IGNORE NULLS OVER (ORDER BY dt.d)) AS id,
    dt.d,
    NVL(s.type, LAST_VALUE(s.type) IGNORE NULLS OVER (ORDER BY dt.d)) AS type,
    NVL(s.code, LAST_VALUE(s.code) IGNORE NULLS OVER (ORDER BY dt.d)) AS code,
    NVL(s.location, LAST_VALUE(s.location) IGNORE NULLS OVER (ORDER BY dt.d)) AS location
  FROM (
    SELECT min_d + level - 1 as d
      FROM dmm
    CONNECT BY min_d + level - 1 <= max_d
  ) dt LEFT JOIN so123 s ON (dt.d = s.d)
ORDER BY dt.d
;

Output:

        ID D                TYPE       CODE       LOCATION 
---------- ---------------- ---------- ---------- ----------
         2 14-04-2015 00:00 YT         RR         F2         
         2 15-04-2015 00:00 YT         RR         F2         
         2 16-04-2015 00:00 F          FC         F1         
         2 17-04-2015 00:00 F          FC         F1         
         2 18-04-2015 00:00 F          FC         F1         
         2 19-04-2015 00:00 F          FC         F1         
         2 20-04-2015 00:00 F          FC         F1         
         2 21-04-2015 00:00 F          FC         F1         
         2 22-04-2015 00:00 F          FC         F1         
         2 23-04-2015 00:00 F          FC         F1         
         2 24-04-2015 00:00 F          FC         F1         
         2 25-04-2015 00:00 F          FC         F1         
         2 26-04-2015 00:00 F          FC         F1         
         2 27-04-2015 00:00 F          FC         F1         
         2 28-04-2015 00:00 F          FC         F1         
         2 29-04-2015 00:00 F          FC         F1         
         2 30-04-2015 00:00 F          FC         F1         
         2 01-05-2015 00:00 F          FC         F1         
         2 02-05-2015 00:00 F          FC         F1         
         2 03-05-2015 00:00 F          FC         F1         
         2 04-05-2015 00:00 F          FC         F1         
         2 05-05-2015 00:00 F          FC         F1         
         2 06-05-2015 00:00 F          FC         F1         
         2 07-05-2015 00:00 F          FC         F1         
         2 08-05-2015 00:00 F          FC         F1         
         2 09-05-2015 00:00 F          FC         F1         
         2 10-05-2015 00:00 F          FC         F1         
         2 11-05-2015 00:00 F          FC         F1         
         2 12-05-2015 00:00 F          FC         F1         
         2 13-05-2015 00:00 F          FC         F1         
         2 14-05-2015 00:00 F          FC         F1         
         1 15-05-2015 00:00 TG         RET        X1         
         1 16-05-2015 00:00 TG         RET        X1         
         1 17-05-2015 00:00 GG         CAN        S2         
         1 18-05-2015 00:00 GG         CAN        S2         
         1 19-05-2015 00:00 GG         CAN        S2         
         1 20-05-2015 00:00 DTR        ISS        D2         

 37 rows selected 

How does that work? We generate all the dates between the MIN and MAX dates from the source table. To do that, we use the CONNECT BY clause to make Oracle generate records until the condition min_d + level - 1 <= max_d doesn't hold any longer.

Then, we take the generated records and LEFT JOIN the source table to them. Here comes the LAST_VALUE analytic function's magic into play. This function searches for the last non-null (the IGNORE NULLS option) value in the table, using specified ordering and fills in the missing fields.

You can read more about that function here:

http://oracle-base.com/articles/misc/first-value-and-last-value-analytic-functions.php

SQLFiddle Demo