CODEWITHSUNDEEP
sqlt-sql
user1202606
user1202606

Reputation: 1160

sql Case statement bring back multiple values

I want to bring back the county Id and the county name. How can I fix this query?

DECLARE @test int = 0;


select 
CASE (@test)
    when 0 then (SELECT co.Id, co.Description
                 FROM Dictionary.Counties as co
                 INNER JOIN CountyCollaboration as cc on cc.CountyId = co.Id
                 WHERE cc.CollaborationId = (SELECT cc1.CollaborationId from CountyCollaboration as cc1
                                             WHERE cc1.CountyId = 34))
END

I get the error only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

If I comment out co.Description so I'm only bringing back co.Id, I get a different error: subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <=, >, >=, or when the subquery is used as as expression.

Upvotes: 0

Views: 180

Answers (3)

D Stanley
D Stanley

Reputation: 152566

Your subquery was returning multiple fields and possible multiple records. A subquery like this must always return one field and one record.

I'm guessing the INNER JOIN is causing multiple records to be returned. If all of the records have the same value you can to a DISTINCT or TOP 1.

Upvotes: 0

Dan Bracuk
Dan Bracuk

Reputation: 20804

I suggest restructuring your query to look like this.

select id, country 
from 
(select co.id
, co.country
, case @test  code for test goes here end caseresult
from all that stuff you have as a subquery in your question
) derivedtable

Upvotes: 1

Phil Sandler
Phil Sandler

Reputation: 28016

You can only return one expression from a CASE statement. Try using IF/ELSE instead.

The CASE statement in T-SQL is not a control flow statement like CASE/SWITCH in many programming languages.

Upvotes: 1

Related Questions