Search code examples
sqldatabaseoracleoracle8i

Subquery return more than one value


I have the following query which gives gives req_no and order_no. order_no is a from a sub_query which you can see from below sql. For a few req_no there are more than one order_no's and because of that I get ORA-01427: single-row subquery returns more than one row.

I would like to display both the order_no for one req_no, how can I achieve this? Any help is highly appreciable.

Thanks

P.S. Our client's one database is still Oracle 8i.

SELECT  max_qst.req_no,
            (SELECT  DISTINCT max_odr.order_no
                FROM     maximo_orders max_odr,
                         maximo_order_revisions max_odv,
                         maximo_order_items max_odi,
                         maximo_order_dates max_odd,
                         maximo_requisition_order max_rqo,
                         maximo_requisition_details max_req
              WHERE       max_req.req_no = max_qst.req_no
                         AND max_req.req_yr = max_qst.req_yr
                         AND max_odr.order_no = max_odi.order_no
                         AND max_odi.order_item_id = max_odd.order_item_id
                         AND max_req.requisition_item_id = max_rqo.requisition_item_id
                         AND max_rqo.order_schedule_id = max_odd.order_schedule_id
                         AND max_odv.order_no = max_odi.order_no
                         AND max_odv.revision_no =
                                 (SELECT   MAX (max_alias.revision_no)
                                     FROM   maximo_order_revisions max_alias
                                    WHERE   max_alias.order_no = max_odv.order_no)
                         AND maximo_order_item (max_odi.order_no,
                                                                  max_odv.revision_no,
                                                                  max_odi.order_item_id
                                                                 ) = 'CONFIRMED'
                        )
  FROM  maximo_requisitions max_qst, maximo_requisition_details max_qsd
 WHERE       max_qst.qst_id = max_qsd.qst_id
            AND max_qst.enter_date = '2001'
            AND max_qst.req_no = 'PUR_12WX'

Update 1

Desired out put.

REQ_No     ORDER_NO

PUR_12WX   PR_9078  
PUR_12WX   PR_9079  

Solution

  • Use a join instead of a correlated sub-query.

    I've removed the max_qst references from the sub-query and moved them to the join predicate.

    I've also just changed it to use a LEFT JOIN. This allows for the possibility of there being no order_no values returned.

    SELECT
      max_qst.req_no,
      sub_query.order_no
    FROM
      maximo_requisitions          max_qst
    INNER JOIN
      maximo_requisition_details   max_qsd
        ON max_qst.qst_id = max_qsd.qst_id
    LEFT JOIN
    (
      SELECT DISTINCT
        max_odr.order_no,
        max_req.req_no,
        max_req.req_yr
      FROM
        maximo_orders              max_odr,
        maximo_order_revisions     max_odv,
        maximo_order_items         max_odi,
        maximo_order_dates         max_odd,
        maximo_requisition_order   max_rqo,
        maximo_requisition_details max_req
      WHERE
            max_odr.order_no            = max_odi.order_no
        AND max_odi.order_item_id       = max_odd.order_item_id
        AND max_req.requisition_item_id = max_rqo.requisition_item_id
        AND max_rqo.order_schedule_id   = max_odd.order_schedule_id
        AND max_odv.order_no            = max_odi.order_no
        AND max_odv.revision_no         = (SELECT MAX (max_alias.revision_no)
                                             FROM maximo_order_revisions max_alias
                                            WHERE max_alias.order_no = max_odv.order_no)
        AND maximo_order_item (max_odi.order_no,  max_odv.revision_no, max_odi.order_item_id) = 'CONFIRMED'
    )
      suq_query
        ON  max_qst.req_no = sub_query.req_no
        AND max_qst.req_yr = sub_query.req_yr
    WHERE
      max_qst.enter_date = '2001'
      max_qst.req_no = 'PUR_12WX'