SQLTeam.com | Weblogs | Forums

Incorrect syntax near the keyword 'AS'

#1

Can anybody tell why I got an error message "Incorrect syntax near the keyword 'AS'. Thank you very much

SELECT 'A' AS Fc, 100 AS Company, 1 AS RecType,
EMPLOYEE.EMPLOYEE AS Employee,
CONVERT(VARCHAR(10),EMPLOYEE.ADJ_HIRE_DATE,101) AS ADJ_HIRE_DATE,
CONVERT(VARCHAR(10),EMPLOYEE.TERM_DATE,101) AS TERM_DATE,
EMPLOYEE.EMP_STATUS,
'BNMedSubsidy' as FieldName,
(CASE WHEN (EMPLOYEE.ADJ_HIRE_DATE>='1/1/2006' AND EMP_STATUS IN('R1','R2','RS','A5'))
THEN 'SUB_RN_SN'
ELSE
IIF((EMPLOYEE.ADJ_HIRE_DATE<'1/1/2006' AND TERM_DATE>='1/1/2008'),
'RY_SN_' + SUBSTRING(HREMPUSF.A_FIELD, 1, 2) + SUBSTRING(HREMPUSF.A_FIELD, 4, 2),

            IIF((EMPLOYEE.ADJ_HIRE_DATE<'1/1/2006' AND TERM_DATE<'1/1/2008'),     
            'RY_SY_' + SUBSTRING(HREMPUSF.A_FIELD, 1, 2) + SUBSTRING(HREMPUSF.A_FIELD, 4, 2),
            'OOPS')) AS Afield
                               
          FROM            HREMPUSF INNER JOIN
                                                   EMPLOYEE ON HREMPUSF.EMPLOYEE = EMPLOYEE.EMPLOYEE
          WHERE        (HREMPUSF.FIELD_KEY = 96) 
                                     AND EMP_STATUS IN('R1','R2','RS','A5')
          ORDER BY EMPLOYEE.EMPLOYEE
0 Likes

#2

Remove first bracket on this line:
(CASE WHEN (EMPLOYEE.ADJ_HIRE_DATE>='1/1/2006' AND EMP_STATUS IN('R1','R2','RS','A5'))

or add bracket to this line:
'OOPS')) AS Afield

0 Likes

#3

I removed but I am still having the same error
SELECT 'A' AS Fc, 100 AS Company, 1 AS RecType,
EMPLOYEE.EMPLOYEE AS Employee,
CONVERT(VARCHAR(10),EMPLOYEE.ADJ_HIRE_DATE,101) AS ADJ_HIRE_DATE,
CONVERT(VARCHAR(10),EMPLOYEE.TERM_DATE,101) AS TERM_DATE,
EMPLOYEE.EMP_STATUS,
'BNMedSubsidy' as FieldName,
CASE WHEN (EMPLOYEE.ADJ_HIRE_DATE>='1/1/2006' AND EMP_STATUS IN('R1','R2','RS','A5'))
THEN 'SUB_RN_SN'
ELSE
IIF((EMPLOYEE.ADJ_HIRE_DATE<'1/1/2006' AND TERM_DATE>='1/1/2008'),
'RY_SN_' + SUBSTRING(HREMPUSF.A_FIELD, 1, 2) + SUBSTRING(HREMPUSF.A_FIELD, 4, 2),

            IIF((EMPLOYEE.ADJ_HIRE_DATE<'1/1/2006' AND TERM_DATE<'1/1/2008'),     
            'RY_SY_' + SUBSTRING(HREMPUSF.A_FIELD, 1, 2) + SUBSTRING(HREMPUSF.A_FIELD, 4, 2),
			'OOPS')) AS Afield
		 
                               
          FROM            HREMPUSF INNER JOIN
                                                   EMPLOYEE ON HREMPUSF.EMPLOYEE = EMPLOYEE.EMPLOYEE
          WHERE        (HREMPUSF.FIELD_KEY = 96) 
                                     AND EMP_STATUS IN('R1','R2','RS','A5')
          ORDER BY EMPLOYEE.EMPLOYEE
0 Likes

#4

Remove the open paren before the CAST statement, and add an END for the CASE statement (CASE requires an END in SQL).

...same as before...,
CASE WHEN (EMPLOYEE.ADJ_HIRE_DATE>='1/1/2006' AND EMP_STATUS IN ('R1','R2','RS','A5')) --<--note no ( before CASE
THEN 'SUB_RN_SN'
ELSE
IIF((EMPLOYEE.ADJ_HIRE_DATE<'1/1/2006' AND TERM_DATE>='1/1/2008'),
'RY_SN_' + SUBSTRING(HREMPUSF.A_FIELD, 1, 2) + SUBSTRING(HREMPUSF.A_FIELD, 4, 2),

            IIF((EMPLOYEE.ADJ_HIRE_DATE<'1/1/2006' AND TERM_DATE<'1/1/2008'),     
            'RY_SY_' + SUBSTRING(HREMPUSF.A_FIELD, 1, 2) + SUBSTRING(HREMPUSF.A_FIELD, 4, 2),
            'OOPS')) END AS Afield --<<-- Note added END
0 Likes