1

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?

CodeMinion
  • 653
  • 2
  • 10
  • 24
  • What does `@Door` look like? Is it a CSV list that you are trying to use as an actual value list for the `IN(…)` operator? – Andriy M Jun 24 '11 at 09:46

1 Answers1

1

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.

CodeMinion
  • 653
  • 2
  • 10
  • 24
Alex Aza
  • 76,499
  • 26
  • 155
  • 134
  • Can I use Distinct along with your suggestion? And from my understanding, if I use TOP1 like that I might get lots of the same [Zone] instead of a set of all available ones? – CodeMinion Jun 24 '11 at 02:31
  • `TOP 1` will return just one, so `DISTINCT` does not make much sense in this case. – Alex Aza Jun 24 '11 at 02:38
  • @Kitler - if this is not what you want, please show a sample input and expected output for the query. – Alex Aza Jun 24 '11 at 02:59
  • I meant like SELECT DISTINCT CASE, although I suppose TOP 1 would still return the same thing in the inner SELECT. – CodeMinion Jun 24 '11 at 03:06
  • @Kitler - I don't understand what you mean. Sorry. – Alex Aza Jun 24 '11 at 03:19
  • 1
    @Kitler: In any event, the subquery **must** return no more than one value. Whether you ensure that with `DISTINCT CASE …` or with `TOP 1` is up to you. The latter is probably better, though, because with DISTINCT you'd have an absolutely unnecessary distinct sort, which would affect the performance negatively. – Andriy M Jun 24 '11 at 09:53
  • thanks for your replies, turns out i was just overcomplicating things, abandoned this approach and just used a WHERE CASE instead of another dataset – CodeMinion Jun 26 '11 at 22:28
  • @Kitler - glad you found a solution. I believe I answered your question the best I could. :) – Alex Aza Jun 26 '11 at 22:30