Oracle: Get data on all months, 0 if no data

Question

I have an Mview that brings data group by idNumber and Month. So I want to display 0 if there is no data for an specific month. This is my query:

select MonthName, myCost, myNumber 
  from
     (
       select MONTH mm, myCost, myNumber
         from myOracle_mv
     ) myTotals,
     (
       select to_char(date '2012-12-1' + numtoyminterval(level,'month'), 'mm') MonthName
        from dual
      connect by level <= 12
     ) ALLMONTHS
where mm = MonthName

So I was expecting:

Month Number Data
-----------------------
1     abc123 4444
2     0
3     abc123 4444
4     abc123 4444
5     0
6     abc123 4444
7     abc123 4444
8     0
9     abc123 4444
10    abc123 4444
11    0
12    abc123 4444

Instead I'm still getting:

1    abc123  4444
3    abc123  4444
4    abc123  4444
6    abc123  4444
7    abc123  4444
9    abc123  4444
10   abc123  4444
12   abc123  4444

Any ideas?

Thanks!

EDIT: Thanks for the answers. I did have the outer join in my Query but forgot to type in because was concentrating in changes the names of the table/columns.

So yes, I have tried with the OUTER JOIN and I still not getting the expected results. Any feedback is greatly appreciated.

EDIT: This is the data on myOracle_MV:

3777.24     AAA 1   2012
49973.12    AAA 2   2012
4049.91     AAA 3   2012
469.485     AAA 4   2012
5872.22     AAA 5   2012
65837.71    AAA 6   2012
566.23      AAA 7   2012
18432.95    AAA 8   2012
4337.75     AAA 12  2011
18811       BBB 1   2012
29872.67    BBB 2   2012
29068.55    BBB 3   2012
264957.8    BBB 4   2012
67673       BBB 5   2012
855.02      BBB 6   2012
5226.1      BBB 7   2012
2663.24     BBB 8   2012
5490.58     BBB 12  2011
3845.47     CCC 1   2012
3050.54     CCC 2   2012
3784.44     CCC 3   2012
799.73      CCC 4   2012
124884.2    CCC 5   2012
5157.24     CCC 6   2012
19184.78    CCC 7   2012
2280.05     CCC 8   2012
107.07      DDD 3   2012
181.78      DDD 4   2012
110.09      DDD 5   2012
18016.19    DDD 6   2012
1772.95     DDD 7   2012
63.32       DDD 8   2012

Answer 1

Very similar to exsiting answers, but this:

select months.month, mv.mycost, coalesce(mv.mynumber, 0) as mynumber
from (
  select to_char(date '1970-01-01'
    + numtoyminterval(level - 1, 'month'), 'mm') as month
  from dual
  connect by level <= 12) months
left join myoracle_mv mv
on mv.month = months.month
order by months.month, mv.mycost, mv.mynumber;

gives this with the data you posted:

MONTH MYCOST   MYNUMBER
----- ------ ----------
01    AAA       3777.24 
01    BBB         18811 
01    CCC       3845.47 
02    AAA      49973.12 
02    BBB      29872.67 
02    CCC       3050.54 
03    AAA       4049.91 
03    BBB      29068.55 
03    CCC       3784.44 
03    DDD        107.07 
04    AAA       469.485 
04    BBB      264957.8 
04    CCC        799.73 
04    DDD        181.78 
05    AAA       5872.22 
05    BBB         67673 
05    CCC      124884.2 
05    DDD        110.09 
06    AAA      65837.71 
06    BBB        855.02 
06    CCC       5157.24 
06    DDD      18016.19 
07    AAA        566.23 
07    BBB        5226.1 
07    CCC      19184.78 
07    DDD       1772.95 
08    AAA      18432.95 
08    BBB       2663.24 
08    CCC       2280.05 
08    DDD         63.32 
09                    0 
10                    0 
11                    0 
12    AAA       4337.75 
12    BBB       5490.58 

 35 rows selected

If you want a zero to appear in the mynumber column then you can make that:

select months.month, mv.mycost, coalesce(mv.mynumber, 0) as mynumber

which gives:

...
08    DDD         63.32 
09                    0 
10                    0 
11                    0 
12    AAA       4337.75 
...

From the comments on Jafar's answer it sounds like maybe you'd got that far on your own but you want zero values for all mycost values for all months. If that is the case then you need to get the list of possible values for mycost and outer join to that as well. This is taking all values that are in the MV already:

select months.month, costs.mycost, coalesce(mv.mynumber, 0) as mynumber
from (
  select to_char(date '1970-01-01'
    + numtoyminterval(level - 1, 'month'), 'mm') as month
  from dual
  connect by level <= 12) months
cross join (
  select distinct mycost
  from myoracle_mv) costs
left join myoracle_mv mv
on mv.month = months.month
and mv.mycost = costs.mycost
order by months.month, costs.mycost, mv.mynumber;

and gives:

MONTH MYCOST   MYNUMBER
----- ------ ----------
01    AAA       3777.24 
01    BBB         18811 
01    CCC       3845.47 
01    DDD             0 
02    AAA      49973.12 
02    BBB      29872.67 
02    CCC       3050.54 
02    DDD             0 
03    AAA       4049.91 
03    BBB      29068.55 
03    CCC       3784.44 
03    DDD        107.07 
04    AAA       469.485 
04    BBB      264957.8 
04    CCC        799.73 
04    DDD        181.78 
05    AAA       5872.22 
05    BBB         67673 
05    CCC      124884.2 
05    DDD        110.09 
06    AAA      65837.71 
06    BBB        855.02 
06    CCC       5157.24 
06    DDD      18016.19 
07    AAA        566.23 
07    BBB        5226.1 
07    CCC      19184.78 
07    DDD       1772.95 
08    AAA      18432.95 
08    BBB       2663.24 
08    CCC       2280.05 
08    DDD         63.32 
09    AAA             0 
09    BBB             0 
09    CCC             0 
09    DDD             0 
10    AAA             0 
10    BBB             0 
10    CCC             0 
10    DDD             0 
11    AAA             0 
11    BBB             0 
11    CCC             0 
11    DDD             0 
12    AAA       4337.75 
12    BBB       5490.58 
12    CCC             0 
12    DDD             0 

 48 rows selected 

But hopefully you have another table that holds the possble mycost values (assuming that's representing something like a cost center, rather than a price; slightly hard to tell what's what) and you can use that instead of the subquery.

SQL Fiddle.

Also note that if you wanted to add a filter, e.g. to restrict data to a particular year, you'd need to do that in the in the left join clause, not as a where clause, or you'd revert the outer join to an inner one. For example, adding this:

where mv.year = 2011

would mean you only got back two rows:

MONTH MYCOST   MYNUMBER
----- ------ ----------
12    AAA       4337.75 
12    BBB       5490.58 

But if you made than another condition on the outer join you'd still get 48 rows back, with 46 of them having zeros and two having the values above:

...
left join myoracle_mv mv
on mv.month = months.month
and mv.mycost = costs.mycost
and mv.year = 2011
order by months.month, costs.mycost, mv.mynumber;

...
11    CCC             0 
11    DDD             0 
12    AAA       4337.75 
12    BBB       5490.58 
12    CCC             0 
12    DDD             0 

 48 rows selected 

Answer 2

Maybe something like this

select MonthName, COALESCE(myCost,0), myNumber 
  from
     (
       select to_char(date '2012-12-1' + numtoyminterval(level,'month'), 'mm') MonthName
        from dual
      connect by level <= 12
     ) ALLMONTHS LEFT OUTER JOIN
     (
       select MONTH mm, myCost, myNumber
         from myOracle_mv
     ) myTotals ON 
    mm = MonthName

Answer 3

you need an outer join ( (+) at the end of your query ):

select MonthName, myCost, myNumber from
(
select
MONTH mm, myCost, myNumber
from
myOracle_mv
) myTotals,
(
select
to_char(date '2012-12-1' + numtoyminterval(level,'month'), 'mm') MonthName
from
dual
connect by level <= 12
)ALLMONTHS
where mm = MonthName(+)

---

for your example, you don't need to calculate dates:

 select MONTH mm, NVL(myCost, 0), myNumber
 from
    (select level from dual connect by level <= 12) NUM
    left outer join  myOracle_mv MV  ON ( MV.MONTH  = NUM.level )
;

Answer 4

You'd need to do an outer join between the two inline views

select MonthName, myCost, myNumber 
  from (select MONTH mm, myCost, myNumber
          from myOracle_mv
       ) myTotals
       right outer join
       (select to_char(date '2012-12-1' + numtoyminterval(level,'month'), 'mm') MonthName
          from dual
       connect by level <= 12) ALLMONTHS
       on( myTotals.mm = allmonths.MonthName )

You can also use the old Oracle-specific (+) syntax for outer joins but I would generally suggest using the SQL standard syntax.