• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1017
  • 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

Featured Post

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.

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