Getting the following error ORA-01427: single-row subquery returns more than one row 01427. 00000 - "single-row subquery returns more than one row"

Question

I am getting the following error:

I want to get a column value from the first query and pass that value to the next query.

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

with the following query:

 with

deal_XX AS(

SELECT distinct idh.vendor supplier
                  ,idh.deal_id
                  ,mff_report.mff_merch_sql.get_sup_name(idh.vendor) sup_name


              FROM im_doc_head idh
                  ,mff_report.stage_complex_deal_head_hist scdhh
                  ,im_complex_deal_detail icdd
                  ,mff_report.v_loc vl
                  ,item_master im
                                  ,item_master im_parent
             WHERE (   (    idh.type IN ('DEBMEC','CRDMEC') --Debit and Credit Memos in APPROVED or POSTED
                        AND idh.status IN ('APPRVE','POSTED'))
                    OR (    idh.type = 'CRDNRC'             --Credit Note Requests in APPROVED or MATCHED
                        AND idh.status IN ('APPRVE','MTCH')))
               AND idh.deal_type = 'C'
               AND NVL(:PM_supplier,idh.vendor) = idh.vendor

               AND idh.deal_id = scdhh.deal_id (+)
               AND SUBSTR(idh.ext_doc_id,(INSTR(idh.ext_doc_id,'-',1) + 1),INSTR(idh.ext_doc_id,'-',1,2) - (INSTR(idh.ext_doc_id,'-',1) + 1)) = scdhh.deal_detail_id (+)
               AND idh.doc_date = scdhh.end_invoice_date (+)
               AND idh.doc_id = icdd.doc_id
               AND icdd.location = vl.loc
               AND icdd.item = im.item
                               AND im.item_parent = im_parent.item (+)
               AND (   :PM_supplier IS NOT NULL
                    OR :PM_doc_date_from IS NOT NULL
                    OR :PM_doc_date_to IS NOT NULL
                    OR :PM_approval_date_from IS NOT NULL
                    OR :PM_approval_date_to IS NOT NULL
                    OR :PM_ext_doc_id IS NOT NULL
                    OR :PM_batch_mode = 'Y')
                    )  

  select distinct  ship.order_no    , (select deal_id from deal_XX) hhhh
  from ordloc_discount od
     ,shipment ship
     ,mff_report.stage_complex_deal_head_hist scdhh  
    where od.deal_ID = (select deal_id from deal_XX) 

    and od.deal_id = scdhh.deal_id 
    and ship.status_code = 'R'
    and od.order_no = ship.order_no
    and ship.receive_date BETWEEN
        to_date(scdhh.start_invoice_date , 'YYYY-MM-DD" "HH24:MI:SS')
         AND
         to_date(scdhh.end_invoice_date , 'YYYY-MM-DD" "HH24:MI:SS') ;

Well, how many rows does select deal_id from deal_XX return when run on its own? Don’t you want to join to that CTE, rather than use subqueries? — Alex Poole

Radim Bača Radim Bača · Accepted Answer · 2017-12-15T22:22:25

If you run Q1:select deal_id from deal_XX then you probably get more than one row. Therefore, there are two issues:

Having select *, (subselect) from tableX then the subselect has to return always just one value for each row of the tableX. Since you have Q1 as a subselect, your query fails.
The same issue is when you have select * from tableX X where X.y = (subselect). Again, query processor expects you to provide a subselect returning just one value for each row of TableX and you have Q1 returning many rows.

The solutions depend on what you are trying to achieve. The second issue could be possibly fixed using od.deal_ID IN (select deal_id from deal_XX) meaning that you want rows where deal_id can be found in the list of deal_ids returned by deal_XX.

Getting the following error ORA-01427: single-row subquery returns more than one row 01427. 00000 - "single-row subquery returns more than one row"

1 Answers