Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2016
    Posts
    3

    Unanswered: Subquery returned more than 1 value

    Can someone look at this code, I can't find why this is returning error(Subquery returned more than 1 value)
    any help is appreciated.

    SELECT *,0 as TotalProd , 0 as TotalRMN
    INTO #VBPRODFINAL
    FROM
    (SELECT * FROM (SELECT pre_price +
    CASE
    WHEN pre_price_ad IS NOT NULL THEN '/$' + pre_price_ad
    ELSE ''
    END AS PRE_PRICE
    ,PP.line_number
    ,Isnull(cnt, 0) AS CNT
    ,part_code PART_CODE
    ,cut
    ,Sum(make_cases) AS MAKE_CASES
    ,brand
    ,prod_type
    ,priority
    ,lot_number
    ,first_sort
    ,part_done
    ,CASE
    WHEN RIGHT(lot_number, 3) <> @OLD THEN 'OLD'
    ELSE ''
    END AS OTHER_LOT
    ,expected_time
    ,sort_part
    ,tray_count
    FROM plant_make_plan PP
    INNER JOIN @DONE DD ON PP.part_code = DD.part_done
    INNER JOIN #VBPROD ON productid = PP.part_code
    AND lotnumber = PP.lot_number
    AND PP.line_number = line_num
    WHERE PP.make_date = Cast(Getdate() AS DATE)
    --AND pp.LINE_NUMBER = @LINE
    GROUP BY PP.pre_price,
    PP.pre_price_ad,
    PP.line_number,
    PP.build_through,
    PP.part_code,
    PP.cut,
    PP.brand,
    PP.prod_type,
    PP.priority,
    PP.lot_number,
    PP.first_sort,
    PP.sort_part,
    part_done,
    PP.expected_time,
    PP.tray_count,
    cnt,
    line_num) as FINAL
    WHERE make_cases > 0
    -- ORDER BY line_number,
    -- first_sort,
    --BRAND,
    -- sort_part
    ) as FProd
    UPDATE #VBPRODFINAL
    SET TotalProd = (SELECT SUM(t.CNT) OVER (PARTITION BY ProductId) as TOTCNT FROM #VBPROD u
    INNER JOIN #VBPRODFINAL t on u.ProductID = t.PART_CODE
    WHERE u.ProductID = t.PART_CODE and t.LOT_NUMBER = u.LotNumber)

  2. #2
    Join Date
    Jul 2016
    Posts
    3

    Subquery returned more than 1 value

    I am using 2012 and I keep getting this error, any help would be greatly appreciated.

    Code:
    IF OBJECT_ID('tempdb..#VBPROD') IS NOT NULL
        DROP TABLE #VBPROD
    	IF OBJECT_ID('tempdb..#VBPRODFINAL') IS NOT NULL
        DROP TABLE #VBPRODFINAL
     DECLARE @BRAND VARCHAR(20)
       DECLARE @LINE VARCHAR(10)
       IF (SELECT COUNT(1) FROM dbo.PLANT_MAKE_PLAN WHERE MAKE_DATE = CAST(GETDATE() AS DATE)) = 0
          BEGIN
          EXEC dbo.spMAKE_PLAN
          END 
          
          DECLARE @OLD AS INT
          SET @OLD = (SELECT MAX(RIGHT(LOT_NUMBER,3)) FROM dbo.PLANT_MAKE_PLAN WHERE MAKE_DATE = CAST(GETDATE() AS DATE))
                
          DECLARE @DONE AS TABLE (PART_DONE VARCHAR(10),VB NVARCHAR(5))
          INSERT INTO @DONE
          SELECT DISTINCT PART_CODE,VB FROM MBA_COMPANY_INFORMATION.dbo.DAILY_MAKE_TIMED_SHORT
          WHERE PRODUCTION_DIFF - VB <= 0
          
    	      
    	SELECT ProductID,LEFT(RIGHT([Barcode1],13),1) as LINE_NUM,LotNumber,COUNT(1) as cnt 
    	INTO #VBPROD										  
    	FROM vb.DBO.vbsBoxes 
    	WHERE CONVERT(DATE,CreateDate) = CONVERT(Date,GETDATE())
    	Group By LEFT(RIGHT([Barcode1],13),1),ProductID,LotNumber
    		--SELECT * FROM #VBPROD
    EXEC dbo.spPLANT_PLAN_UPDATE_NEEDED
          
        
          SELECT *,0 as TotalProd , 0 as TotalRMN
    	  INTO #VBPRODFINAL
    	  FROM
          (SELECT * FROM   (SELECT pre_price + 
    	                 CASE 
    					   WHEN pre_price_ad IS NOT NULL THEN '/$' + pre_price_ad 
                                ELSE '' 
                         END AS PRE_PRICE
    					,PP.line_number
    					,Isnull(cnt, 0) AS CNT
    					,part_code PART_CODE
    					,cut
    					,Sum(make_cases) AS MAKE_CASES
    					,brand
    					,prod_type
    					,priority
    					,lot_number
    					,first_sort
    					,part_done
    					,CASE 
                            WHEN RIGHT(lot_number, 3) <> @OLD THEN 'OLD' 
                              ELSE '' 
                         END AS OTHER_LOT
    					,expected_time
    					,sort_part
    					,tray_count 
                  FROM   plant_make_plan PP 
    			  INNER JOIN @DONE DD ON PP.part_code = DD.part_done 
                  INNER JOIN #VBPROD ON productid = PP.part_code 
    			            AND lotnumber = PP.lot_number 
    			            AND PP.line_number = line_num 
                  WHERE  PP.make_date = Cast(Getdate() AS DATE) 
    			         --AND  pp.LINE_NUMBER = @LINE
                  GROUP  BY PP.pre_price, 
                            PP.pre_price_ad, 
                            PP.line_number, 
                            PP.build_through, 
                            PP.part_code, 
                            PP.cut, 
                            PP.brand, 
                            PP.prod_type, 
                            PP.priority, 
                            PP.lot_number, 
                            PP.first_sort, 
                            PP.sort_part, 
                            part_done, 
                            PP.expected_time, 
                            PP.tray_count, 
                            cnt, 
                            line_num) as FINAL 
          WHERE  make_cases > 0 
        --  ORDER  BY line_number, 
        --            first_sort,
    				--BRAND, 
        --            sort_part
    				          ) as FProd
    	UPDATE  #VBPRODFINAL 
    	SET TotalProd =  (SELECT SUM(t.CNT) OVER (PARTITION BY ProductId) as TOTCNT FROM #VBPROD u 
    	INNER JOIN #VBPRODFINAL t on u.ProductID = t.PART_CODE
    	WHERE u.ProductID = t.PART_CODE and t.LOT_NUMBER = u.LotNumber)

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    You have multiple sub-select statements. One or more of them are returning more than one row in their result set, which is forbidden by both SQL Server and Relational Algebra.

    There are many ways to resolve this problem, but only one of those ways produces the answer that you want. You haven't given enough information for me to determine which of the possible solutions is the one that you want.

    For the time being, simplify your query by removing the extraneous parts and simplifying it as much as possible. After you do that, try to cut down the complexity of the queries if you can. If you haven't found the problem at that point, then change the sub-select values to return a Count(*) which should help you resolve the question pretty quickly.

    After you get the inner SELECT working properly, build it back into the UPDATE statement.

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

  4. #4
    Join Date
    Jul 2016
    Posts
    3

    Sub Select

    Ok what I am trying to do is get the production information for each line, then insert it into the #VBPROD table I join on it to get the counts to Sum those across all lines to get a total for all lines. I am also attaching excel to show results of a few of the expected results for TotalProd and TotalRMNClick image for larger version. 

Name:	Totals.jpg 
Views:	1 
Size:	134.6 KB 
ID:	17065


    Code:
    SELECT 'SELECT '
          + QUOTENAME(PRE_PRICE,'''')+','
          + QUOTENAME(LINE_NUMBER,'''')+','
          + QUOTENAME(CNT,'''')+','
          + QUOTENAME(CUT,'''')+','
    	  + QUOTENAME(MAKE_CASES,'''')+','
    	  + QUOTENAME(BRAND,'''')+','
    	  + QUOTENAME(PROD_TYPE,'''')+','
    	  + QUOTENAME(PRIORITY,'''')+','
    	  + QUOTENAME(LOT_NUMBER,'''')+','
    	  + QUOTENAME(FIRST_SORT,'''')+','
    	  + QUOTENAME(PART_DONE,'''')+','
    	  + QUOTENAME(OTHER_LOT,'''')+','
    	  + QUOTENAME(EXPECTED_TIME,'''')+','
    	  + QUOTENAME(SORT_PART,'''')+','
    	  + QUOTENAME(TRAY_COUNT,'''')+','
    	  + QUOTENAME(TotalProd,'''')+','
          + QUOTENAME(TotalRMN,'''')
          + ' UNION ALL'
    FROM #VBPRODFINAL
    
    
    
    INSERT INTO #VBPRODFINAL 
           ( PRE_PRICE,LINE_NUMBER,CNT,CUT,MAKE_CASES,BRAND,PROD_TYPE,PRIORITY,LOT_NUMBER,FIRST_SORT,PART_DONE,OTHER_LOT,EXPECTED_TIME,SORT_PART,TRAY_COUNT,TotalProd,TotalRMN)
    SELECT '','1','297','Thighs','297','ORG','SP','','1116209','2','1409','','','4','1782','0','0' UNION ALL
    SELECT '','1','38','Leg Quarters','38','ORG','SP','','1116209','2','1469','','','3','228','0','0' UNION ALL
    SELECT '','1','2','Leg Quarters','2','ORG','SP','','1116209','2','5469','','','2','12','0','0' UNION ALL
    SELECT '','2','74','B/S Thighs','74','ORG','SP','','1116209','1','4465','','','3','444','0','0' UNION ALL
    SELECT '','2','11','B/S Thighs','11','ORG','SP','','1116209','1','5465','','','5','66','0','0' UNION ALL
    SELECT '','2','236','B/S Thighs','286','ORG','SP','','1116209','1','1465','','','6','1716','0','0' UNION ALL
    SELECT '','3','85','Drumsticks','84','ORG','SP','','1116209','1','1408','','','5','504','0','0' UNION ALL
    SELECT '','3','25','Drumsticks','24','ORG','SP','','1116209','1','4408','','','3','144','0','0' UNION ALL
    SELECT '','3','5','Thighs','5','ORG','SP','','1116209','2','5409','','','6','30','0','0' UNION ALL
    SELECT '','4','99','Wings','99','ORG','SP','','1116209','1','1410','','','5','594','0','0' UNION ALL
    SELECT '','4','1','Wings','1','ORG','SP','','1116209','1','5410','','','4','6','0','0' UNION ALL
    SELECT '','5','2','Split Breast','2','ORG','SP','','1116209','3','1403','','','9','12','0','0' UNION ALL
    SELECT '','5','10','Cut-up','10','ORG','FP','','1116209','1','1404','','','1','40','0','0' UNION ALL
    SELECT '','5','1','Cut-up','1','ORG','FP','','1116209','1','14048','','','2','4','0','0' UNION ALL
    SELECT '','5','97','Split Breast','97','ORG','SP','','1116209','2','1406','','','8','582','0','0' UNION ALL
    SELECT '','5','79','Drumsticks','80','ORG','SP','','1116209','1','1408','','','10','480','0','0' UNION ALL
    SELECT '','5','1','Split Breast','1','ORG','SP','','1116209','2','5406','','','7','6','0','0' UNION ALL
    SELECT '','6','23','B/S Breast','182','ORG','SP','','1116209','1','1427','','','7','2184','0','0' UNION ALL
    SELECT '','6','87','B/S Breast','200','ORG','SP','','1116209','1','1429','','','2','2400','0','0' UNION ALL
    SELECT '','6','24','B/S Breast','24','ORG','SP','','1116209','1','4427','','','6','288','0','0' UNION ALL
    SELECT '','6','44','B/S Breast','44','ORG','SP','','1116209','1','4429','','','1','528','0','0' UNION ALL
    SELECT '','6','161','B/S Thighs','50','ORG','SP','','1116209','1','1465','','','8','300','0','0' UNION ALL
    SELECT '','8','384','B/S Breast','290','ORG','SP','','1116209','1','1429','','','4','3480','0','0' UNION ALL
    SELECT '','8','17','B/S Breast','17','ORG','SP','','1116209','1','5429','','','3','204','0','0' UNION ALL
    SELECT '','9','137','Tenders','137','ORG','SP','','1116209','1','1431','','','4','1644','0','0' UNION ALL
    SELECT '','9','13','B/S Thighs','100','ORG','SP','','1116209','1','1465','','','5','600','0','0' UNION ALL
    SELECT '$10.99','6','30','B/S Breast','30','ORG','SP','','1116209','1','2427','','','3','360','0','0' UNION ALL
    SELECT '$11.49','9','8','Tenders','8','ORG','SP','','1116209','1','54311','','','3','96','0','0' UNION ALL
    SELECT '$11.79','6','5','B/S Breast','5','ORG','SP','','1116209','1','54271','','','5','60','0','0' UNION ALL
    SELECT '$11.99','6','35','B/S Breast','35','ORG','SP','','1116209','1','14277','','','4','210','0','0' UNION ALL
    SELECT '$11.99','9','45','Tenders','45','ORG','SP','','1116209','1','14317','','','2','540','0','0' UNION ALL
    SELECT '$3.79','3','25','Drumsticks','25','ORG','SP','','1116209','1','2408','','','1','150','0','0' UNION ALL
    SELECT '$3.79','3','40','Thighs','40','ORG','SP','','1116209','2','2409','','','8','240','0','0' UNION ALL
    SELECT '$3.99','1','296','Thighs','300','ORG','SP','','1116209','2','14097','','','5','1800','0','0' UNION ALL
    SELECT '$3.99','3','96','Drumsticks','90','ORG','SP','','1116209','1','14087','','','2','540','0','0' UNION ALL
    SELECT '$3.99','3','259','Thighs','255','ORG','SP','','1116209','2','14097','','','9','1530','0','0' UNION ALL
    SELECT '$3.99','4','191','Wings','190','ORG','SP','','1116209','1','14107','','','2','1140','0','0' UNION ALL
    SELECT '$3.99','5','80','Cut-up','80','ORG','FP','','1116209','1','14047','','','4','320','0','0' UNION ALL
    SELECT '$4.39','1','5','Leg Quarters','5','ORG','SP','','1116209','2','54691','','','1','30','0','0' UNION ALL
    SELECT '$4.39','4','35','Wings','35','ORG','SP','','1116209','1','2410','','','1','210','0','0' UNION ALL
    SELECT '$4.49','3','14','Drumsticks','14','ORG','SP','','1116209','1','54081','','','4','84','0','0' UNION ALL
    SELECT '$4.49','3','17','Thighs','16','ORG','SP','','1116209','2','54091','','','7','96','0','0' UNION ALL
    SELECT '$4.69','4','10','Wings','10','ORG','SP','','1116209','1','54101','','','3','60','0','0' UNION ALL
    SELECT '$5.59','5','3','Cut-up','3','ORG','FP','','1116209','1','14041','','','3','12','0','0' UNION ALL
    SELECT '$6.99','2','50','B/S Thighs','50','ORG','SP','','1116209','1','2465','','','1','300','0','0' UNION ALL
    SELECT '$6.99','5','35','Split Breast','35','ORG','SP','','1116209','2','2406','','','5','210','0','0' UNION ALL
    SELECT '$7.49','2','14','B/S Thighs','14','ORG','SP','','1116209','1','54651','','','4','84','0','0' UNION ALL
    SELECT '$7.69','5','7','Split Breast','7','ORG','SP','','1116209','2','54061','','','6','42','0','0' UNION ALL
    SELECT '$7.99','2','148','B/S Thighs','148','ORG','SP','','1116209','1','14657','','','2','888','0','0' UNION ALL
    SELECT '$9.99','8','28','B/S Breast','28','ORG','SP','','1116209','1','54291','','','2','336','0','0' UNION ALL
    SELECT '$9.99/$8.99','8','125','B/S Breast','125','ORG','SP','','1116209','1','2429','','','1','1500','0','0' UNION ALL
    SELECT '$9.99/$8.99','9','60','Tenders','60','ORG','SP','','1116209','1','2431','','','1','720','0','0' UNION ALL

Posting Permissions

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