sql case statement
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SELECT
CASE FirstName
WHEN (select '1' from TABLE_NAME where name IN ('MARY', 'SUE', 'ELLEN')) THEN 'MOTHER'
ELSE 'OTHER'
END
FROM CastOfDallas
CASE FirstName
WHEN (select '1' from TABLE_NAME where name IN ('MARY', 'SUE', 'ELLEN')) THEN 'MOTHER'
ELSE 'OTHER'
END
FROM CastOfDallas
ASKER
Perfect...Thanks!
There is: Build a cross-reference table.