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
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
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
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