Max function limitation in Materialized View
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: Max function limitation in Materialized View

  1. #1
    Join Date
    Mar 2001
    Location
    New York , New York
    Posts
    577

    Unhappy

    Hi,

    I am trying to create a MV as

    create materialized view MV_MAXPRICEDATE_STOCKPRICE
    build immediate
    refresh complete on commit
    enable query rewrite
    as
    select company_id,exchange_id,count(price_date),COUNT(*),max(price_date)
    as price_date
    from stock_price
    group by
    company_id,exchange_id
    /

    But it gives me the following error.

    ERROR at line 8:
    ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view

    I know its because of the max function. If I use sum instead of max it works fine.

    Why does Oracle have this limitation and is there any workaround to it because I dont want to refresh My MV manually.

    That will be a pain in the ...... you know what :-)

    Thanks
    Ronnie
    ronnie_yours@yahoo.com

    You can if you think you can.

  2. #2
    Join Date
    Apr 2002
    Location
    Shenzhen, China
    Posts
    327
    I have made a test, It worked fine as your situation.

    Check page 120/618 of Data Warehousing Guide.
    Oracle Certified Master - September, 2003, the Second OCM in China
    *** LOOKING for PART TIME JOB***
    Data Warehouse & Business Intelligence Expert
    MCSE, CCNA, SCJP, SCSA from 1998

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