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!

ORA-01427: single row subquery returns more than one row error while creating mview

Discussion in 'SQL PL/SQL' started by archie, Apr 22, 2014.

  1. archie

    archie Guest

    I have a materialized view to be created in my database. The source for the materialized view is another view in another database. I am accessing the view in source databse using a db link. My materialized view definition is pretty straightforward. Just selecting columns from the source view.

    DROP PUBLIC SYNONYM MVIEW;

    CREATE OR REPLACE PUBLIC SYNONYM MVIEW FOR MVIEW;

    DROP MATERIALIZED VIEW MVIEW;

    CREATE MATERIALIZED VIEW MVIEW

    TABLESPACE STGTAB

    PCTUSED 0

    PCTFREE 10

    INITRANS 2

    MAXTRANS 255

    STORAGE (

    INITIAL 1M

    NEXT 1M

    MINEXTENTS 1

    MAXEXTENTS UNLIMITED

    PCTINCREASE 0

    BUFFER_POOL DEFAULT

    )
    NOCACHE

    LOGGING

    NOCOMPRESS

    NOPARALLEL

    BUILD IMMEDIATE

    REFRESH FORCE ON DEMAND

    WITH PRIMARY KEY

    AS

    SELECT A,

    B,

    C,

    D,

    E,

    F,

    G,

    H,

    I,

    J,

    K,

    L,

    M
    FROM view@dblink

    WHERE GREATEST (CREATE_DATE, UPDATE_DATE) >= '07-APR-14';

    When I ran this, I got the below error.

    "SQL Error: ORA-01427: single row subquery returns more than one row."

    This error occurs only when I add a particular column E to the mview definition. If I run the script without E, then it creates mview successfully.

    I checked the source view definition, the value for E was retrieved through a sub query, but the case was same for columns A,B,C as well but those did not throw error.

    Kindly help. I am thoroughly confused why this error occurred only for E.

    When I executed only the select statement in the mview definition, it worked fine and I could see data. But the same query when run as the mview definition threw the above error. Is the error likely to be at the source database?

    Thanks in advance!
     
  2. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    Re: ORA-01427: single row subquery returns more than one row error while creating mvi

    Hi.
    Can show text of your view "view"?

    Check data from your view at double rows ... no in they problem ?

    Note : use select distinct ....