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

Question

'USE [SAPL_BK_NEW]
GO
/****** Object:  StoredProcedure [dbo].[MachineCTE]    Script Date: 11/12/2016 10:32:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--exec MachineCTE 'AQUARELL'

ALTER proc [dbo].[MachineCTE]
(
@companyName varchar(100)
)
AS
BEGIN

CREATE TABLE #MachineCTE
(
ID int,
mcdesp varchar(max),
mccost int, 
mcopsts varchar(30),
State varchar(100),
mcfact varchar(50),
mccomp varchar(60)
);

WITH Machine_CTE(ID,mcdesp,mccost, mcopsts,State,mcfact,mccomp)
AS (SELECT ROW_NUMBER() OVER(ORDER BY mcdesp ASC),
mac.mcdesp  as Machine,count(mac.mccost ) as Cost,count(mcopsts ) as Condition,mcopsts  as [State],mcfact,mccomp
FROM Machine mac    
where Rtrim(mac.mccomp)=@companyName  GROUP BY mcdesp ,mcopsts,mcfact,mccomp)

insert into #MachineCTE select ID,mcdesp,mccost, mcopsts,State,mcfact,mccomp from Machine_CTE




select * from (SELECT 
    CTE.mcdesp as Machines_Name,
    SUM(CTE.mccost) as Total_Machines,
    ISNULL((SELECT mcopsts FROM #MachineCTE MCTE WHERE State='GOOD' and MCTE.mcdesp  = CTE.mcdesp ),0) AS [GOOD],
    ISNULL((SELECT mcopsts  FROM #MachineCTE MCTE WHERE State='IDLE' and MCTE.mcdesp  = CTE.mcdesp ),0) AS [IDLE],
    ISNULL((SELECT mcopsts  FROM #MachineCTE MCTE WHERE State='UR' and MCTE.mcdesp  = CTE.mcdesp ),0) AS [UR] 
FROM #MachineCTE CTE GROUP BY mcdesp) t1 union all
select * from 
(SELECT 
    CTE.mcdesp as Machines_Name,
    '0' as Total_Machines,
    '0' AS [GOOD],
    '0' AS [IDLE],
    '0' AS [UR] 
FROM #MachineCTE CTE where mcfact='EXTRA' GROUP BY mcdesp,mccomp,mcopsts,mcfact)  t2  


drop table #MachineCTE


END'

please help to solve this issue

SELECT 
    CTE.mcdesp as Machines_Name,
    SUM(CTE.mccost) as Total_Machines,
    ISNULL((SELECT mcopsts FROM #MachineCTE MCTE WHERE State='GOOD' and MCTE.mcdesp  = CTE.mcdesp ),0) AS [GOOD],
    ISNULL((SELECT mcopsts  FROM #MachineCTE MCTE WHERE State='IDLE' and MCTE.mcdesp  = CTE.mcdesp ),0) AS [IDLE],
    ISNULL((SELECT mcopsts  FROM #MachineCTE MCTE WHERE State='UR' and MCTE.mcdesp  = CTE.mcdesp ),0) AS [UR] 
FROM #MachineCTE CTE GROUP BY mcdesp

the above statement is the issue

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

It *should* be self-explanatory, one of the subqueries (`SELECT mcopsts FROM #MachineCTE MCTE WHERE State='GOOD' and MCTE.mcdesp = CTE.mcdesp`) is returning more than one value. Easiest fix is to `SELECT TOP 1` with the subquery. — Der Kommissar, Nov 12 '16 at 05:17
To expand on what EBrown already said: if you have a subquery in your select clause, that subquery can return at most **one** row. Otherwise you will get the error you are getting. Either make sure the subquery (subqueries) return at most one row, or rewrite the query with joins to accomodate for the multiple rows returned. — TT., Nov 12 '16 at 06:37
Please read [this question](http://stackoverflow.com/q/2653188/243373) on SO and study the answers posted there. You should be able to work your way through this problem with the answers posted there. — TT., Nov 12 '16 at 06:44

score 0 · Accepted Answer · answered Nov 12 '16 at 15:47

The simplest method to avoid the problem is to include TOP 1 in the subqueries:

ISNULL((SELECT TOP 1 mcopsts FROM #MachineCTE MCTE WHERE State = 'GOOD' and MCTE.mcdesp  = CTE.mcdesp), 0) AS [GOOD],

However, that is a merely avoiding the real issue. Why do you expect only one row to be returned, if multiple may match? How do you want to combine values from multiple rows, if there are multiple matches?

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

1 Answers1