sql db2 - how to add/create rows with a 'select'

Question

my query pulls data like this:

BSYY  CCF
2001  .5
2003  .8
2007  .9

I'd like to create values '0' for the years where CCF value doesn't exist so it would look like:

BSYY  CCF
2001  .5
2002  0
2003  .8
2004  0
2005  0
2006  0
2007  .9

The problem is, I am not given authority of 'create' or 'insert'... Is there a way to do it without creating a temp table?

Answer 1

You can certainly do this with SQL, especially if you have a table that can provide all years. Here is an example showing how to do it by generating a table:

with
   t (bsyy, ccf) as (values (2001, 0.5),(2003,0.8),(2007,0.9))
  ,y (year) as (values (2000),(2001),(2002),(2003),(2004),
                       (2005),(2006),(2007),(2008),(2009))
select
   y.year
  ,coalesce(t.ccf,0.0) as ccf
from
   y
   left outer join t on y.year = t.bsyy
order by
   y.year;

You would replace the t common table expression with your actual source table. For example, if you have a table called tab1 that has columns bsyy and ccf, the query would be:

with
   y (year) as (values (2000),(2001),(2002),(2003),(2004),
                       (2005),(2006),(2007),(2008),(2009))
select
   y.year
  ,coalesce(t.ccf,0.0) as ccf
from
   y
   left outer join tab1 t 
      on y.year = t.bsyy
order by
   y.year;

Instead of hard-coding the values for the y common table expression, you could use a recursive expression to generate those values for you.

However, why force the DBMS to do this? Why not just do it at the application level?