Materialized View With Monthly Substitute Variables

Question

I am trying to reconfigure a View into a materialized view for summary roll-ups. IS there a way to implement the process so that I can gather each months parameters from the materialized view easily without having to run Substitute variables each time:

def RECORDMONTH = 201402
def BeginDate   = 2/01/2014
def EndDate     = 2/28/2014
def YEAR_MONTH =  2014-02

Would it be able to be designed in a way that can pull all twqeleve months even if that month has happened yet.

Really appreciate it.

Answer 1

I'm guessing that this is a continuation of a number of other questions that you've asked. My guess is that you just want something like this where you generate the months you want (in this case the 12 months of 2015) and use that in an outer join in your query.

WITH all_months AS
  SELECT add_months( date '2015-01-01', level-1 ) start_of_month,
         last_day( add_months( date '2015-01-01', level-1 )) end_of_month
    FROM dual
 CONNECT BY level <= 12
)
SELECT am.start_of_month,
       count(*) num_rows,
       <<other aggregates>>
  FROM your_table yt
       RIGHT OUTER JOIN all_months am
          ON( yt.some_date BETWEEN am.start_of_month AND am.end_of_month )
 GROUP BY am.start_of_month