DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: cannot create a fast refresh materialized view from a complex query

  1. #1
    Join Date
    Mar 2001
    Location
    Reading, U.K
    Posts
    598

    cannot create a fast refresh materialized view from a complex query

    Hi All,
    If I add this part AND TO_CHAR(A.CAL_DAY_DT, 'YYYYMM') <= TO_CHAR(TRUNC(SYSDATE), 'YYYYMM') Iam getting the follwoing error
    cannot create a fast refresh materialized view from a complex query
    Cheers!
    OraKid.

  2. #2
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Documented behaviour ... see this documentation
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  3. #3
    Join Date
    Mar 2001
    Location
    Reading, U.K
    Posts
    598
    Thnx Slimdave.
    Then how do I handle this...
    I need all the records from day 1 in my dim table till last day of the current month.

    eg, first day in my dim table 01-01-2000 and last day of the curent month 30-09-2003.
    Cheers!
    OraKid.

  4. #4
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Your MV will have to be based entirely on data in the table(s) without reference to sysdate.

    A simple approach would be to include a column that states whether the row is to be included in the MV or not, filte the MV by that column value, and update it peridically (every month, I guess) to generate the incluion of new rows into the MV.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  5. #5
    Join Date
    Mar 2001
    Location
    Reading, U.K
    Posts
    598
    Thnx Slimdave. I got my answer by adding another table...

    One more help. Is there a substitute for anlytical function LAG(). I mean can we write this is a different way.

    'coz I cannot use LAG() function in FAST REFRESH MATERIALIZED VIEWS.

    Issue boy...

    LAG(A.DELINQUENCY_STATUS_ID) OVER (PARTITION BY
    A.ACCOUNT_ID ORDER BY
    TRIM(C.CAL_MONTH_STR) ASC) AS PRIOR_DELINQUENCY_STATUS_ID


    I need you input on this. This is the script,

    CREATE MATERIALIZED VIEW MV_SUB_DELINQUENCY_FLOW_CYCLE
    REFRESH FAST ON DEMAND
    AS
    SELECT
    B.PORTFOLIO_GROUP_ID,
    A.SITE_ID,
    A.ACCOUNT_ID,
    A.CYCLE_NUMBER,
    TRIM(C.CAL_MONTH_STR) REPORT_PERIOD,
    LAG(A.DELINQUENCY_STATUS_ID) OVER (PARTITION BY
    A.ACCOUNT_ID ORDER BY
    TRIM(C.CAL_MONTH_STR) ASC) AS PRIOR_DELINQUENCY_STATUS_ID,
    A.DELINQUENCY_STATUS_ID,
    (CASE WHEN A.DELINQUENCY_STATUS_ID = 10 AND A.BANKRUPTCY_CHGOFF > 0 THEN
    'Y'
    ELSE 'N' END) BANKRUPTCY_FLAG,
    NVL(A.CYCLE_CLOSING_BAL, 0) CLOSING_BAL,
    A.ROWID AS AROWID,
    B.ROWID AS BROWID,
    C.ROWID AS CROWID
    FROM CYCLE_SUMMARY_FACT A,
    PORTFOLIOS_DIM B,
    CCRT_CALENDAR_DIM C
    WHERE A.PORTFOLIO_ID = B.PORTFOLIO_ID
    AND A.CYCLE_END_DATE = C.CAL_DAY_DT
    AND A.ACCOUNT_STATUS_ID NOT IN (1, 2);
    Cheers!
    OraKid.

  6. #6
    Join Date
    Mar 2001
    Location
    Reading, U.K
    Posts
    598
    CAN ANYONE SHARE THEIR IDEA PLSSSS...

    One more help. Is there a substitute for anlytical function LAG(). I mean can we write this is a different way.

    'coz I cannot use LAG() function in FAST REFRESH MATERIALIZED VIEWS.

    Issue boy...

    LAG(A.DELINQUENCY_STATUS_ID) OVER (PARTITION BY
    A.ACCOUNT_ID ORDER BY
    TRIM(C.CAL_MONTH_STR) ASC) AS PRIOR_DELINQUENCY_STATUS_ID

    I need you input on this. This is the script,

    CREATE MATERIALIZED VIEW MV_SUB_DELINQUENCY_FLOW_CYCLE
    REFRESH FAST ON DEMAND
    AS
    SELECT
    B.PORTFOLIO_GROUP_ID,
    A.SITE_ID,
    A.ACCOUNT_ID,
    A.CYCLE_NUMBER,
    TRIM(C.CAL_MONTH_STR) REPORT_PERIOD,
    LAG(A.DELINQUENCY_STATUS_ID) OVER (PARTITION BY
    A.ACCOUNT_ID ORDER BY
    TRIM(C.CAL_MONTH_STR) ASC) AS PRIOR_DELINQUENCY_STATUS_ID,
    A.DELINQUENCY_STATUS_ID,
    (CASE WHEN A.DELINQUENCY_STATUS_ID = 10 AND A.BANKRUPTCY_CHGOFF > 0 THEN
    'Y'
    ELSE 'N' END) BANKRUPTCY_FLAG,
    NVL(A.CYCLE_CLOSING_BAL, 0) CLOSING_BAL,
    A.ROWID AS AROWID,
    B.ROWID AS BROWID,
    C.ROWID AS CROWID
    FROM CYCLE_SUMMARY_FACT A,
    PORTFOLIOS_DIM B,
    CCRT_CALENDAR_DIM C
    WHERE A.PORTFOLIO_ID = B.PORTFOLIO_ID
    AND A.CYCLE_END_DATE = C.CAL_DAY_DT
    AND A.ACCOUNT_STATUS_ID NOT IN (1, 2);
    Cheers!
    OraKid.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width