Incorrect syntax near the keyword 'As'

SQL Apprentice Question
I'm trying to complete an If, Then statement within a script that I put
together, and I keep getting an "Incorrect syntax near the keyword 'As'"
error toward the end of the statement.


The whole statement says that if you are taking the payroll codes from the
benefit table, then provide the payroll code's description such "health
insurance" or "dental insurance".


The very beginning of my clause, of course, says "SELECT ... CASE
UPR30300.PAYROLCD WHEN 'SC2HF' THEN UPR40800.DSCRIPTN
WHEN 'SC2LE' THEN UPR40800.DSCRIPTN
WHEN 'SC2LS' THEN UPR40800.DSCRIPTN
WHEN 'SC2LC' THEN UPR40800.DSCRIPTN
WHEN 'SC2LF' THEN UPR40800.DSCRIPTN
ELSE 0 END As [Payroll Description]


There must be something wrong with the ELSE portion. I tried to fix it, by
saying ELSE '' END As [Payroll Description]. But, I got the same error.


What am I doing wrong, and how can I fix this?



Celko Answers
Having blanks inside a column name is a bad idea, since the name will
not port to other languages, is very easy to mis-type and messes up
the data dictionary. You also used the proprietary square brackets
instead of the proper double quotes.

You can make your code a lot easier to read without the extra WHEN
clauses. A CASE expression has one and only one data type. You need
to be sure that the THEN clauses are all the same data type. That can
be done with implicit promotion or use a CAST() function.


CASE
WHEN UPR30300.payrol_cd
IN ('0SC2HF', 'SC2LE', 'SC2LS', 'SC2LC')
THEN UPR40800.dscriptn
ELSE '{{Unknown}}' AS payroll_description