Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2011
    Posts
    3

    Question Unanswered: SQL Querying issue

    iam getting the below error while trying to run sql query

    Msg 512, Level 16, State 1, Line 1
    Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

    but if in the same below query if i put the parameter in upto 10 then its working fine......if the parameter is 11 or above then the query is giving the above error....pls help me


    SELECT substring(sc.container_id,0,14) container_id,
    imc.item_class,
    sh.ship_to, sh.shipment_id,
    cust.ADDRESS1,
    sh.carrier,
    sh.stop_sequence,
    sh.company,
    sh.order_type
    FROM (SELECT max (im.item_class) item_class
    FROM shipping_container sc WITH (NOLOCK), item im WITH (NOLOCK)
    WHERE sc.item = im.item
    AND sc.parent =
    (SELECT internal_container_num
    FROM shipping_container WITH (NOLOCK)
    WHERE sc.container_id = 'RP111027073'
    AND internal_shipment_line_num IS NULL)) imc,
    shipping_container sc WITH (NOLOCK),
    shipment_header sh WITH (NOLOCK),
    ( select customer, address1, company from dbo.CUSTOMER ) cust
    WHERE sc.container_id= 'RP111027073 '
    AND sc.internal_shipment_line_num IS NULL
    AND sh.internal_shipment_num = sc.internal_shipment_num
    and sh.ship_to = cust.customer
    and sh.company = cust.company

    Please help me as iam new even in this site

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    The problem appears to lie in the shipping_container table.
    Code:
    SELECT substring(sc.container_id,0,14) container_id
    ,  imc.item_class
    ,  sh.ship_to
    ,  sh.shipment_id
    ,  cust.ADDRESS1
    ,  sh.carrier
    ,  sh.stop_sequence
    ,  sh.company
    ,  sh.order_type
       FROM (SELECT max (im.item_class) item_class
          FROM shipping_container sc WITH (NOLOCK), item im WITH (NOLOCK)
          WHERE  sc.item = im.item
             AND sc.parent = (SELECT internal_container_num
                FROM shipping_container WITH (NOLOCK)
                WHERE  sc.container_id = 'RP111027073'
                   AND internal_shipment_line_num IS NULL)) imc
    ,           shipping_container sc WITH (NOLOCK),
       shipment_header sh WITH (NOLOCK),
       (select customer, address1, company
          from dbo.CUSTOMER ) cust
       WHERE sc.container_id= 'RP111027073 '
          AND sc.internal_shipment_line_num IS NULL
          AND sh.internal_shipment_num = sc.internal_shipment_num
          AND sh.ship_to = cust.customer
          AND sh.company = cust.company
    i-PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Dec 2011
    Posts
    3

    Error while running the query

    thanks for you're reply...but its same only with the below query also


    SELECT substring(sc.container_id,0,14) container_id
    , imc.item_class
    , sh.ship_to
    , sh.shipment_id
    , cust.ADDRESS1
    , sh.carrier
    , sh.stop_sequence
    , sh.company
    , sh.order_type
    FROM (SELECT max (im.item_class) item_class
    FROM shipping_container sc WITH (NOLOCK), item im WITH (NOLOCK)
    WHERE sc.item = im.item
    AND sc.parent = (SELECT internal_container_num
    FROM shipping_container WITH (NOLOCK)
    WHERE sc.container_id = 'RP111115238'
    AND internal_shipment_line_num IS NULL)) imc
    , shipping_container sc WITH (NOLOCK),
    shipment_header sh WITH (NOLOCK),
    (select customer, address1, company
    from dbo.CUSTOMER ) cust
    WHERE sc.container_id= 'RP111115238'
    AND sc.internal_shipment_line_num IS NULL
    AND sh.internal_shipment_num = sc.internal_shipment_num
    AND sh.ship_to = cust.customer
    AND sh.company = cust.company


    Can you please help me out

  4. #4
    Join Date
    Jun 2009
    Posts
    9

    use top 1 clause with inner query.

    SELECT
    substring(sc.container_id,0,14) container_id,
    imc.item_class,
    sh.ship_to, sh.shipment_id,
    cust.ADDRESS1,
    sh.carrier,
    sh.stop_sequence,
    sh.company,
    sh.order_type
    FROM
    (SELECT max (im.item_class) item_class
    FROM
    shipping_container sc WITH (NOLOCK),
    item im WITH (NOLOCK)
    WHERE
    sc.item = im.item
    AND sc.parent =(SELECT top 1 internal_container_num
    FROM
    shipping_container WITH (NOLOCK)
    WHERE
    sc.container_id = 'RP111027073'
    AND internal_shipment_line_num IS NULL))
    imc,
    shipping_container sc WITH (NOLOCK),
    shipment_header sh WITH (NOLOCK),
    ( select customer, address1, company from dbo.CUSTOMER ) cust
    WHERE
    sc.container_id = 'RP111027073 '
    AND sc.internal_shipment_line_num IS NULL
    AND sh.internal_shipment_num = sc.internal_shipment_num
    and sh.ship_to = cust.customer
    and sh.company = cust.company


    try this...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •