Syntax error using CASE...WHEN

shelbyinfotech
shelbyinfotech used Ask the Experts™
on
Msg 170, Level 15, State 1, Line 6
Line 6: Incorrect syntax near '='.



SELECT RTRIM(LTRIM(CLASTNAME)) + ' , ' + RTRIM(LTRIM(CFIRSTNAME)) AS EMPNAME,
       CGROUP2                                                    AS EMPDEPT,
       CEMPID                                                     AS EMPID,
       CGROUP1,
       DEPTCODE = CASE UPPER(RTRIM(LTRIM([CGROUP2])))
                    WHEN UPPER(RTRIM(LTRIM([CGROUP2]))) = 'COMMERCIAL PERMITS'
                  THEN
                    'COM'
                    WHEN UPPER(RTRIM(LTRIM([CGROUP2]))) = 'COMMERCIAL REAPPRAIS'
                  THEN
                    'COM'
                    WHEN UPPER(RTRIM(LTRIM([CGROUP2]))) = 'RESIDENTIAL PERMITS'
                  THEN
                    'RES'
                    WHEN UPPER(RTRIM(LTRIM([CGROUP2]))) = 'RESIDENTIAL REAPPRAI'
                  THEN
                    'RES'
                    ELSE 'OTH'
                  END
FROM   EMPLOYEE
WHERE  ( RIGHT('000' + RTRIM(LTRIM(CEMPID)), 5) = '10515' )  

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2011
Top Expert 2012
Commented:
don't make the assignment,  simply use an alias


CASE UPPER(RTRIM(LTRIM([CGROUP2])))
                    WHEN UPPER(RTRIM(LTRIM([CGROUP2]))) = 'COMMERCIAL PERMITS'
                  THEN
                    'COM'
                    WHEN UPPER(RTRIM(LTRIM([CGROUP2]))) = 'COMMERCIAL REAPPRAIS'
                  THEN
                    'COM'
                    WHEN UPPER(RTRIM(LTRIM([CGROUP2]))) = 'RESIDENTIAL PERMITS'
                  THEN
                    'RES'
                    WHEN UPPER(RTRIM(LTRIM([CGROUP2]))) = 'RESIDENTIAL REAPPRAI'
                  THEN
                    'RES'
                    ELSE 'OTH'
                  END as DEPTCODE
Jim HornSQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015
Commented:
You're doing 'UPPER(RTRIM(LTRIM([CGROUP2])))' twice, and for CASE blocks  instead of 'column name =' use ' as column name'

SELECT RTRIM(LTRIM(CLASTNAME)) + ' , ' + RTRIM(LTRIM(CFIRSTNAME)) AS EMPNAME,
      CGROUP2 AS EMPDEPT,
      CEMPID AS EMPID,
      CGROUP1,
      CASE UPPER(RTRIM(LTRIM([CGROUP2])))
            WHEN 'COMMERCIAL PERMITS' THEN 'COM'
            WHEN 'COMMERCIAL REAPPRAIS' THEN 'COM'
            WHEN 'RESIDENTIAL PERMITS' THEN 'RES'
            WHEN 'RESIDENTIAL REAPPRAI' THEN 'RES'
            ELSE 'OTH'
            END as DEPTCODE
FROM  EMPLOYEE
WHERE  ( RIGHT('000' + RTRIM(LTRIM(CEMPID)), 5) = '10515' )
Jim HornSQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015

Commented:
Thanks for the split.  Good luck with your project.  -Jim

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial