#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2012
    Posts
    2
    Rep Power
    0

    Question ORA-01427: single-row subquery returns more than one row


    I am returning the ORA-01427 after running the query below. Can someone help me understand why I am returning the error and how to address it. Thank you.

    select b.value , b.name, p.value ......
    (case when p.value <> 'G2' then null else (select c.oldvalue from ad_changelog c
    where c.record_id = b.c_bpartner_id and c.ad_table_id = 291
    and c.ad_column_id = 4216 ) end) as oldtradeName
    from c_bpartner b, zz_receipt r, zz_recp_alloc a, m_product p, ad_user us
    where a.c_bpartner_id = b.c_bpartner_id
    and a.zz_receipt_id = r.zz_receipt_id
    and us.ad_user_id = r.createdby
    and p.m_product_id = a.m_product_id
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2003
    Location
    Paris Uppland
    Posts
    2,385
    Rep Power
    391
    The problem is the sub-select in the case expression.

    Code:
    case when p.value <> 'G2' then null else (select c.oldvalue from ad_changelog c
    where c.record_id = b.c_bpartner_id and c.ad_table_id = 291
    and c.ad_column_id = 4216 ) end
    This select can in some cases return more than one row, so you need to decide which of these multiple values that should be returned. For instance you could add an aggregate function, e.g.

    Code:
    select max(c.oldvalue) from ad_changelog c
    where c.record_id = b.c_bpartner_id and c.ad_table_id = 291
    and c.ad_column_id = 4216 )
    which would guarantee that only one value is returned.
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2012
    Posts
    2
    Rep Power
    0

    Thumbs up Thank you


    Thank you very much. I have implemented the suggestion and it worked fine.


    Originally Posted by swampBoogie
    The problem is the sub-select in the case expression.

    Code:
    case when p.value <> 'G2' then null else (select c.oldvalue from ad_changelog c
    where c.record_id = b.c_bpartner_id and c.ad_table_id = 291
    and c.ad_column_id = 4216 ) end
    This select can in some cases return more than one row, so you need to decide which of these multiple values that should be returned. For instance you could add an aggregate function, e.g.

    Code:
    select max(c.oldvalue) from ad_changelog c
    where c.record_id = b.c_bpartner_id and c.ad_table_id = 291
    and c.ad_column_id = 4216 )
    which would guarantee that only one value is returned.
  6. #4
  7. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2014
    Posts
    1
    Rep Power
    0

    Question


    I have this query and I have this error
    QUERYUPDATE AD_PROCESS_PARA_TRL trl
    SET Name = (SELECT et.Name
    FROM AD_ELEMENT_TRL et, AD_ELEMENT e, AD_PROCESS_PARA f
    WHERE et.AD_LANGUAGE=trl.AD_LANGUAGE
    AND et.AD_Element_ID=e.AD_Element_ID
    AND e.ColumnName=f.ColumnName
    AND f.AD_Process_Para_ID=trl.AD_Process_Para_ID),
    Description = (SELECT et.Description
    FROM AD_ELEMENT_TRL et, AD_ELEMENT e, AD_PROCESS_PARA f
    WHERE et.AD_LANGUAGE=trl.AD_LANGUAGE
    AND et.AD_Element_ID=e.AD_Element_ID
    AND e.ColumnName=f.ColumnName
    AND f.AD_Process_Para_ID=trl.AD_Process_Para_ID)


    and this is the error



    Error starting at line : 1 in command -
    UPDATE AD_PROCESS_PARA_TRL trl
    SET Name = (SELECT et.Name
    FROM AD_ELEMENT_TRL et, AD_ELEMENT e, AD_PROCESS_PARA f
    WHERE et.AD_LANGUAGE=trl.AD_LANGUAGE
    AND et.AD_Element_ID=e.AD_Element_ID
    AND e.ColumnName=f.ColumnName
    AND f.AD_Process_Para_ID=trl.AD_Process_Para_ID),
    Description = (SELECT et.Description
    FROM AD_ELEMENT_TRL et, AD_ELEMENT e, AD_PROCESS_PARA f
    WHERE et.AD_LANGUAGE=trl.AD_LANGUAGE
    AND et.AD_Element_ID=e.AD_Element_ID
    AND e.ColumnName=f.ColumnName
    AND f.AD_Process_Para_ID=trl.AD_Process_Para_ID)
    -- ,Help = (SELECT et.Help
    -- FROM AD_ELEMENT_TRL et, AD_ELEMENT e, AD_PROCESS_PARA f
    -- WHERE et.AD_LANGUAGE=trl.AD_LANGUAGE
    -- AND et.AD_Element_ID=e.AD_Element_ID
    -- AND e.ColumnName=f.ColumnName
    -- AND f.AD_Process_Para_ID=trl.AD_Process_Para_ID)
    -- ,IsTranslated = (SELECT et.IsTranslated
    -- FROM AD_ELEMENT_TRL et, AD_ELEMENT e, AD_PROCESS_PARA f
    -- WHERE et.AD_LANGUAGE=trl.AD_LANGUAGE
    -- AND et.AD_Element_ID=e.AD_Element_ID
    -- AND e.ColumnName=f.ColumnName
    -- AND f.AD_Process_Para_ID=trl.AD_Process_Para_ID)
    -- ,Updated = SYSDATE WHERE EXISTS (SELECT 1 FROM AD_ELEMENT_TRL et, AD_ELEMENT e, AD_PROCESS_PARA f
    -- WHERE et.AD_LANGUAGE=trl.AD_LANGUAGE
    -- AND et.AD_Element_ID=e.AD_Element_ID
    -- AND e.ColumnName=f.ColumnName
    -- AND f.AD_Process_Para_ID=trl.AD_Process_Para_ID
    -- AND f.IsCentrallyMaintained='Y' AND f.IsActive='Y'
    -- AND (trl.Name <> et.Name OR NVL(trl.Description,' ') <> NVL(et.Description,' ') OR NVL(trl.Help,' ') <> NVL(et.Help,' ')))
    Error report -
    SQL Error: ORA-01427: single-row subquery returns more than one row
  8. #5
  9. No Profile Picture
    Senior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2003
    Location
    Canada
    Posts
    308
    Rep Power
    12
    Run each sub-query separately to figure out which one is returning more than one row then fix it - create proper joining between tables as we do not know the tables definitaion you are using.
  10. #6
  11. Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Sep 2006
    Posts
    859
    Rep Power
    388

    Thumbs down


    Also:
    1) DO NOT hijack another's post
    2) Use the "code" tags to format your code.

IMN logo majestic logo threadwatch logo seochat tools logo