SUM() with self-join in materialized view

I guess solution is in your query itself specially as you are using 9i.  All you need to do is use Case.
Try this:

select
      sum(case
            when t5.BUSINESS_DAY_DATE   between t1.WkStartDay and t5.BUSINESS_DAY_DATE then
              t5.AMOUNT
            else
              null
            end)       WTD_Amount,
      sum(case
            when t5.BUSINESS_DAY_DATE   between t1.PrdStartDay and t5.BUSINESS_DAY_DATE then
              t5.AMOUNT
            else
              null
            end)       PTD_Amount,
from    Transaction_Master      t5,
        Date_Master             t1      
where   t5.BUSINESS_DAY_DATE    = t1.DAY
order by
        t5.STORE_NUMBER,
        t5.ACCOUNTTYPE_CODE,
        t5.ACCOUNTTYPE,
        t5.LINE_NUMBER,
        t5.ACCOUNT_CODE,
        t5.BUSINESS_DAY_DATE;


Let me know if this works !
-Ritesh

Thank you Ritesh for another good idea.
I am not at work and cannot check this solution but from what I see in a query it seems that the result will be a general aggregation (sum()) of week to date data, month-to-date and year-to-date. But what I need is a gradual aggregation from beginning of the week, month or a year till the end.

Like in YTD (year-to-date) sample:

Date                  Amount$           Year-to-date
01/01/2007       $100                  $100
01/02/2007       $50                    $150
01/03/2007       $200                  $350
...

To do this kind of aggregation normally we have to do a self-join and use sum() implemented in correlated query in select clause (what is unfortunately is not allowed in materialized views in Oracle) so a correlated query can retrieve and accumulate Amount$ value from each transaction from the main query. I have showed it in my query.

If to use the solution you provided above we should get something like this:

Date                  Amount$           Year-to-date
01/01/2007       $100                  $350
01/02/2007       $50                    $350
01/03/2007       $200                  $350
...

Sometimes that is what we need (in data warehousing for example - OLAP) but in OLTP I have to do a gradual accumulation of $ values against each transaction which falls in a specified time frame (a week, month, period or a year).

Perhaps if there is no other alternative solution to implement this logic in materialized query I have to do it in PL/SQL procedure. But a materialized view is one of my Client's requirements. That is why I would be grateful for any suggestions regarding this issue.

Thanks in advance.

Thank you very much! These analytical function I have overlooked. It seems an excellent solution.

I will need just to do some extra work because my Client uses own fiscal year calendar (even with month split). But I believe if to add an additional mapping table to this query and use converted a week, month and year-to-date attributes it will work fine.

Thanks again and regards.

Hi Ritesh,

I need a help again with my task.

I used over (partition...)) clause in my query and got a correct results.
But when I tried to create a materialized view I got the following error:

ORA-32036: unsupported case for inlining of query name in WITH clause

It has something to do with "WITH" clause. When I used only one cte table in with clause I was able to create another materialized view without issues. But in this case with multiple references something did not work out.

Here is my final query:

create materialized view thcuser.mv_CashRec
build immediate
refresh complete
as

with  
    tDM as
    (
        select  dm.day,
                dm.FISCAL_WEEK,
                min(dm.day) over (partition by trunc(dm.FISCAL_WEEK)  order by dm.day) as Week_Start_Day,
                dm.FISCAL_MONTH,
                min(dm.day) over (partition by trunc(dm.FISCAL_MONTH) order by dm.day) as Period_Start_Day,
                dm.FISCAL_YEAR,
                min(dm.day) over (partition by trunc(dm.FISCAL_YEAR)  order by dm.day) as Year_Start_Day
        from    DATE_MASTER dm
        where   fiscal_year = extract(year from sysdate) -- 2007
    ),
    tYTD AS
    (    
        select min(day)     as Year_Start_Date
        from   tDM
    ),
    tDetail as
    (
        select  S.STORE_NUMBER,  
                (
                    CASE
                    WHEN TA.LINE_NUMBER IN ('1050','1051','9080','3080','3085','3090','3120','3125')
                    THEN '6'
                    WHEN TA.LINE_NUMBER IN ('9000','9004','9008','9010','9030','9040')
                    THEN '8'
                    ELSE
                        CASE
                        WHEN SUBSTR(TA.LINE_NUMBER,1,1) = '3'
                        THEN '3'
                        WHEN (SUBSTR(TA.LINE_NUMBER,1,1) = '4')
                        THEN '4'
                        ELSE
                        '5'
                        END
                    END
                ) AS ACCOUNTTYPE_CODE,
                       
                (
                    CASE
                    WHEN TA.LINE_NUMBER IN ('1050','1051','9080','3080','3085','3090','3120','3125')
                    THEN 'Memos'
                    WHEN TA.LINE_NUMBER IN ('9000','9004','9008','9010','9030','9040')
                    THEN 'Reconciliation'
                    ELSE
                        CASE
                        WHEN SUBSTR(TA.LINE_NUMBER,1,1) = '3'
                        THEN 'Receipts'
                        WHEN (SUBSTR(TA.LINE_NUMBER,1,1) = '4' )
                        THEN 'Disbursements'
                        ELSE
                        'Other'
                        END
                    END
                ) AS ACCOUNTTYPE,
                       
                TA.LINE_NUMBER,
                TA.ACCOUNT_CODE,
                TRUNC(TM.BUSINESS_DAY_DATE) as BUSINESS_DAY_DATE,
                SUM(TA.AMOUNT) AS AMOUNT
                       
        FROM    TRANSACTION_ACCOUNT        TA,
                TRANSACTION_MASTER         TM,
                STORE                      S,
                tYTD                       Y
                       
        WHERE   TA.TRANSACTION_MASTER_ID      = TM.TRANSACTION_MASTER_ID
          and   TM.STORE_ID                   = S.STORE_ID
          and   TM.TRANSACTION_STATUS         = 'C'
          and   TRUNC(TM.BUSINESS_DAY_DATE)  >= y.Year_Start_Date
         
        GROUP BY S.STORE_NUMBER,
                 (CASE
                    WHEN TA.LINE_NUMBER IN ('1050','1051','9080','3080','3085','3090','3120','3125')
                    THEN '6'
                    WHEN TA.LINE_NUMBER IN ('9000','9004','9008','9010','9030','9040')
                    THEN '8'
                    ELSE
                        CASE
                        WHEN SUBSTR(TA.LINE_NUMBER,1,1) = '3'
                        THEN '3'
                        WHEN (SUBSTR(TA.LINE_NUMBER,1,1) = '4')
                        THEN '4'
                        ELSE
                        '5'
                        END
                    END
                  ),
                  ( CASE
                    WHEN TA.LINE_NUMBER IN ('1050','1051','9080','3080','3085','3090','3120','3125')
                    THEN 'Memos'
                    WHEN TA.LINE_NUMBER IN ('9000','9004','9008','9010','9030','9040')
                    THEN 'Reconciliation'
                    ELSE
                        CASE
                        WHEN SUBSTR(TA.LINE_NUMBER,1,1) = '3'
                        THEN 'Receipts'
                        WHEN (SUBSTR(TA.LINE_NUMBER,1,1) = '4' )
                        THEN 'Disbursements'
                        ELSE
                        'Other'
                        END
                    END ),
                    TA.LINE_NUMBER,
                    TA.ACCOUNT_CODE,
                    TRUNC(TM.BUSINESS_DAY_DATE)
                 
        UNION ALL              

        SELECT      S.STORE_NUMBER,
                    '9'                             as ACCOUNTTYPE_CODE,
                    'Balancing Summary'             as ACCOUNTTYPE,
                    TA.LINE_NUMBER,
                    TA.ACCOUNT_CODE,
                    TRUNC(TM.BUSINESS_DAY_DATE)     as BUSINESSDATE,
                    SUM(TA.AMOUNT)                  as AMOUNT
               
        FROM        TRANSACTION_ACCOUNT             TA,
                    TRANSACTION_MASTER              TM,
                    STORE                            S,
                    tYTD                             Y
                   
        WHERE       TA.TRANSACTION_MASTER_ID         = TM.TRANSACTION_MASTER_ID
          AND       TM.STORE_ID                      = S.STORE_ID
          AND       TA.LINE_NUMBER                  in ('1940','1941','3080','3085','3120','3125','9040')
          AND       TM.TRANSACTION_STATUS            = 'C'    
          AND       TRUNC(TM.BUSINESS_DAY_DATE)     >= y.Year_Start_Date
                 
        GROUP BY    S.STORE_NUMBER,
                    TA.LINE_NUMBER,
                    TA.ACCOUNT_CODE,
                    TRUNC(TM.BUSINESS_DAY_DATE)
                           
        UNION ALL

        SELECT      S.STORE_NUMBER,
                    '9'                              AS ACCOUNTTYPE_CODE,
                    'Balancing Summary'              AS ACCOUNTTYPE,
                    '4222'                           AS LINE_NUMBER,
                    '185'                            AS ACCOUNT_CODE,
                    TRANSACTION_DATE                 AS BUSINESSDATE,
                    SUM(TRANSACTION_AMOUNT)          AS AMOUNT
        FROM        FINANCIAL_POST_MASTER    F,
                    STORE                    S,
                    tYTD                     y
        WHERE       F.STORE_ID                        = S.STORE_ID
          AND       TRUNC(F.TRANSACTION_DATE)        >= y.Year_Start_Date
         
        GROUP BY    S.STORE_NUMBER,
                    F.TRANSACTION_DATE                    
                   
    )
select  d.STORE_NUMBER,
        d.ACCOUNTTYPE_CODE,
        d.ACCOUNTTYPE,
        d.LINE_NUMBER,
        d.ACCOUNT_CODE,
        d.BUSINESS_DAY_DATE,
        d.AMOUNT,
       
        dm.Week_Start_Day,
        sum(d.Amount) over (partition by trunc(dm.Week_Start_Day) order by
            d.STORE_NUMBER,
            d.ACCOUNTTYPE_CODE,
            d.ACCOUNTTYPE,
            d.LINE_NUMBER,
            d.ACCOUNT_CODE,
            d.BUSINESS_DAY_DATE)               as WTD_Amount,
       
        dm.Period_Start_Day,
        sum(d.Amount) over (partition by trunc(dm.Period_Start_Day) order by
            d.STORE_NUMBER,
            d.ACCOUNTTYPE_CODE,
            d.ACCOUNTTYPE,
            d.LINE_NUMBER,
            d.ACCOUNT_CODE,
            d.BUSINESS_DAY_DATE)              as PTD_Amount,
       
        y.Year_Start_Date,
        sum(d.Amount) over (partition by trunc(d.BUSINESS_DAY_DATE,'year') order by  
            d.STORE_NUMBER,
            d.ACCOUNTTYPE_CODE,
            d.ACCOUNTTYPE,
            d.LINE_NUMBER,
            d.ACCOUNT_CODE,
            d.BUSINESS_DAY_DATE) as YTD_Amount
               
from    tDetail             d,
        tDM                 dm,
        tYTD                y
       
where   d.BUSINESS_DAY_DATE    = dm.DAY

order by
        d.STORE_NUMBER,
        d.ACCOUNTTYPE_CODE,
        d.ACCOUNTTYPE,
        d.LINE_NUMBER,
        d.ACCOUNT_CODE,
        d.BUSINESS_DAY_DATE;

     

There is surely some syntax errors in creating this Materialized view.  Some of the stuff I tried:

create materialized view mv_CashRec
build immediate
refresh complete
as
with
  tdm as    (select sysdate from dual)
from dual
ERROR at line 7:
ORA-00928: missing SELECT keyword

create materialized view mv_CashRec
build immediate
refresh complete
as
select   (select sysdate from dual) tdm
from dual
ERROR at line 5:
ORA-22818: subquery expressions not allowed here

create materialized view mv_CashRec
build immediate
refresh complete
as
select   sysdate tdm
from dual
Snapshot created.

I guess you have to remove WITH command and avoid writing inline subqueries.
-Ritesh

Thanks Retesh for an advice.

Actually I found that the problem was not with WITH clause as is bat with one of cte (WITH) tables:

From Oracle documentation:

ORA-32036 unsupported case for inlining of query name in WITH clause

    Cause: There is at least one query name which is inlined more than once because it's definition query is too simple and references another query name. This is not currently supported.

    Action: remove such a query name and retry.

When I replaced only one out of three cte tables

tYTD AS
    (    
        select min(day)     as Year_Start_Date
        from   tDM
    )

on correlated subquery everything started to work fine.