[RESOLVED] Problem using SUM(CASE

[DavidGraham167]

I am using the following query using SUM & CASE.
It works when I use the lines containing '80' to '86' but when I add the lines containing '5' to '20' I get the following error:
Incorrect syntax near '5'
Incorrect syntax near keywork 'AS'

query = "SELECT sage_id, pay_code, name," & _
" SUM(CASE WHEN RIGHT(work_code,2)='80' THEN hours ELSE 0 END) AS leave," & _
" SUM(CASE WHEN RIGHT(work_code,2)='81' THEN hours ELSE 0 END) AS unpaid," & _
" SUM(CASE WHEN RIGHT(work_code,2)='82' THEN hours ELSE 0 END) AS sick," & _
" SUM(CASE WHEN RIGHT(work_code,2)='83' THEN hours ELSE 0 END) AS mat," & _
" SUM(CASE WHEN RIGHT(work_code,2)='84' THEN hours ELSE 0 END) AS pat," & _
" SUM(CASE WHEN RIGHT(work_code,2)='85' THEN hours ELSE 0 END) AS par," & _
" SUM(CASE WHEN RIGHT(work_code,2)='86' THEN hours ELSE 0 END) AS comp," & _
" SUM(CASE WHEN RIGHT(work_code,2)='99' AND expense='5' THEN expense ELSE 0 END) AS 5p," & _
" SUM(CASE WHEN RIGHT(work_code,2)='99' AND expense=10 THEN expense ELSE 0 END) AS 10p," & _
" SUM(CASE WHEN RIGHT(work_code,2)='99' AND expense=15 THEN expense ELSE 0 END) AS 15p," & _
" SUM(CASE WHEN RIGHT(work_code,2)='99' AND expense=20 THEN expense ELSE 0 END) AS 20p" & _
" FROM (" & JDquery & ") AS JD" & _
" GROUP BY sage_id, pay_code, name" & _
" ORDER BY pay_code"

I am trying to get the sum of expenses when the individual expenses are 5, 10, 15 & 20 (expense is numeric)

The actual query is shown below:

SELECT sage_id, pay_code, name,
SUM(CASE WHEN RIGHT(work_code,2)='80' THEN hours ELSE 0 END) AS leave,
SUM(CASE WHEN RIGHT(work_code,2)='81' THEN hours ELSE 0 END) AS unpaid,
SUM(CASE WHEN RIGHT(work_code,2)='82' THEN hours ELSE 0 END) AS sick,
SUM(CASE WHEN RIGHT(work_code,2)='83' THEN hours ELSE 0 END) AS mat,
SUM(CASE WHEN RIGHT(work_code,2)='84' THEN hours ELSE 0 END) AS pat,
SUM(CASE WHEN RIGHT(work_code,2)='85' THEN hours ELSE 0 END) AS par,
SUM(CASE WHEN RIGHT(work_code,2)='86' THEN hours ELSE 0 END) AS comp,
SUM(CASE WHEN RIGHT(work_code,2)='99' AND expense='5' THEN expense ELSE 0 END) AS 5p,
SUM(CASE WHEN RIGHT(work_code,2)='99' AND expense=10 THEN expense ELSE 0 END) AS 10p,
SUM(CASE WHEN RIGHT(work_code,2)='99' AND expense=15 THEN expense ELSE 0 END) AS 15p,
SUM(CASE WHEN RIGHT(work_code,2)='99' AND expense=20 THEN expense ELSE 0 END) AS 20p
FROM (
SELECT js.job_number, DATEPART(ww,DATEADD(d,1,week_end)) as week_of_year, js.pay_code, js.work_code, js.hours, js.expense, js.week_end, e.name, e.basic, e.sage_id
FROM jobsheet AS js
LEFT JOIN employee AS e ON js.pay_code = e.pay_code
WHERE (week_end >='01/05/2013 ' AND week_end <='02/01/2013')
) AS JD
GROUP BY sage_id, pay_code, name
ORDER BY pay_code

[DavidGraham167]

I solved it.
It was the ALIAS, it shouldn't start with a number.
I assumed the problem was with the expense='5' but it was AS 5p - I changed it to AS p5