CASE IF WHEN ELSE BUT NOT IF TUESDAY

My new job has thrust me straight from tidying up the underwear drawer into the convoluted world of corporate data structures. So yesterday, I wrote a fairly convoluted bit of code (almost two pages of nearly incomprehensible SELECT CASE ELSE FROM JOIN WHERE GROUP BY) that reached into the innards of four different databases, pulled out a few miles of intestines, applied a couple IF THEN and CASE WHEN statements and viola – generated reports I could then email to the eagerly waiting clients.

It is good to be back. It is also painful. It’s just exactly like getting back on a bike after a few decades. I remember everything I used to do. I just…don’t necessarily remember exactly how.

And with computers…you’ve got to be pretty exact.

So, for the edification of the guild, I would like to pass along the following bit of information, which took me almost an hour to reacquire. When one is writing a CASE statement as part of a select in SQL Server, it should look something like this:

SELECT
'Item Code' = CASE
WHEN C.ITEM_ACTVY_CD = 'A' THEN 'Active'
ELSE 'Inactive'
END
FROM T_ITEMS

Got that?

If you forget the ‘END’, it won’t work.

Trying to say CASE WHEN C.ITEM_ACTVY_CD = 'A' THEN 'Active' ELSE 'Inactive' END AS ‘Item Code’ also will not work.

Oh. And, by the way, cussing at the computer doesn’t help. Nor does muttering, “Oh, c’mon. You know what I mean!”

It will just keep stubbornly saying something utterly unhelpful, like, “ERROR: Server level 8276928672, line 427 statement 2, error near ‘FROM’”

Gee. How did we ever get anything done before we had computers?!?!