Which subquery returns more than one value? It's hard to find out for me-CodePudding

select 
    T1.ItemCode, 
    T1.ItemName, 
    T1.U_PKMD,
    (select sum( T2.OnHand) AS 'QTY' 
     from OITW T2 
     join OITM T3 on T2.ItemCode = T3.ItemCode  
     where T3.[ItemCode] = T1.[ItemCode]   
       and WhsCode in ('KHI-04', 'KHI-12', 'RWP-01', 'LHR-01')) 
    - (select Quantity 
       from drf1  drf 
       inner join OITM itm on drf.[ItemCode] = itm.[ItemCode]
       where drf.[ItemCode] = T1.[ItemCode] 
from 
    OITW T0  
left join
    OITM T1 on T0.[ItemCode] = T1.[ItemCode] 
where 
    WhsCode in ('KHI-04', 'KHI12', 'LHR-01', 'RWP-O1') 
    and T1.OnHand is not null
    and ItmsGrpCod in ('101', '116')
    and ItemName not like '%Tool%' 
    and T1.OnHand != 0 
order by 
    T1.ItemCode

I get this error:

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

CodePudding user response：

The subquery that returns more than one value is this one:

(select Quantity 
 from drf1  drf 
 inner join OITM itm 
 on drf.[ItemCode] = itm.[ItemCode]
 where drf.[ItemCode] = T1.[ItemCode])

as the other one returns a sum, and therefore one value:

(select sum( T2.OnHand) AS 'QTY' 
 from OITW T2 
 join OITM T3 
 on T2.ItemCode = T3.ItemCode  
 where T3.[ItemCode] = T1.[ItemCode]   
   and WhsCode in ('KHI-04','KHI-12','RWP-01','LHR-01'))

CodePudding user response：

After reformatting, looking at the individual queries, it is in your SUBTRACTION query. Notice it is MINUS the select of all QUANTITY records from it. THAT is your culprit.

Should that only return 1 record, or the SUM( Quantity )