Menu
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.
604
The SQL CASE Statement The CASE statement goes through conditions and returns a value when the first condition is met (like an if-then-else statement). So, once a condition is true, it will stop reading and return the result. If no conditions are true, it returns the value in the ELSE clause.
The simple way to achieve this goal is to add a CASE expression to your SELECT statement. In this article, we'll introduce you to the syntax, formats, and uses of the CASE expression. The CASE expression is a conditional expression: it evaluates data and returns a result.
The expression starts with the CASE keyword and ends with the END keyword. The names of specific columns or expressions are entered after the CASE keyword. The WHEN and THEN keywords define the logical conditions. After the WHEN clause, we see the condition or value to compare; the THEN determines the result if the condition is met.
The searched CASE expression is the most commonly-used format. But instead of comparing a column or expression against a defined set of values, a searched expression can compare multiple WHEN conditions and determine a result. This makes a searched CASE the better choice for more complicated logic. CASE WHEN conditions THEN when_result ...
try this
SELECT LastName, CASE WHEN FirstName ='Ian' THEN JobNo else null END
FROM Employees
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
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).
42
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
