I have an Oracle 18c table that has rows for certain years:

with data (year_, amount) as (
select 2024, 100 from dual union all
select 2025, 200 from dual union all
select 2025, 300 from dual union all
select 2026, 400 from dual union all
select 2027, 500 from dual union all
select 2028, 600 from dual union all
select 2028, 700 from dual union all
select 2028, 800 from dual union all
select 2029, 900 from dual union all
select 2031, 100 from dual
)
select
    *
from
    data

     YEAR_     AMOUNT
---------- ----------
      2024        100
      2025        200
      2025        300
      2026        400
      2027        500
      2028        600
      2028        700
      2028        800
      2029        900
      2031        100

I want at least one row for each year within this range: sysyear 9. In other words, I want rows for 10 years, starting with the current year (currently 2023).

I'm missing rows for certain years: 2023, 2030, and 2032. So I want to generate filler rows for those missing years. The amount for the filler rows would be null.

It would look like this:

     YEAR_     AMOUNT
---------- ----------
      2023             --filler
      2024        100
      2025        200
      2025        300
      2026        400
      2027        500
      2028        600
      2028        700
      2028        800
      2029        900
      2030             --filler
      2031        100
      2032             --filler

In an Oracle SQL query, how can I select the rows and generate filler rows within the 10 year range?

Edit: I would prefer not to manually create a list of years in the query or in a table. I would rather create a dynamic range within the query.

CodePudding user response：

I broke out the CTE into multiple bits to help explain, but this should do the trick

SQL> with data (year_, amount) as (
  2  select 2024, 100 from dual union all
  3  select 2025, 200 from dual union all
  4  select 2025, 300 from dual union all
  5  select 2026, 400 from dual union all
  6  select 2027, 500 from dual union all
  7  select 2028, 600 from dual union all
  8  select 2028, 700 from dual union all
  9  select 2028, 800 from dual union all
 10  select 2029, 900 from dual union all
 11  select 2031, 100 from dual
 12  ),
 13  boundaries as (
 14  select max(year_) maxy, min(year_) miny
 15  from data
 16  ),
 17  all_the_years as
 18  ( select miny rownum-1 yr
 19    from boundaries
 20    connect by level <= maxy-miny 1
 21  )
 22  select *
 23  from  all_the_years a
 24   left outer join data d
 25  on ( a.yr = d.year_ )
 26  order by 1;

        YR      YEAR_     AMOUNT
---------- ---------- ----------
      2024       2024        100
      2025       2025        200
      2025       2025        300
      2026       2026        400
      2027       2027        500
      2028       2028        700
      2028       2028        800
      2028       2028        600
      2029       2029        900
      2030
      2031       2031        100

11 rows selected.

If its fixed at 10 years, then you don't need the MAX - just connect level <= 10

CodePudding user response：

Try it like here:

Select    y.YEAR_, t.AMOUNT
From      (Select EXTRACT(YEAR From SYSDATE)   LEVEL - 1 "YEAR_" From Dual Connect By LEVEL <= 10) y
Left Join tbl t ON(t.YEAR_ = y.YEAR_)
Order By  y.YEAR_, t.AMOUNT

With your sample data:

WITH 
    tbl (YEAR_, AMOUNT) AS 
        (
            Select 2024, 100 From Dual Union All
            Select 2025, 200 From Dual Union All
            Select 2025, 300 From Dual Union All
            Select 2026, 400 From Dual Union All
            Select 2027, 500 From Dual Union All
            Select 2028, 600 From Dual Union All
            Select 2028, 700 From Dual Union All
            Select 2028, 800 From Dual Union All
            Select 2029, 900 From Dual Union All
            Select 2031, 100 From Dual
        )

... the result is: