CODEWITHSUNDEEP
subquery
jgiven
jgiven

Reputation: 427

Query error "Subquery returned more than 1 value..." Shouldn't "IN" solve this?

I have received great help here before, so I'm hoping someone can help me figure this out. Can anyone tell me why Query #1 results in the error "Subquery returned more than 1 value..." but Query #2 works fine? Since I am using "IN" instead of "=" to qualify the results I thought multiple values would be acceptable.

Any suggestions on how I can accomplish this?

--Query #1
SELECT _Column1_ from _View1_
WHERE
_Column2_ IN( CASE
WHEN ISNUMERIC(1000110) = 1 AND LEN(1000110) > 3
THEN (
SELECT  _Column2_
FROM    _Table1_
WHERE   _Column2_ = CONVERT(Int, LEFT(1000110, 4))
OR      _Column3_ = CONVERT(Int, LEFT(1000110, 4))
)
WHEN ISNUMERIC(1000110) = 1 AND LEN(1000110) <= 3
THEN 0
ELSE (SELECT _Column2_ from _Table1_)
END)

NOTE: In the final query, 1000110 will actually be a username variable that could return a numeric username or alpha numeric

--Query #2
WHEN ISNUMERIC(1000110) = 1 AND LEN(1000110) > 3
THEN CONVERT(Int, LEFT(1000110, 4))
WHEN ISNUMERIC(1000110) = 1 AND LEN(1000110) <= 3
THEN 0
ELSE (SELECT _Column2_ from _Table1_)
END)

Upvotes: 0

Views: 180

Answers (1)

suff trek
suff trek

Reputation: 39777

SELECT _Column2_ from _Table1_ most likely returns multiple resuluts. In this context only a single value is allowed.

As a test try SELECT TOP(1) _Column2_ from _Table1_

Upvotes: 2

Related Questions