Subquery returned more than 1 value

Transact-SQL
1

Hi , Am trying to run this query (attached sample query) but I encounter an error. it says Msg 512, Level 16, State 1, Line 18
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. May i know what possible error with my script. any idea on how to fix this error. thanks.

select case when a.SOURCE = 5 then (select case when supplier = 'POL' then supplier when supplier <> 'POL' then supplier else ' No Supplier' end from #sample2 PB where PB.esn = a.series AND PB.id = 'POL' and PB.salesid = (select DISTINCT salesid from #sample3 WHERE cid = a.SOURCERECID ) ) end AS SUPPLIER from #sample a where a.sourcedataareaid = 'POL' and a.source=5 and dateadd(hour,8,a.createddatetime) >= @fromDateTime and dateadd(hour,8,a.createddatetime) <= @toDateTime

2

You can always add TOP(1) to your select

3

This means that... well, the subquery returned more than one value. :slight_smile:

In order to identify which value, you can run this query:

SELECT A.series, COUNT(*)
FROM #sample a
INNER JOIN #sample2 PB 
	ON PB.esn = a.series
INNER JOIN #sample3 S3 
	ON PB.salesid = S3.salesid
	AND S3.cid = a.SOURCERECID
WHERE a.sourcedataareaid = 'POL'
	AND a.source = 5
	AND dateadd(hour, 8, a.createddatetime) >= @fromDateTime
	AND dateadd(hour, 8, a.createddatetime) <= @toDateTime
	AND PB.id = 'POL'
GROUP BY A.series
HAVING COUNT(*) > 1

If it doesn't return duplicates, change the group by to A.series, a.SOURCERECID, S3.supplier

4

Thank you guys for the reply. I will try this in my actual data.