• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1023
  • Last Modified:

create materialized view issue

I cannot create a materialized view from a complex query containing WITH, UNION ALL and self-join.

The purpose of the query to calculate year-to-date, period-to-day and week-to-date values per transaction.

The query itself works but very slow due to multi million records transaction tables. I have tried to create a materialized view instead but got a following error:

                        "ORA-22818: subquery expressions not allowed here" (line ~39)

Here is a query:

create materialized view product_sales_mv
refresh complete on demand
as


    WITH      
    tYTD AS
            (      
                select min(day) AS YTD_DATE
                from   date_master
                where  fiscal_year = 2007
            ),
    tDM AS
    (
    select
          dm.day,
          (
                select  min(m1.day) as WkStartDay
                from
                        POSINT.DATE_MASTER m1,
                        POSINT.DATE_MASTER m2
                where   m1.FISCAL_YEAR   = m2.FISCAL_YEAR
                  and   m1.FISCAL_MONTH  = m2.FISCAL_MONTH
                  and   m1.FISCAL_WEEK   = m2.FISCAL_WEEK
                  and   m2.DAY                  = dm.day
                  and   m1.DAY                 <= dm.day
          ) as WkStartDay,

          (
                select
                    min(m1.day) as PrdStartDay
                from
                    POSINT.DATE_MASTER m1,
                    POSINT.DATE_MASTER m2
                where m1.FISCAL_YEAR   = m2.FISCAL_YEAR
                  and m1.FISCAL_MONTH  = m2.FISCAL_MONTH
                  and m2.DAY                = dm.day
                  and m1.DAY               <= dm.day
          ) as PrdStartDay
    from DATE_MASTER dm

    ),
            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.YTD_DATE
                 -- AND s.store_number = 4165
                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.YTD_DATE            
                    --  AND s.store_number = 4165
        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.YTD_DATE
        --AND s.store_number = 4165
        GROUP BY S.STORE_NUMBER, F.TRANSACTION_DATE                              
                          
            )
    SELECT d.*,
                 
              (
                       
                SELECT SUM(d3.AMOUNT)
                      
                FROM tDetail d3
                WHERE d3.BUSINESS_DAY_DATE BETWEEN p.WkStartDay and d.BUSINESS_DAY_DATE
                  AND d3.STORE_NUMBER                     = d.STORE_NUMBER
                  AND d3.LINE_NUMBER                     = d.LINE_NUMBER
                  AND d3.ACCOUNTTYPE_CODE              = d.ACCOUNTTYPE_CODE             
                GROUP BY d3.STORE_NUMBER,
                         d3.LINE_NUMBER
              ) AS WTD_AMOUNT,

              (
                       
                SELECT SUM(d2.AMOUNT)
                      
                FROM tDetail d2
                WHERE d2.BUSINESS_DAY_DATE BETWEEN p.PrdStartDay and d.BUSINESS_DAY_DATE
                  AND d2.STORE_NUMBER                     = d.STORE_NUMBER
                  AND d2.LINE_NUMBER                     = d.LINE_NUMBER
                  AND d2.ACCOUNTTYPE_CODE              = d.ACCOUNTTYPE_CODE
                      
                GROUP BY d2.STORE_NUMBER,
                         d2.LINE_NUMBER
              ) AS PTD_AMOUNT,

            (
                SELECT SUM(d1.AMOUNT)
                      
                FROM tDetail d1,
                     tYTD       y
                      
                WHERE d1.BUSINESS_DAY_DATE BETWEEN y.YTD_DATE and d.BUSINESS_DAY_DATE
                  AND d1.STORE_NUMBER                     = d.STORE_NUMBER
                  AND d1.LINE_NUMBER                     = d.LINE_NUMBER
                  AND d1.ACCOUNTTYPE_CODE              = d.ACCOUNTTYPE_CODE
                      
                GROUP BY d1.STORE_NUMBER,
                         d1.LINE_NUMBER
              ) AS YTD_AMOUNT
    FROM   tDetail d,
           tDM p
    WHERE p.day = d.BUSINESS_DAY_DATE



0
GoodName
Asked:
GoodName
1 Solution
 
rbrookerCommented:
it may not be ideal, but you can try rearranging the query to be :

select d.*,
       (select sum(d3.amount)
        from tdetail d3
        where d3.business_day_date between p.wkstartday and d.business_day_date
        and d3.store_number = d.store_number
        and d3.line_number = d.line_number
        and d3.accounttype_code = d.accounttype_code
        group by d3.store_number,
                 d3.line_number) as wtd_amount,
       (select sum(d2.amount)
        from tdetail d2
        where d2.business_day_date between p.prdstartday and d.business_day_date
        and d2.store_number = d.store_number
        and d2.line_number = d.line_number
        and d2.accounttype_code = d.accounttype_code
        group by d2.store_number,
                 d2.line_number) as ptd_amount,
       (select sum(d1.amount)
        from tdetail d1,
             (select min(day) as ytd_date
              from date_master
              where fiscal_year = 2007) y
        where d1.business_day_date between y.ytd_date and d.business_day_date
        and d1.store_number = d.store_number
        and d1.line_number = d.line_number
        and d1.accounttype_code = d.accounttype_code
        group by d1.store_number,
                 d1.line_number) as ytd_amount
from (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,
           (select min(day) as ytd_date
            from date_master
            where fiscal_year = 2007) 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.ytd_date
      -- AND s.store_number = 4165
      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,
           (select min(day) as ytd_date
            from date_master
            where fiscal_year = 2007) 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.ytd_date
      --  AND s.store_number = 4165
      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,
           (select min(day) as ytd_date
            from date_master
            where fiscal_year = 2007) y
      where f.store_id = s.store_id
      and trunc(f.transaction_date) >= y.ytd_date
      --AND s.store_number = 4165
      group by s.store_number,
               f.transaction_date) d,
     (select dm.day,
             (select min(m1.day) as wkstartday
              from posint.date_master m1,
                   posint.date_master m2
              where m1.fiscal_year = m2.fiscal_year
              and m1.fiscal_month = m2.fiscal_month
              and m1.fiscal_week = m2.fiscal_week
              and m2.day = dm.day
              and m1.day <= dm.day) as wkstartday,
             
             (select min(m1.day) as prdstartday
              from posint.date_master m1,
                   posint.date_master m2
              where m1.fiscal_year = m2.fiscal_year
              and m1.fiscal_month = m2.fiscal_month
              and m2.day = dm.day
              and m1.day <= dm.day) as prdstartday
      from date_master dm) p
where p.day = d.business_day_date

same query, just written a little differently.

good luck :)
0
 
GoodNameAuthor Commented:
Thank you for your help.
But when I used your version just adding

create materialized view product_sales_mv
refresh complete on demand
as

I have got the same error message:
               " ORA-22818: subquery expressions not allowed here ".

Please advice.
0
 
gatorvipCommented:
http://download-west.oracle.com/docs/cd/B12037_01/server.101/b10736/basicmv.htm#sthref431

>>>Besides tables, other elements such as views, inline views (subqueries in the FROM clause of a SELECT statement), subqueries, and materialized views can all be joined or referenced in the SELECT clause. You cannot, however, define a materialized with a subquery in the select list of the defining query. You can, however, include subqueries elsewhere in the defining query, such as in the WHERE clause.
<<<<
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

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