Using CASE to select a field in SQL?

Question

Based on if one field in the database is equal to something, I want to select the value of another field. I was thinking that I could use CASE THEN but I can't get it to return the value of a field.

Here is what I have tried so far:

SELECT LastName, CASE FirstName WHEN 'Ian' THEN JobNo END FROM Employees

JobNo is the name of the field which I want to get the value from.

Answer 1

try this

SELECT LastName, CASE  WHEN FirstName ='Ian' THEN JobNo else null END
FROM Employees

Answer 2

you're missing the else statement in your case statement, once that's in there it should work

like this:

SELECT LastName, 
    CASE FirstName WHEN 'Ian' THEN JobNo ELSE -1 END
FROM Employees

Answer 3

You need to define the ELSE:

SELECT LastName, CASE FirstName WHEN 'Ian' THEN JobNo ELSE FirstName END
FROM Employees

I've defaulted to return the FirstName, if you don't want that just replace with something else. Just make sure the datatypes are equivalent to avoid potential issues (I've assumed JobNo is a varchar ref code for purpose of demonstrating syntax).