sql case statement

MariaHalt
MariaHalt used Ask the Experts™
on
Is there a way to test for a string range in the case statement?  See my lame examples that don't work:

SELECT
CASE FirstName
WHEN IN ('MARY', 'SUE', 'ELLEN') THEN 'MOTHER'
ELSE 'OTHER'
END
FROM CastOfDallas

SELECT
CASE FirstName
WHEN ('MARY' OR 'SUE' OR 'ELLEN') THEN 'MOTHER'
ELSE 'OTHER'
END
FROM CastOfDallas

I don't want to do this, becaue the then's are the same.  There must be a better way, right?

SELECT
CASE FirstName
WHEN 'MARY'  THEN 'MOTHER'
WHEN 'SUE'   THEN 'MOTHER'
WHEN 'ELLEN' THEN 'MOTHER'
ELSE 'OTHER'
END
FROM CastOfDallas
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2010
Commented:
SELECT
CASE WHEN FirstName IN ('MARY', 'SUE', 'ELLEN') THEN 'MOTHER'
ELSE 'OTHER'
END
FROM CastOfDallas
Top Expert 2012

Commented:
There must be a better way, right?
There is:  Build a cross-reference table.
SELECT
CASE FirstName
WHEN (select '1' from TABLE_NAME where name IN ('MARY', 'SUE', 'ELLEN')) THEN 'MOTHER'
ELSE 'OTHER'
END
FROM CastOfDallas

Author

Commented:
Perfect...Thanks!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial