CODEWITHSUNDEEP
visual-studio-2008sql-server-2008ssrs-2008
CodeMinion
CodeMinion

Reputation: 653

SSRS2008 - SELECT in a CASE...WHEN...THEN

I am using a hidden internal Parameter (@DoorHelper) to check if another parameter I am using(@Door) contains a special value I use to represent 'global'('9999') and return all Doors in that case.

SELECT 
CASE 
WHEN ('9999'+'-'+ RIGHT(Unknown1,1) in (@Door)) 
THEN (SELECT DISTINCT [Zone]+' - '+CAST([OfficeID] as char(5)) FROM [ADB].[dbo].[ZoneByOffice])
ELSE (@Door)
END AS ZoneID
FROM [ADB].[dbo].[EventLog]

Issue is, returning more than 1 result from a THEN seems to throw errors.

How can I reform this SQL to do what I want it to?

Upvotes: 1

Views: 199

Answers (1)

Alex Aza
Alex Aza

Reputation: 78457

You can use TOP 1

SELECT 
    CASE 
        WHEN ('9999'+'-'+ RIGHT(Unknown1,1) in (@Door)) 
        THEN (SELECT TOP 1 [Zone]+ ' - '+ CAST([OfficeID] as char(5)) FROM [ADB].[dbo].[ZoneByOffice])
        ELSE (@Door)
    END AS ZoneID
FROM [ADB].[dbo].[EventLog]

or you limit the result set with WHERE condition by unique field if any, to guarantee it never returns more than one row.

Upvotes: 1

Related Questions