CODEWITHSUNDEEP
sqlsql-servert-sqlsubquery
fasih ullah khan
fasih ullah khan

Reputation: 47

subquery is returning multiple rows without operators

I have this SQL Query

SELECT LEFT(ORIGINALPATH, 34), count(originalpath) as docType1,
(SELECT count(originalpath)
FROM   docType2 
WHERE  ORIGINALPATH LIKE 'D:\era-server\16\ibosdata\00001\%' 
GROUP  BY LEFT(ORIGINALPATH, 34)) AS [dcoType2]
FROM   docType1
WHERE  ORIGINALPATH LIKE 'D:\era-server\16\ibosdata\00001\%' 
GROUP  BY LEFT(ORIGINALPATH, 34) 
ORDER  BY LEFT(ORIGINALPATH, 34);

But I am getting 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.

despite the fact that I am using no such operators. Please help.

Upvotes: 2

Views: 55

Answers (1)

Avi
Avi

Reputation: 1845

Can you remove inner group by and try it. Probably the group by might be creating multiple rows in subquery. Edited: added a condition to make sure the group by is as per same grouping element. 

SELECT LEFT(ORIGINALPATH, 34), count(originalpath) as docType1,
(SELECT count(originalpath)
FROM   docType2 de
WHERE  ORIGINALPATH LIKE 'D:\era-server\16\ibosdata\00001\%' 
and LEFT(de.ORIGINALPATH, 34) = LEFT(d1.ORIGINALPATH, 34)
GROUP  BY LEFT(ORIGINALPATH, 34)) AS [dcoType2]
FROM   docType1 d1
WHERE  ORIGINALPATH LIKE 'D:\era-server\16\ibosdata\00001\%' 
GROUP  BY LEFT(ORIGINALPATH, 34) 
ORDER  BY LEFT(ORIGINALPATH, 34);

Upvotes: 1

Related Questions