Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1219
  • Last Modified:

SUM() with self-join in materialized view

Thank you again for helping me to find the problem. But If it is not possible to use sub-query in select statement in materialized view then can you suggest any other alternatives.

I have a requirement to calculate year-to-date, month-to-date and week-to-date values using materialized views (fast refresh method). Such calculations normally requires self-join with sum().

Here is my simplified sql sample again:

select  t5.STORE_NUMBER,
        t5.ACCOUNTTYPE_CODE,
        t5.ACCOUNTTYPE,
        t5.LINE_NUMBER,
        t5.ACCOUNT_CODE,
        t5.BUSINESS_DAY_DATE,
        t5.AMOUNT,
        t1.WkStartDay,
        (
            select  sum( it5.AMOUNT )       as WTD_Amount
            from    Transaction_Master   it5
            where   it5.STORE_NUMBER        = t5.STORE_NUMBER
              and   it5.ACCOUNTTYPE_CODE    = t5.ACCOUNTTYPE_CODE
              and   it5.ACCOUNTTYPE         = t5.ACCOUNTTYPE
              and   it5.LINE_NUMBER         = t5.LINE_NUMBER
              and   it5.ACCOUNT_CODE        = t5.ACCOUNT_CODE
           
              and   it5.BUSINESS_DAY_DATE   between t1.WkStartDay and t5.BUSINESS_DAY_DATE
            group by
                    it5.STORE_NUMBER,
                    it5.LINE_NUMBER  
        )           WTD_Amount,
        t1.PrdStartDay,
        (
            select  sum( it5.AMOUNT )       as PTD_Amount
            from    Transaction_Master   it5
            where   it5.STORE_NUMBER        = t5.STORE_NUMBER
              and   it5.ACCOUNTTYPE_CODE    = t5.ACCOUNTTYPE_CODE
              and   it5.ACCOUNTTYPE         = t5.ACCOUNTTYPE
              and   it5.LINE_NUMBER         = t5.LINE_NUMBER
              and   it5.ACCOUNT_CODE        = t5.ACCOUNT_CODE
             
              and   it5.BUSINESS_DAY_DATE   between t1.PrdStartDay and t5.BUSINESS_DAY_DATE
            group by
                    it5.STORE_NUMBER,
                    it5.LINE_NUMBER    
        )           PTD_Amount,
        (
            select min(day)     as YTD_DATE
            from   date_master
            where  fiscal_year  = 2007
        )   YTD_DATE,
        (
            select  sum( it5.AMOUNT )       as YTD_Amount
            from    Transaction_Master   it5
            where   it5.STORE_NUMBER        = t5.STORE_NUMBER
              and   it5.ACCOUNTTYPE_CODE    = t5.ACCOUNTTYPE_CODE
              and   it5.ACCOUNTTYPE         = t5.ACCOUNTTYPE
              and   it5.LINE_NUMBER         = t5.LINE_NUMBER
              and   it5.ACCOUNT_CODE        = t5.ACCOUNT_CODE
             
              and   it5.BUSINESS_DAY_DATE   between (
                                                            select min(day)     as YTD_DATE
                                                            from   date_master
                                                            where  fiscal_year  = 2007
                                                    )       and t5.BUSINESS_DAY_DATE
            group by
                    it5.STORE_NUMBER,
                    it5.LINE_NUMBER    
        )           YTD_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;




0
GoodName
Asked:
GoodName
  • 4
  • 3
1 Solution
 
Ritesh_GargCommented:
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
0
 
GoodNameAuthor Commented:
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.
0
 
Ritesh_GargCommented:
I guess I understand your requirement.
Here is what I found on a sample table I created:
D_DT                  AMT
--------------- ---------
01-JAN-07             100
15-JAN-07              20
01-FEB-07              50
01-MAR-07             200

Query:
select  t5.d_dt, t5.amt,
        sum(t5.amt) over (partition by trunc(t5.d_dt,'month') order by t5.d_dt) mtd,
        sum(t5.amt) over (partition by trunc(t5.d_dt,'year') order by t5.d_dt) ytd
from    t5

Output:
D_DT                  AMT       MTD       YTD
--------------- --------- --------- ---------
01-JAN-07             100       100       100
15-JAN-07              20       120       120
01-FEB-07              50        50       170
01-MAR-07             200       200       370

Does this help?
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
GoodNameAuthor Commented:
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.
0
 
GoodNameAuthor Commented:
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;

     
0
 
Ritesh_GargCommented:
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
0
 
GoodNameAuthor Commented:
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.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now