Imitate rows that don't exist

Question

I need to substitute default data for one row that doesn't exist. Below is what I have for data followed by what I need to return for data. I'd like to do this in SQL as opposed to building something in PL/SQL. I'm using Oracle 8i.

What I have:

Item     Period_start_date     Qty_Used
1234     1-MAR-2015            10
1234     1-JUN-2015            32
1234     1-JUL-2015            14
1234     1-SEP-2015            11

What I need:

1234     1-MAR-2015            10
1234     1-APR-2015            0
1234     1-MAY-2015            0
1234     1-JUN-2015            32
1234     1-JUL-2015            14
1234     1-AUG-2015            0
1234     1-SEP-2015            11

Answer 1

Using 8i makes this a little more complicated than it might be in later release.

You can generate a list of all the months in the range covered by your existing data with a hierarchical query, starting from the earliest date and number of months:

select item, min(period_start_date) min_date,
  months_between(max(period_start_date), min(period_start_date)) as num_months
from your_table
group by item

... and using that as an inner query for a hierarchical query:

select item, add_months(min_date, level) as period_start_date
from (
  select item, min(period_start_date) min_date,
    months_between(max(period_start_date), min(period_start_date)) as num_months
  from your_table
  group by item
)
connect by level < num_months

That gives you, in this case, six dummy rows, for April to August. (We know we don't need dummy rows for March or September).

You can then exclude any that have real data for the same date with not exists; and union that with the data from the real table:

select item, period_start_date, qty_used
from your_table
union all
select item, period_start_date, 0
from (
  select item, add_months(min_date, level) as period_start_date
  from (
    select item, min(period_start_date) min_date,
      months_between(max(period_start_date), min(period_start_date)) as num_months
    from your_table
    group by item
  )
  connect by level < num_months
) t
where not exists (
  select null
  from your_table
  where item = t.item
  and period_start_date = t.period_start_date
)
order by item, period_start_date;

      ITEM PERIOD_STAR   QTY_USED
---------- ----------- ----------
      1234 01-MAR-2015         10
      1234 01-APR-2015          0
      1234 01-MAY-2015          0
      1234 01-JUN-2015         32
      1234 01-JUL-2015         14
      1234 01-AUG-2015          0
      1234 01-SEP-2015         11

With a fixed start date you can modify the generated table:

select item, period_start_date, qty_used
from your_table
union all
select item, period_start_date, 0
from (
  select item, add_months(date '2013-03-01', level - 1) as period_start_date
  from (select distinct item from your_table)
  connect by add_months(date '2013-03-01', level - 1) < sysdate
) t
where not exists (
  select null
  from your_table
  where item = t.item
  and period_start_date = t.period_start_date
)
order by item, period_start_date;

You can also left outer join from the generated table data, but of course have to use the old Oracle-specific syntax:

select t.item, t.period_start_date, nvl(yt.qty_used, 0) as qty
from (
  select item, add_months(date '2013-03-01', level - 1) as period_start_date
  from (select distinct item from your_table)
  connect by add_months(date '2013-03-01', level - 1) < sysdate
) t, your_table yt
where yt.item (+) = t.item
and yt.period_start_date (+) = t.period_start_date
order by t.item, t.period_start_date;