Home » SQL & PL/SQL » SQL & PL/SQL » Create Materialized View in Oracle 9.2.0.5
Create Materialized View in Oracle 9.2.0.5 [message #211035] Mon, 25 December 2006 10:47 Go to next message
barnard90
Messages: 4
Registered: December 2006
Junior Member
Hi

I am trying to create a Materialized View based on a Normal View for Complete Refresh in Oracle 9.2.0.6

The normal view is Employee_V

CREATE MATERIALIZED VIEW Employee_MV AS SELECT * FROM Employee_V

This works perfectly well in Oracle 9.2.0.6.

But the Materialized View Creation Fails in Oracle 9.2.0.5
with the same Create Options
Is there a limitation in Oracle 9.2.0.5 in Creation of a Materialized View based on a Normal View ?
Please suggest
Re: Create Materialized View in Oracle 9.2.0.5 [message #211148 is a reply to message #211035] Tue, 26 December 2006 18:19 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
What's the error message?
Re: Create Materialized View in Oracle 9.2.0.5 [message #211281 is a reply to message #211035] Wed, 27 December 2006 15:32 Go to previous messageGo to next message
barnard90
Messages: 4
Registered: December 2006
Junior Member
The Error I am getting in 9.2.0.5 is

'Cannot Create Materialized View with a Sub Query'

I have a Sub query in my View Definition Employee_V.
That Sub query is necessary
Is there a way that I can have tge sub query in View Creation and I can still create a materialized view
based on the normal view in 9.2.0.5

Re: Create Materialized View in Oracle 9.2.0.5 [message #211291 is a reply to message #211281] Wed, 27 December 2006 18:29 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Hmmm. The ol' drip-feed important information to the expert trick, huh? That's the second time I've fallen for that one this week.

Undaunted, I'll press on though. But this time I'll ask more than one question and hope for more than one answer.

1. What is the error number?
2. What type of sub-query is it (EXISTS, IN, NOT IN, NOT EXISTS, ANY, ALL, scalar)
3. What MV features are you trying to use (eg. Fast Refresh, Query rewrite)

Ross Leishman
Re: Create Materialized View in Oracle 9.2.0.5 [message #211416 is a reply to message #211035] Thu, 28 December 2006 09:39 Go to previous messageGo to next message
barnard90
Messages: 4
Registered: December 2006
Junior Member
Thanks for the post

I am using COMPLETE REFERESH

I am using
EXISTS , IN , NOT EXISTS in my sub queries in my view creation.



The complete view is quite big and is as below

CREATE OR REPLACE VIEW TA_PROJECT_LIFECYCLE_V
AS
  (SELECT  ---  This is the outer most loop to fetch the fields in the manner they are displayed in view
   V1.PROJECT_ID,
   V1.PROJECT_SHORT_NME,
   V1.PROJECT_STATUS_NAME,
   V1.COUNTRY_CODE,
   V1.COUNTRY_NME,
   V1.REGIONAL_DEPT_CODE,
   V1.REGIONAL_DEPT_NME,
   V1.PROJECT_APP_DURATION_CNT,
   V1.IFC_SECTOR_NME,
   V1.IFC_SECTOR_CODE,
   V1.FRONTIER_IND_TXT,
   V1.DIVISION_ALPHA_CODE,
   V1.DEPT_ALPHA_CODE,
   V1.DEPT_NAME,
   V1.RESP_CENTER_NME,
   V1.PROJECT_CATEGORY_CODE,
   V1.PROJECT_CATEGORY_NME,
   V1.PROJECT_SUB_CATEGORY_CODE,
   V1.PROJECT_SUB_CATEGORY_NME,
   V1.PROJECT_CREATION_DATE,
   V1.TAAS_DOCUMENT_STATUS_CODE,
   V1.TAAS_DOC_TEMPLATE_TYPE_NME,
   V1.INVESTMENT_LEAD_CODE,
   V1.ER_APPROVAL_DATE,
   V1.TAAS_DOC_TEMPLATE_CAT_CODE,
   V1.TAAS_DOCUMENT_STATUS_NME,
   V1.ER_ESTIMATED_FUNDING_NEEDS_AMT,
   V1.TOTAL_FUNDING_SOURCE_AMT,
   V1.CHARITY_AMT,
   V1.ORIGINAL_APPROVAL_DATE,
   V1.REVISED_APPROVAL_DATE,
   V1.REPLICATION_IND,
   V1.APPROVAL_TEMPLATE_CAT_CODE,
   V1.AWARD_GRANT_IND,
   DECODE(V1.BUSINESS_LINE_CODE,NULL,V2.BUSINESS_LINE_CODE,
                                V1.BUSINESS_LINE_CODE) AS BUSINESS_LINE_CODE,
   DECODE(V1.BUSINESS_LINE_NAME,NULL,V2.BUSINESS_LINE_NME,
                                V1.BUSINESS_LINE_NAME) AS BUSINESS_LINE_NAME
   FROM   (SELECT   -- Sub level query which merges two queries
           V1.PROJECT_ID AS PROJECT_ID,
           V1.PROJECT_NME AS PROJECT_SHORT_NME,
           V1.PROJECT_STATUS_NME AS PROJECT_STATUS_NAME,
           V1.COUNTRY_CODE AS COUNTRY_CODE,
           V1.COUNTRY_NME AS COUNTRY_NME,
           V1.REGIONAL_DEPT_CODE,
           V3.DEPT_NME AS REGIONAL_DEPT_NME,
           --1 + round((v1.fin_Dt - v1.st_dt)/30) as project_app_duration_cnt,
           --trunc(months_between(v1.fin_Dt,v1.st_dt)) +1 as project_app_duration_cnt,
           ((TO_NUMBER(TO_CHAR(V1.FIN_DT,'yyyy')) * 12 + TO_NUMBER(TO_CHAR(V1.FIN_DT,'mm')) + 1)) - ((TO_NUMBER(TO_CHAR(V1.ST_DT,'yyyy')) * 12 + TO_NUMBER(TO_CHAR(V1.ST_DT,'mm')))) AS PROJECT_APP_DURATION_CNT,
           V1.IFC_SECTOR_NME,
           V1.IFC_SECTOR_CODE,
           V1.FC_IND_TEXT AS FRONTIER_IND_TXT,
           V1.DIV_ALPHA_CODE AS DIVISION_ALPHA_CODE,
           V1.DEPT_ALPHA AS DEPT_ALPHA_CODE,
           V1.DEPT_NM AS DEPT_NAME,
           V1.RESP_CENTER AS RESP_CENTER_NME,
           V1.PROJECT_CATEGORY_CODE,
           V1.PROJECT_CATEGORY_NME,
           V1.PROJECT_SUB_CATEGORY_CODE,
           V1.PROJECT_SUB_CATEGORY_NME,
           V1.CREATION_DATE AS PROJECT_CREATION_DATE,
           V1.TAAS_DOCUMENT_STATUS_CODE,
           V1.TAAS_DOC_TEMPLATE_TYPE_NME,
           V1.INVESTMENT_LEAD_CODE,
           V1.TAAS_DOCUMENT_STATUS_DATE AS ER_APPROVAL_DATE,
           V1.TAAS_DOC_TEMPLATE_CAT_CODE,
           V1.TAAS_DOCUMENT_STATUS_NME,
           V1.FUNDING_AMOUNT AS ER_ESTIMATED_FUNDING_NEEDS_AMT,
           V1.APPROVAL_AMT AS TOTAL_FUNDING_SOURCE_AMT,
           V1.CHARITY_AMT,
           V1.MIN_DT AS ORIGINAL_APPROVAL_DATE,
           V1.MAX_DT AS REVISED_APPROVAL_DATE,
           V1.REP_IND AS REPLICATION_IND,
           V1.APP_CAT_CODE AS APPROVAL_TEMPLATE_CAT_CODE,
           V1.BL_CODE AS BUSINESS_LINE_CODE,
           V1.BL_NAME AS BUSINESS_LINE_NAME,
           V1.AWARD_GRANT_IND AS AWARD_GRANT_IND
           FROM     (SELECT     --- This query merges ER and approval information
                     V1.PROJECT_ID,
                     V1.PROJECT_NME,
                     V1.PROJECT_STATUS_NME,
                     V1.COUNTRY_CODE,
                     V1.COUNTRY_NME,
                     V1.REGIONAL_DEPT_CODE,
                     V1.IFC_SECTOR_NME,
                     V1.IFC_SECTOR_CODE,
                     V1.FC_IND_TEXT,
                     V1.DIV_ALPHA_CODE,
                     V1.DEPT_ALPHA,
                     V1.DEPT_NM,
                     V1.RESP_CENTER,
                     V1.PROJECT_CATEGORY_CODE,
                     V1.PROJECT_SUB_CATEGORY_CODE,
                     V1.PROJECT_CATEGORY_NME,
                     V1.PROJECT_SUB_CATEGORY_NME,
                     V1.CREATION_DATE,
                     V1.TAAS_DOCUMENT_STATUS_CODE,
                     V1.TAAS_DOC_TEMPLATE_TYPE_NME,
                     V1.INVESTMENT_LEAD_CODE,
                     V1.TAAS_DOCUMENT_STATUS_DATE,
                     V1.TAAS_DOC_TEMPLATE_CAT_CODE,
                     V1.TAAS_DOCUMENT_STATUS_NME,
                     V1.FUNDING_AMOUNT,
                     (SELECT V4.START_DATE
                      FROM   TAAS.PROJECT_ACTIVITY_TIMELINE V4
                      WHERE  V4.TAAS_DOCUMENT_ID = V2.TAAS_DOCUMENT_ID
                             AND V4.TAAS_DOCUMENT_VERSION_NBR = V2.TAAS_DOCUMENT_VERSION_NBR
                             AND V4.PROJECT_ACTIVITY_CATEGORY_CODE = '101'
                             AND V4.PROJECT_ACTIVITY_TYPE_CODE = '101') AS ST_DT,
                     (SELECT V4.FINISH_DATE
                      FROM   TAAS.PROJECT_ACTIVITY_TIMELINE V4
                      WHERE  V4.TAAS_DOCUMENT_ID = V2.TAAS_DOCUMENT_ID
                             AND V4.TAAS_DOCUMENT_VERSION_NBR = V2.TAAS_DOCUMENT_VERSION_NBR
                             AND V4.PROJECT_ACTIVITY_CATEGORY_CODE = '102'
                             AND V4.PROJECT_ACTIVITY_TYPE_CODE = '102') AS FIN_DT,
                     V2.TAAS_DOCUMENT_ID,
                     V2.TAAS_DOCUMENT_VERSION_NBR,
                     V2.APPROVAL_AMT,
                     V2.CHARITY_AMT,
                     V2.MIN_DT,
                     V2.MAX_DT,
                     V2.REP_IND,
                     V2.APP_CAT_CODE,
                     V2.BL_CODE,
                     V2.BL_NAME,
                     V2.AWARD_GRANT_IND
                     FROM   (SELECT     ---- Selecting Country , Department information
                             V1.PROJECT_ID,
                             V1.PROJECT_NME,
                             V1.PROJECT_STATUS_NME,
                             V1.COUNTRY_CODE,
                             V1.COUNTRY_NME,
                             V1.REGIONAL_DEPT_CODE,
                             V1.IFC_SECTOR_NME,
                             V1.IFC_SECTOR_CODE,
                             V1.FC_IND_TEXT,
                             V1.DIV_ALPHA_CODE,
                             V1.DEPT_ALPHA,
                             V1.DEPT_NM,
                             V1.RESP_CENTER,
                             V1.PROJECT_CATEGORY_NME,
                             V1.PROJECT_CATEGORY_CODE,
                             V1.PROJECT_SUB_CATEGORY_CODE,
                             V1.PROJECT_SUB_CATEGORY_NME,
                             V1.CREATION_DATE,
                             V2.TAAS_DOCUMENT_STATUS_CODE,
                             V2.TAAS_DOC_TEMPLATE_TYPE_NME,
                             V2.INVESTMENT_LEAD_CODE,
                             V2.TAAS_DOCUMENT_STATUS_DATE,
                             V2.TAAS_DOC_TEMPLATE_CAT_CODE,
                             V2.TAAS_DOCUMENT_STATUS_NME,
                             V2.TAAS_DOCUMENT_ID,
                             V2.TAAS_DOCUMENT_VERSION_NBR,
                             V2.FUNDING_AMOUNT
                             FROM   (SELECT   PROJECT.PROJECT_ID,
                                              PROJECT.PROJECT_NME,  --- Selecting Country, Department information
                                              PROJECT_STATUS.PROJECT_STATUS_NME,
                                              COUNTRY.COUNTRY_NME,
                                              COUNTRY.COUNTRY_CODE,
                                              -- nvl(fc.Frontier_Country_Indicator_Txt,'non-Frontier') as fc_ind_text,
                                              --decode(fc.frontier_country_ind,'Y','Frontier','N','non-Frontier') as fc_ind_text,
                                              (SELECT DECODE(FC.FRONTIER_COUNTRY_IND,'Y','Frontier',
                                                                                     'N','non-Frontier') AS FC_IND_TEXT
                                               FROM   REFERENCE.FRONTIER_COUNTRY_SUMMARY FC
                                               WHERE  FC.COUNTRY_CODE (+)  = COUNTRY.COUNTRY_CODE
                                                      AND FC.AS_OF_DATE IN (SELECT MAX(AS_OF_DATE)
                                                                            FROM   REFERENCE.FRONTIER_COUNTRY_SUMMARY)) AS FC_IND_TEXT,
                                              -- fc.Frontier_Country_Indicator_Txt as fc_ind_text,
                                              COUNTRY.REGIONAL_DEPT_CODE,
                                              IFC.IFC_SECTOR_NME,
                                              IFC.IFC_SECTOR_CODE,
                                              CAT.PROJECT_CATEGORY_NME,
                                              CAT.PROJECT_CATEGORY_CODE,
                                              SCAT.PROJECT_SUB_CATEGORY_NME,
                                              SCAT.PROJECT_SUB_CATEGORY_CODE,
                                              PROJECT.CREATION_DATE,
                                              V2.DIV_ALPHA_CODE,
                                              V2.RESP_CENTER,
                                              V2.DEPT_ALPHA,
                                              V2.DEPT_NM
                                     FROM     IPS.PROJECT,
                                              IPS.PROJECT_STATUS,
                                              REFERENCE.COUNTRY COUNTRY,
                                              --   REFERENCE.FRONTIER_COUNTRY_SUMMARY fc,
                                              (SELECT   PROJECT.PROJECT_ID,
                                                        MIN(RES.DIV_ALPHA_CODE) AS DIV_ALPHA_CODE,
                                                        MIN(RES.RESP_CENTER_NME) AS RESP_CENTER,
                                                        MIN(DEP.DEPT_ALPHA_CODE) AS DEPT_ALPHA,
                                                        MIN(DEP.DEPT_NME) AS DEPT_NM
                                               FROM     IPS.PROJECT PROJECT,
                                                        IPS.PROJECT_RESPONSIBILITY RESP,
                                                        REFERENCE.RESPONSIBILITY_CENTER RES,
                                                        REFERENCE.DEPARTMENT DEP
                                               WHERE    ((PROJECT.PROJECT_ID = RESP.PROJECT_ID)
                                                         AND (RES.DEPT_DIV_CODE = RESP.DEPT_DIV_CODE)
                                                         AND (DEP.DEPT_CODE = RES.DEPT_CODE)
                                                         AND (RESP.DEPT_ROLE_END_DATE IS NULL ))
                                                        AND PROJECT.PROJECT_ID IN (SELECT   DISTINCT PROJECT_ID
                                                                                   FROM     TAAS.TAAS_PROJECT_PROFILE
                                                                                   GROUP BY PROJECT_ID)
                                               GROUP BY PROJECT.PROJECT_ID) V2,
                                              REFERENCE.IFC_SECTOR IFC,
                                              IPS.PROJECT_CATEGORY CAT,
                                              IPS.PROJECT_SUB_CATEGORY SCAT
                                     WHERE    ((PROJECT_STATUS.PROJECT_STATUS_CODE = PROJECT.PROJECT_STATUS_CODE)
                                               AND (COUNTRY.COUNTRY_CODE = PROJECT.COUNTRY_CODE)
                                                   --and (fc.country_code(+) = country.country_code  )
                                                   --and exists (select max(as_of_date) from reference.frontier_country_summary)
                                                   -- and fc.as_of_date in ((select max(as_of_date) from reference.frontier_country_summary))
                                               AND V2.PROJECT_ID (+)  = PROJECT.PROJECT_ID
                                               AND (IFC.IFC_SECTOR_CODE = PROJECT.IFC_SECTOR_CODE)
                                               AND (CAT.PROJECT_CATEGORY_CODE = PROJECT.PROJECT_CATEGORY_CODE)
                                               AND (SCAT.PROJECT_SUB_CATEGORY_CODE = PROJECT.PROJECT_SUB_CATEGORY_CODE)
                                               AND (PROJECT.PROJECT_ID IN (SELECT DISTINCT (PROJECT_ID)
                                                                           FROM   TAAS.TAAS_PROJECT_PROFILE)))
                                     ORDER BY IPS.PROJECT.PROJECT_ID) V1,
                                    (SELECT   --- ER information
                                     TP.PROJECT_ID,
                                     TP.TAAS_DOCUMENT_ID,
                                     TP.TAAS_DOCUMENT_VERSION_NBR,
                                     TD.TAAS_DOCUMENT_STATUS_CODE,
                                     TAA.TAAS_DOC_TEMPLATE_TYPE_NME,
                                     TP.INVESTMENT_LEAD_CODE,
                                     TD.TAAS_DOCUMENT_STATUS_DATE,
                                     TD.TAAS_DOC_TEMPLATE_CAT_CODE,
                                     TDS.TAAS_DOCUMENT_STATUS_NME,
                                     TFP.FUNDING_AMOUNT
                                     FROM   TAAS.TAAS_PROJECT_PROFILE TP,
                                            TAAS.TAAS_DOCUMENT TD,
                                            TAAS.TAAS_DOCUMENT_STATUS TDS,
                                            TAAS.TAAS_DOCUMENT_TEMPLATE_TYPE TAA,
                                            TAAS.FINANCIAL_PLAN TFP
                                     WHERE  TD.TAAS_DOC_TEMPLATE_CAT_CODE = 'ER'
                                            AND TFP.FUNDING_SOURCE_CODE = '114'
                                            AND TFP.FUNDING_SOURCE_TYPE_CODE = '104'
                                            AND TAAS_DOCUMENT_STATUS_DATE IN (SELECT   MAX(TAAS_DOCUMENT_STATUS_DATE)
                                                                              FROM     TAAS.TAAS_DOCUMENT
                                                                              WHERE    TAAS_DOCUMENT_STATUS_CODE = '101'
                                                                                       AND TAAS_DOC_TEMPLATE_CAT_CODE = 'ER'
                                                                                       AND ACTIVE_IND = 'Y'
                                                                              GROUP BY PROJECT_ID)
                                            AND TP.TAAS_DOCUMENT_VERSION_NBR IN (SELECT   MAX(TAAS_DOCUMENT_VERSION_NBR)
                                                                                 FROM     TAAS.TAAS_DOCUMENT
                                                                                 WHERE    TAAS_DOCUMENT_STATUS_CODE = '101'
                                                                                          AND TAAS_DOC_TEMPLATE_CAT_CODE = 'ER'
                                                                                          AND ACTIVE_IND = 'Y'
                                                                                 GROUP BY PROJECT_ID)
                                            AND TD.TAAS_DOCUMENT_STATUS_CODE = '101'
                                            AND (TD.TAAS_DOCUMENT_ID = TP.TAAS_DOCUMENT_ID)
                                            AND (TD.TAAS_DOCUMENT_VERSION_NBR = TP.TAAS_DOCUMENT_VERSION_NBR)
                                            AND (TDS.TAAS_DOCUMENT_STATUS_CODE = TD.TAAS_DOCUMENT_STATUS_CODE)
                                            AND (TAA.TAAS_DOC_TEMPLATE_TYPE_CODE = TD.TAAS_DOC_TEMPLATE_TYPE_CODE)
                                            AND (TD.TAAS_DOCUMENT_ID = TFP.TAAS_DOCUMENT_ID)
                                            AND (TD.TAAS_DOCUMENT_VERSION_NBR = TFP.TAAS_DOCUMENT_VERSION_NBR)
                                            AND (TD.TAAS_DOCUMENT_STATUS_CODE = '101')
                                            AND (TD.TAAS_DOC_TEMPLATE_CAT_CODE = 'ER')) V2  -- ER view
                             WHERE  V1.PROJECT_ID = V2.PROJECT_ID (+) ) V1,
                            ( /*start from here */  --- Final apporval view which links all the three sub approval views below
                            SELECT   ALL_PROJ.PROJECT_ID,
                                     ALL_PROJ.TAAS_DOCUMENT_ID,
                                     PROJ_DOC_STATUS_DATES.MIN_STATUS_DATE AS MIN_DT,
                                     PROJ_DOC_STATUS_DATES.MAX_STATUS_DATE AS MAX_DT,
                                     PROJ_DOC_STATUS_DATES.REP_IND,
                                     PROJ_DOC_STATUS_DATES.APP_CAT_CODE,
                                     PROJ_DOC_STATUS_DATES.AWARD_GRANT_IND,
                                     PROJ_DOC_STATUS_DATES.TAAS_DOCUMENT_VERSION_NBR,
                                     PROJ_DOC_STATUS_DATES.BL_CODE,
                                     PROJ_DOC_STATUS_DATES.BL_NAME,
                                     NVL(PROJ_AMT_101_104.AMT_101_104,0) AS APPROVAL_AMT,
                                     NVL(PROJ_AMT_105.AMT_105,0) AS CHARITY_AMT
                             FROM     (--one
                                      SELECT   TA1.PROJECT_ID,
                                               TA1.TAAS_DOCUMENT_ID,
                                               MAX(TA1.TAAS_DOCUMENT_STATUS_DATE) LAST_VERSION_STATUS_DATE
                                       FROM     TAAS.TAAS_DOCUMENT TA1,
                                                TAAS.TAAS_DOCUMENT TD
                                       WHERE    TA1.PROJECT_ID = TD.PROJECT_ID
                                                AND TA1.TAAS_DOCUMENT_ID = TD.TAAS_DOCUMENT_ID
                                                AND TA1.TAAS_DOCUMENT_VERSION_NBR = TD.TAAS_DOCUMENT_VERSION_NBR
                                                AND TD.TAAS_DOC_TEMPLATE_CAT_CODE = 'AP'
                                                AND TD.TAAS_DOCUMENT_STATUS_CODE = '101'
                                                AND TD.ACTIVE_IND = 'Y'
                                                AND NOT EXISTS (SELECT PROJECT_ID,
                                                                       TAAS_DOCUMENT_VERSION_NBR
                                                                FROM   TAAS.TAAS_DOCUMENT TD1
                                                                WHERE  TA1.PROJECT_ID = TD1.PROJECT_ID
                                                                       AND TA1.TAAS_DOCUMENT_ID = TD1.TAAS_DOCUMENT_ID
                                                                       AND TD1.TAAS_DOC_TEMPLATE_CAT_CODE = 'AP'
                                                                       AND TD1.ACTIVE_IND = 'Y'
                                                                       AND TD.TAAS_DOCUMENT_STATUS_CODE = '101'
                                                                       AND TA1.TAAS_DOCUMENT_VERSION_NBR < TD1.TAAS_DOCUMENT_VERSION_NBR)
                                       GROUP BY TA1.PROJECT_ID,
                                                TA1.TAAS_DOCUMENT_ID) ALL_PROJ
                                      LEFT OUTER JOIN (-- twoo  -- This is used to fetch the charity amount
                                                      SELECT   TA1.PROJECT_ID,
                                                               TA1.TAAS_DOCUMENT_ID,
                                                               SUM(FUNDING_AMOUNT) AMT_105
                                                       FROM     TAAS.TAAS_DOCUMENT TA1,
                                                                TAAS.FINANCIAL_PLAN,
                                                                TAAS.TAAS_DOCUMENT TD
                                                       WHERE    TA1.PROJECT_ID = TD.PROJECT_ID
                                                                AND TA1.TAAS_DOCUMENT_ID = TD.TAAS_DOCUMENT_ID
                                                                AND TA1.TAAS_DOCUMENT_VERSION_NBR = TD.TAAS_DOCUMENT_VERSION_NBR
                                                                AND TD.TAAS_DOC_TEMPLATE_CAT_CODE = 'AP'
                                                                AND TD.TAAS_DOCUMENT_STATUS_CODE = '101'
                                                                AND TD.ACTIVE_IND = 'Y'
                                                                AND TA1.TAAS_DOCUMENT_ID = TAAS.FINANCIAL_PLAN.TAAS_DOCUMENT_ID
                                                                AND FUNDING_SOURCE_TYPE_CODE = '105'
                                                                AND TA1.TAAS_DOCUMENT_VERSION_NBR = TAAS.FINANCIAL_PLAN.TAAS_DOCUMENT_VERSION_NBR
                                                                AND NOT EXISTS (SELECT PROJECT_ID,
                                                                                       TAAS_DOCUMENT_VERSION_NBR
                                                                                FROM   TAAS.TAAS_DOCUMENT TD1
                                                                                WHERE  TA1.PROJECT_ID = TD1.PROJECT_ID
                                                                                       AND TA1.TAAS_DOCUMENT_ID = TD1.TAAS_DOCUMENT_ID
                                                                                       AND TD1.TAAS_DOC_TEMPLATE_CAT_CODE = 'AP'
                                                                                       AND TD1.ACTIVE_IND = 'Y'
                                                                                       AND TD.TAAS_DOCUMENT_STATUS_CODE = '101'
                                                                                       AND TA1.TAAS_DOCUMENT_VERSION_NBR < TD1.TAAS_DOCUMENT_VERSION_NBR)
                                                       GROUP BY TA1.PROJECT_ID,
                                                                TA1.TAAS_DOCUMENT_ID) PROJ_AMT_105
                                        ON ALL_PROJ.PROJECT_ID = PROJ_AMT_105.PROJECT_ID
                                           AND ALL_PROJ.TAAS_DOCUMENT_ID = PROJ_AMT_105.TAAS_DOCUMENT_ID
                                      LEFT OUTER JOIN (-- three   --- This is used to fetch the total funding source amount
                                                      SELECT   TA1.PROJECT_ID,
                                                               TA1.TAAS_DOCUMENT_ID,
                                                               SUM(FUNDING_AMOUNT) AMT_101_104
                                                       FROM     TAAS.TAAS_DOCUMENT TA1,
                                                                TAAS.FINANCIAL_PLAN,
                                                                TAAS.TAAS_DOCUMENT TD
                                                       WHERE    TA1.PROJECT_ID = TD.PROJECT_ID
                                                                AND TA1.TAAS_DOCUMENT_ID = TD.TAAS_DOCUMENT_ID
                                                                AND TA1.TAAS_DOCUMENT_VERSION_NBR = TD.TAAS_DOCUMENT_VERSION_NBR
                                                                AND TD.TAAS_DOC_TEMPLATE_CAT_CODE = 'AP'
                                                                AND TD.TAAS_DOCUMENT_STATUS_CODE = '101'
                                                                AND TD.ACTIVE_IND = 'Y'
                                                                AND TA1.TAAS_DOCUMENT_ID = TAAS.FINANCIAL_PLAN.TAAS_DOCUMENT_ID
                                                                AND FUNDING_SOURCE_TYPE_CODE IN ('101',
                                                                                                 '102',
                                                                                                 '103',
                                                                                                 '104')
                                                                AND TA1.TAAS_DOCUMENT_VERSION_NBR = TAAS.FINANCIAL_PLAN.TAAS_DOCUMENT_VERSION_NBR
                                                                AND NOT EXISTS (SELECT PROJECT_ID,
                                                                                       TAAS_DOCUMENT_VERSION_NBR
                                                                                FROM   TAAS.TAAS_DOCUMENT TD1
                                                                                WHERE  TA1.PROJECT_ID = TD1.PROJECT_ID
                                                                                       AND TA1.TAAS_DOCUMENT_ID = TD1.TAAS_DOCUMENT_ID
                                                                                       AND TD1.TAAS_DOC_TEMPLATE_CAT_CODE = 'AP'
                                                                                       AND TD1.ACTIVE_IND = 'Y'
                                                                                       AND TD.TAAS_DOCUMENT_STATUS_CODE = '101'
                                                                                       AND TA1.TAAS_DOCUMENT_VERSION_NBR < TD1.TAAS_DOCUMENT_VERSION_NBR)
                                                       GROUP BY TA1.PROJECT_ID,
                                                                TA1.TAAS_DOCUMENT_ID --,ta1.taas_document_version_nbr
                                                                ) PROJ_AMT_101_104
                                        ON ALL_PROJ.PROJECT_ID = PROJ_AMT_101_104.PROJECT_ID
                                           AND ALL_PROJ.TAAS_DOCUMENT_ID = PROJ_AMT_101_104.TAAS_DOCUMENT_ID
                                      LEFT OUTER JOIN ( -- four  ---  All the necessary fields other than amounts  are fetched here
                                                      SELECT   TA1.PROJECT_ID,
                                                               TA1.TAAS_DOCUMENT_ID,
                                                               MIN(TA1.TAAS_DOCUMENT_STATUS_DATE) MIN_STATUS_DATE,
                                                               MAX(TA1.TAAS_DOCUMENT_STATUS_DATE) MAX_STATUS_DATE,
                                                               MAX(TA1.TAAS_DOCUMENT_VERSION_NBR) AS TAAS_DOCUMENT_VERSION_NBR,
                                                               (SELECT MAX(TPP.AWARD_GRANT_IND)
                                                                FROM   TAAS.TAAS_PROJECT_PROFILE TPP
                                                                WHERE  TPP.PROJECT_ID = TA1.PROJECT_ID
                                                                       AND TPP.TAAS_DOCUMENT_ID = TA1.TAAS_DOCUMENT_ID) AS AWARD_GRANT_IND,
                                                               (SELECT MAX(TPP.REPLICATION_IND)
                                                                FROM   TAAS.TAAS_PROJECT_PROFILE TPP
                                                                WHERE  TPP.PROJECT_ID = TA1.PROJECT_ID
                                                                       AND TPP.TAAS_DOCUMENT_ID = TA1.TAAS_DOCUMENT_ID) AS REP_IND,
                                                               (SELECT MAX(TD.TAAS_DOC_TEMPLATE_CAT_CODE)
                                                                FROM   TAAS.TAAS_DOCUMENT TD
                                                                WHERE  TD.PROJECT_ID = TA1.PROJECT_ID
                                                                       AND TD.TAAS_DOCUMENT_ID = TA1.TAAS_DOCUMENT_ID) AS APP_CAT_CODE,
                                                               (SELECT   DECODE(MIN(BUSINESS_LINE_SUB_AREA_CODE),100,'A2F',
                                                                                                                 200,'BEE',
                                                                                                                 300,'VAF',
                                                                                                                 400,'ESS',
                                                                                                                 500,'INF')
                                                                FROM     TAAS.PROJECT_BUSINESS_LINE PBL
                                                                WHERE    TA1.TAAS_DOCUMENT_ID = PBL.TAAS_DOCUMENT_ID
                                                                GROUP BY PBL.TAAS_DOCUMENT_ID) BL_CODE,
                                                               (SELECT   DECODE(MIN(BUSINESS_LINE_SUB_AREA_CODE),100,'Access To Finance',
                                                                                                                 200,'Business Enabling Environment',
                                                                                                                 300,'Value Addition to Firms',
                                                                                                                 400,'Environment and Social Sustainability',
                                                                                                                 500,'Infrastructure')
                                                                FROM     TAAS.PROJECT_BUSINESS_LINE PBL
                                                                WHERE    TA1.TAAS_DOCUMENT_ID = PBL.TAAS_DOCUMENT_ID
                                                                GROUP BY PBL.TAAS_DOCUMENT_ID) BL_NAME
                                                       FROM     TAAS.TAAS_DOCUMENT TA1
                                                       WHERE    TA1.TAAS_DOC_TEMPLATE_CAT_CODE = 'AP'
                                                                AND TA1.TAAS_DOCUMENT_STATUS_CODE = '101'
                                                                AND TA1.ACTIVE_IND = 'Y'
                                                       GROUP BY TA1.PROJECT_ID,
                                                                TA1.TAAS_DOCUMENT_ID) PROJ_DOC_STATUS_DATES
                                        ON ALL_PROJ.PROJECT_ID = PROJ_DOC_STATUS_DATES.PROJECT_ID
                                           AND ALL_PROJ.TAAS_DOCUMENT_ID = PROJ_DOC_STATUS_DATES.TAAS_DOCUMENT_ID
                             ORDER BY ALL_PROJ.PROJECT_ID) V2   --- This view fetches all the approval information
                     WHERE  V1.PROJECT_ID = V2.PROJECT_ID (+) ) V1,
                    REFERENCE.DEPARTMENT V3-- join with Business line legacy proj table to get department information
           WHERE    V1.REGIONAL_DEPT_CODE = V3.DEPT_CODE
                    AND V1.MIN_DT IS NOT NULL   -- Required to minimize the records which have null open date to get exact count
           ORDER BY V1.PROJECT_ID) V1
          INNER JOIN TAAS.BUSINESS_LINE_LEGACY_PROJ V2  -- join with Business line legacy proj table to get business line codes
            ON (V1.PROJECT_ID = V2.PROJECT_ID (+) ))

[EDIT by Moderator] Added [code] tags to improve readability.

[Updated on: Thu, 28 December 2006 09:56] by Moderator

Report message to a moderator

Re: Create Materialized View in Oracle 9.2.0.5 [message #211423 is a reply to message #211416] Thu, 28 December 2006 09:59 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Barnard90, check BB code features; it helps a lot if you, for example, enclose your code into the [code] tags so that queries are structured. The one you posted earlier was awfully difficult to read and comprehend.
Re: Create Materialized View in Oracle 9.2.0.5 [message #211469 is a reply to message #211291] Thu, 28 December 2006 19:25 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
rleishman wrote on Thu, 28 December 2006 11:29
...
1. What is the error number?
...

Ross Leishman

Previous Topic: How to display records by sets of 3 interval (total 15)
Next Topic: sql problem
Goto Forum:
  


Current Time: Thu Dec 08 16:16:12 CST 2016

Total time taken to generate the page: 0.04969 seconds