CODEWITHSUNDEEP
sqlsql-server
Kirat
Kirat

Reputation: 141

SQL query returning Subquery returned more than 1 value error

I have the following query. Please have a look on that first

select 
    X.ITEMCODE, X.[ITEM_DESCRIPTION], X.[OPENING_BALANCE],   
    X.[SALES], X.[TOTAL_REJECTS], x.[REJECTION_DISEASES]
    (X.[SALES] - X.[TOTAL_REJECTS]) as [PRODUCTION_TOTAL], 
    X. [DOCDATE],
    (X.[OPENING_BALANCE] + (X.[SALES]-X.[TOTAL_REJECTS])) AS [CLOSING_BALANCE]
from
    (SELECT DISTINCT 
         T0.ItemCode AS ITEMCODE, T0.ItemName AS [ITEM_DESCRIPTION],
         ISNULL((SELECT SUM(T1.OnHand)  
                 FROM OITW T1 
                 WHERE T1.WhsCode = 'FGS' 
                   AND T1.ItemCode = T0.ItemCode), 0) AS [OPENING_BALANCE],
         ISNULL((SELECT SUM(T1.[Quantity]) 
                 FROM INV1 T1 
                 WHERE T0.[ItemCode] = T1.[ItemCode]), 0) AS [SALES] , 
         (SELECT T1.[DocDate] FROM INV1 T1 
          WHERE T0.[ItemCode] = T1.[ItemCode]) AS [DOCDATE],
         ISNULL((SELECT SUM(cast(T1.[Quantity] as int))  
                 FROM IGE1 T1 
                 WHERE T0.[ItemCode] = T1.[ItemCode]), 0) AS [TOTAL_REJECTS],
         ISNULL((SELECT T1.[U_RejectionOthers]   
                 FROM IGE1 T1 
                 WHERE T0.[ItemCode] = T1.[ItemCode]), 0) AS [REJECTION_DISEASES]
    from 
        OITM T0 
    where 
        T0.[ItmsGrpCod] in ('104', '121', '122', '123', '124', '125', '126', '127', '129', '130', '134', '135', '136', '139', '140', '141', '142', '143', '144', '145')) X
WHERE 
    X.[OPENING_BALANCE] > 0 OR X.[TOTAL_REJECTS] > 0  OR X.[SALES] > 0

In this code when I tried to add X.[DOCDATE] and x.[REJECTION_DISEASES] in the main select statement then I get the following error

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

Please experts help me out.

Upvotes: 0

Views: 305

Answers (1)

Pரதீப்
Pரதீப்

Reputation: 93694

I suspect below sub-query returns more than one row for given condition in where clause

Isnull((SELECT T1.[u_rejectionothers] 
        FROM   ige1 T1 
        WHERE  T0.[itemcode] = T1.[itemcode]), 0) AS[REJECTION_DISEASES]

Either you need to fix the where clause to return one row or TOP plus Order by to return one row

Fixing where clause. Put the all the conditions in where clause through which you can identify a unique line in ige1 table

Isnull((SELECT T1.[u_rejectionothers] 
        FROM   ige1 T1 
        WHERE  T0.[itemcode] = T1.[itemcode]
          AND  T1.somecol  = 'XXX'), 0) AS[ REJECTION_DISEASES]

Adding TOP plus Order by in sub-query 

Isnull((SELECT TOP 1 T1.[u_rejectionothers] 
        FROM   ige1 T1 
        WHERE  T0.[itemcode] = T1.[itemcode]
        Order by somecol), 0) AS [REJECTION_DISEASES]

Upvotes: 2

Related Questions