And to add to this ancient thread (that I also found with a Google search)
I have been looking for an answer to this online, and have seen a lot of "you don't need to ever do that" responses which I just looooove. Anyway...
Here is a brief outline of what I need to do:
Employees always have a hire1 date, but some employees have a hire2 date. If someone has a hire2 date, I need to use that in my select and where, but if they don't have a hire2, then I have to use hire1. The final output has to show both dates.
I have a case statement in my select clause, but I also need one in my where clause; the where clause is currently not working correctly.
SELECT employeeNumber, hire1, hire2, CASE WHEN hire2 = '' THEN hire1 ELSE hire2 END AS year
WHERE CASE WHEN hire2 = '' THEN hire1 ELSE hire2 END IN (1997,1998,1999)
ORDER BY year
Right now, I am only getting results that have a hire2 date, so something isn't right.
I know what I need in my head, but that doesn't mean I'm explaining it right, so let me know if it's not clear.