Using the output of a case statement as selection criteria in an IN clause

[Member 2655861]

I have the code below which works fine in the two WHEN parts. However when the two parts fail, because the group key is absent (a valid condition) I want to choose both, hence the ELSE part. But no rows are returned in this case. mantype can be W or R SELECT * FROM callsigns WHERE mantype IN (SELECT CASE WHEN EXISTS(SELECT 1 FROM group gr1 WHERE gr1.group_key = 'RA' AND gr1.gra_valid = 1 AND 0 = (gr1.gra_flags & 7)^3) THEN 'R' WHEN EXISTS(SELECT 1 FROM group gr2 WHERE gr2.group_key = 'RA' AND gr2.gra_valid = 1 AND 0 = (gr2.gra_flags & 7)^1) THEN 'W' ELSE 'W'+','+'R' END)

[J4amieC]

Try

ELSE SELECT 'W' UNION SELECT 'R'

[Member 2655861]

I have tried that but get a syntax error line 14 Incorrect syntax near the keyword 'SELECT' line 15 Incorrect syntax near ')' line 14 ELSE SELECT 'W' UNION SELECT 'R' line 15 END) I have tried just the SELECT CASE part of the Query with no group key and line 14 as ELSE 'W'+','+'R' and this returns W,R. Even tried doubling up the quotes viz: ELSE '''W'''+','+'''R''' which returns 'W','R' but this also fails to bring back rows

[J4amieC]

Ive tried this a few different ways, and hit upon the same error as you've gotten. I think you're going to be reduced to splitting it into IF..THEN logic

IF EXISTS(SELECT 1
FROM group gr1
WHERE gr1.group_key = 'RA'
AND gr1.gra_valid = 1
AND 0 = (gr1.gra_flags & 7)^3)
SELECT * FROM callsigns WHERE mantype = 'R'
ELSE IF EXISTS(SELECT 1
FROM group gr2
WHERE gr2.group_key = 'RA'
AND gr2.gra_valid = 1
AND 0 = (gr2.gra_flags & 7)^1)
SELECT * FROM callsigns WHERE mantype = 'W'
ELSE
SELECT * FROM callsigns WHERE mantype IN ('W','R')

Edit: I actually think thats also miles more readable than your original too. :-D

[Member 2655861]

Thanks for that. I had come to the same conclusion myself. Seems a pity that CASE could not be used a quirk of SQL Server!!