CODEWITHSUNDEEP
sqloracle-databaseplsql
may
may

Reputation: 33

How can I insert new rows into a query result using PL/SQL or SQL query in Oracle?

I am selecting data from multiple tables and want to insert data for missing years. Can I do this by using PLSQL or SQL ?

For example, below is output of my query:

YEAR  COUNT
----- -----
2012      5
2013      6
2015      2
2016      1
2019      0

I want to add the year and count=0 for missing years and finally I should have this -

YEAR  COUNT
----- -----
2012      5
2013      6
2014      0
2015      2
2016      1
2017      0
2018      0
2019      0

Upvotes: 1

Views: 536

Answers (2)

Barbaros Özhan
Barbaros Özhan

Reputation: 65288

One option would be using connect by level <= .. syntax containing a left join with min(..) over (partition by ..) and max(..) over (partition by ..) analytic functions as quxiliary :

with t as
(
 select t0.*, 
        min(year) over (partition by 1) as min_year,
        max(year) over (partition by 1) as max_year
   from t0
)
select t1.year as "year", nvl( t."count" , 0) as "count"
  from
  (
   select distinct level + t.min_year - 1 as year
     from t
   connect by level <= t.max_year - t.min_year + 1 ) t1
  left join t
    on t.year = t1.year
 order by "year"

Demo

Upvotes: 1

Eray Balkanli
Eray Balkanli

Reputation: 7960

Create a table years including the year values. 

Create table years ( year int)
insert into years values (2010),(2011),(2012), ... etc

Then all you need to use is left join like below:

SELECT y.year,ISNULL(t.val,0)
FROM years y
LEFT OUTER JOIN yourtable t ON t.year=y.year
ORDER BY y.year

Upvotes: 1

Related Questions