Case in Where Clause

mnp13 · September 12th, 2011, 11:02 AM

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.

Code:

SELECT  employeeNumber, hire1, hire2, CASE WHEN hire2 = '' THEN hire1 ELSE hire2 END AS year
FROM employees
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.

Thanks!

Michelle

joefawcett · September 12th, 2011, 11:37 AM

Are you sure that the empty dates aren't expressed as NULL rather than an empty string?
If they are NULL then you can use:

Code:

SELECT employeeNumber, hire1, hire2, COALESCE(hire2, hire1) [year]
FROM employees
WHERE COALESCE(hire2, hire1)  IN (1997,1998,1999) 
ORDER BY 4

mnp13 · September 12th, 2011, 01:43 PM

You hit it. The reason it wasn't functioning was because I was using '' and not NULL.

I changed it to this, and now it's working.

Code:

SELECT  employeeNumber, hire1, hire2, CASE WHEN hire2 IS NULL THEN hire1 ELSE hire2 END AS year
FROM employees
WHERE CASE WHEN hire2 IS NULL THEN hire1 ELSE hire2 END IN (1997,1998,1999) 
ORDER BY year

However, I'll try coalesce the next time around. THANKS!