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

    Smile Unanswered: Subquery returned more than 1 value

    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

  2. #2
    Join Date
    Apr 2008
    Location
    Iasi, Romania
    Posts
    561
    Provided Answers: 2
    1. your question isn't related to the original question of this thread
    2. the error message is self-explanatory: one of your sub-SELECT returns more than 1 row.
    Florin Aparaschivei
    DB2 9.7, 10.5 on Windows
    Iasi, Romania

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I split these posts off into their own thread, the original post was tacked onto an unrelated thread.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  4. #4
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Try this:
    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 shipping_container sc WITH (NOLOCK)
    	INNER JOIN shipment_header sh WITH (NOLOCK) ON
    		sc.internal_shipment_num = sh.internal_shipment_num
    	INNER JOIN dbo.CUSTOMER AS cust ON
    		sh.ship_to = cust.customer
    		and sh.company = cust.company
    	CROSS JOIN 
    		(SELECT max(im.item_class) item_class
    		FROM shipping_container sc WITH (NOLOCK)
    			INNER JOIN item im WITH (NOLOCK) ON
    				sc.item = im.item
    		WHERE sc.container_id = 'RP111027073'
    			AND internal_shipment_line_num IS NULL
    		) imc
    WHERE sc.container_id= 'RP111027073 '
    	AND sc.internal_shipment_line_num IS NULL
    The query could be simplified a great deal.
    Like ( select customer, address1, company from dbo.CUSTOMER ) cust
    I couldn't think of any reason why it was there.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

Posting Permissions

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