1 Reply Latest reply: Mar 23, 2013 12:55 PM by Frank Kulash RSS

    single row subquery Returning more than one row

    998634
      Hi,

      This is urgent.............

      could u any one please suggest me on this query it returning single row subquey returnin more than one row

      In this 2nd suquery fails some of the order_numbers returning more than price_list based on dates and in that situvation also my query has to successfull

      fro that i added this condition l.ordered_quantity between trunc(product_attr_value_from) and (product_attr_value_to) for that condition also failing this query

      pls give me the correct condition to approach this solution



      select
      substr(party.party_name,1,13),
      c.account_number,
      substr(h.order_number||'-'||l.line_number||'-'||l.shipment_number,1,15),
      q.name,l.unit_selling_price,
      (select qll.operand
      apps.qp_list_headers qlh,
      apps.qp_list_lines qll,
      apps.qp_list_lines qllx
      apps.qp_pricing_attributes qpa,
      apps.qp_rltd_modifiers qrm,
      apps.mtl_system_items msi
      wherer qll.list_header_id=qlh.list_header_id
      and qpa.list_header_id=qlh.list_header_id
      and qpa.list_line_id=qll.list_line_id
      and qrm.rltd_modifier_grp_type='PRICE BREAK'
      and qll.list_line_id=qrm.to_rltd_modifier_id
      and qll.list_line_type_code='PLL'
      and qllx.list_header_id=qlh.lsit_header_id
      and qllx.list_line_id=qrm.from_rltd_modifier_id
      and qllx.list_line_type_code='PBH'
      and qlh.list_header_id=l.price_list_id
      and qpa.product_attr_value=msi.inventory_item_id
      and qpa.product_attr_value=l.inventory_item_id
      and msi.segment2 is null
      and msi.organization_id=89
      and trunc(l.request_date) between trunc(qllx.start_date_active) and trunc(nvl(qllx.end_date_active,sysdate))
      and l_ordered_quantity between qpa.pricing_attr_value_from and qpa.pricing_attr_value_to
      and qpa.product_uom_code=l.pricing_quantity_uom),
      substr(l.ordered_item,1,13),
      u.user_name,
      t.name,
      l.ordered_quantity,
      l.creation_date,
      l.request_date,
      l.pricing_date,
      l.flow_status_code
      from apps.oe_order_headers_all h,
      hz_cust_Accounts c,
      hz_parties party,
      apps.oe_order_lines_all l,
      apps.qp_list_headers_tl q,
      apps.qp_list_headers_b qb,
      apps.fnd_user u,
      apps.so_order_types_all t
      where h.header_id=l.header_id
      and h.sold_to_org_id=c.cust_account_id
      and c.party_id=party.party_id
      and trunc(l.request_date)!=trunc(l.pricing_date)
      and qb.attribute1='Q'
      and q.list_header_id=qb.list_header_id
      and l.price_list_id=q.list_header_id
      and l.flow_status_code=!='CANCELLED'
      AND t.name!='ECOM'
      and t.name!='RMA'
      and l.open_flag='Y'
      and l.item_type_code='MODEL'
      and u.user_id=l.created_by
      and t.order_type_id=h.order_type_id
      and u.user_name='XXCONVERSION'
      and (l.unit_selling_price -
      (select qll.operand
      from apps.qp_list_headers qlh,
      apps.qp_lsit_lines qll,
      apps.qp_list_lines qllx,
      apps.qp_pricing_attributes qpa,
      apps.qp_rltd_modifiers qrm,
      apps.mtl_system_items msi
      where qll.list_header_id=qlh.list_header_id
      and qpa.list_header_id=qlh.lsit_header_id
      and qpa.list_line_id=qll.list_line_id
      and qrm.rltd_modifier_grp_type='PRICE BREAK'
      AND qll.list_line_id=qrm.to_rltd_modifier_id
      and qll.list_line_type_code='PLL'
      and qllx.list_header_id=qlh.list_header_id
      and qllx.list_line_id=qrm.from_rltd_modifier_id
      and qllx.list_line_type_code='PBH'
      and qlh.list_header_id=l.price_list_id
      and qpa.product_attr_value=msi.inventory_item_id
      and qpa.product_Attr_value=l.inventory_item_id
      and msi.segmetn2 is null
      and msi.organization_id=89
      trunc(l.request_date) between trunc(qllx.start_date_active) and trunc(nvl(qllx.end_date_active,sysdate)) and
      and l.ordered_quantity between qpa.pricing_attr_value_from and qpa.pricing_attr_value_to
      and qpa.product_uom_code=l.pricing_quantity_uom)!=0)
      order by creation_date
        • 1. Re: single row subquery Returning more than one row
          Frank Kulash
          Hi,
          995631 wrote:
          Hi,

          This is urgent.............
          Then don't use this forum.
          could u any one please suggest me on this query it returning single row subquey returnin more than one row

          In this 2nd suquery fails some of the order_numbers returning more than price_list based on dates and in that situvation also my query has to successfull
          Remember, none of the people who want to help you are as familiar with your application as you are, so you have to explain things that are obvious to you.
          What does "sucessful" mean in this case? Does is mean producing the right results? What are the right results? How can you tell them apart from the wrong results?

          As the errror message says, the (immediate) problem is that a single-row sub-query is returning more than a single row. If the sub-query tries to bring back more than 1 row, which one do you want to use? Or do you want to use some kind of aggregate, such as the average, which might not be the same as any of them?

          Whenever you have a problem, post a little sample data (CREATE TABLE and INSERT statements, relevant columns only) for all the tables involved, and the results you want from that data.
          Explain, using specific examples, how you get those results from that data.
          Simplify the problem as much as possible. For example, remove all columns and tables that have nothing to do with the part you don't know how to do.
          Always say what version of Oracle you're using (e.g. 11.2.0.2.0).
          See the forum FAQ {message:id=9360002}