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!

Need to convert the query for simplicity.

Discussion in 'SQL PL/SQL' started by beckham2000, Aug 26, 2017.

  1. beckham2000

    beckham2000 Starter

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    10
    Location:
    technology
    Dear everybody.
    This is my question that needs to be answered.
    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

    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. I have searched for help on the internet and still have not found a solution.
    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

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

    Thank's a lot!
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,600
    Likes Received:
    364
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    How could we do that? You have posted no query results, no source table create statements and no sample data, all of which are needed to produce a different query that produces the same results as the one you've posted.

    Provide the needed information and possibly someone here can assist you.
     
  3. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    771
    Likes Received:
    145
    Trophy Points:
    830
    Location:
    Russian Federation
    Hi.
    Restriction on FAST REFRESH