1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Hi, can you help me in converting this to a simple query

Discussion in 'SQL PL/SQL' started by geralde.givens, Apr 19, 2018.

  1. geralde.givens

    geralde.givens Newly Initiated

    Messages:
    1
    Likes Received:
    0
    Trophy Points:
    30
    Location:
    New York, USA
    Hi, I am trying to use this in a materialized view and got below error:


    SQL Error: ORA-12015: cannot create a fast refresh materialized view from a complex query

    12015. 00000 - "cannot create a fast refresh materialized view from a complex query"

    *Cause: Neither ROWIDs and nor primary key constraints are supported for

    complex queries.

    *Action: Reissue the command with the REFRESH FORCE or REFRESH COMPLETE

    option or create a simple materialized view.


    select IR.rowid MV_INST_LOBR_ROWID, J.rowid Job_ROWID, J.* FROM JOB J

    LEFT JOIN MV_INST_LOB_R IR ON

    (IR.I1503_CUST_AC_NO_PT1 = J.I3200_CUST_AC_NO_PT1 AND IR.I1503_CUST_AC_NO_PT2 = J.I3200_CUST_AC_NO_PT2 AND IR.I1503_INST_SEQ_NO = J.I3200_INST_SEQ_NO)

    WHERE IR.I1503_CUST_AC_NO_PT1 IS NOT NULL AND IR.I1503_CUST_AC_NO_PT2 IS NOT NULL

    UNION SELECT null, J.rowid Job_ROWID, J.* FROM JOB J WHERE J.I3200_CUST_AC_NO_PT1 IS NULL AND J.I3200_CUST_AC_NO_PT1 IS NULL;

    Basically, the intention is to get all the records joining MV_INST_LOB_R and JOB table
    where MV_INST_LOB_R do not have record for the joining condition J.I3200_CUST_AC_NO_PT1 / J.I3200_CUST_AC_NO_PT2


    Can you help me in converting to simple query, so that I can use simple materialized view.
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,600
    Likes Received:
    364
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Probably not; it's difficult to impossible to convert outer joins to 'simple' queries (the query itself is actually simple, it's the outer joins that 'complicate' things).

    You will most likely need to abandon the fast refresh for a refresh force or refresh complete.