Line 2: Incorrect syntax near '='.

[ckearney123]

Can't see the problem here? Having a bad day. Anyone make this work for me?

SELECT D399M.HESA_YEAR, D550M.EMPLOYEE_NUMBER, D500M.SURNAME, D399M.HESA_REC_ID, Substring([D399M.HESA_CONTRACT_ID],1,5) AS
Expr1, Len(RTrim([D399M.HESA_CONTRACT_ID])) AS CtractID_Length_, IIf([CtractID_Length_]= '13', Substring([D399M.HESA_CONTRACT_ID],7,6))
AS Len13, IIf([CtractID_Length_]= '12', Substring([D399M.HESA_CONTRACT_ID],6,6)) AS Len12, IIf([CtractID_Length_]= '14', Substring([D399M.HESA_CONTRACT_ID],8,6))
AS Len14, IIf(IsNull([Len13]),[Len14],[Len13]) AS LEN_Final, Len([D399M.HESA_CONTRACT_ID])
AS Expr2, D399M.HESA_CAMP_ID, D399M.HESA_STAFF_ID, D399M.HESA_CONTRACT_ID, D399M.HESA_TERMS, D399M.HESA_PRIM_EMP_FUNC, D399M.HESA_FTE,
D399M.HESA_TEACH_WELSH, D399M.HESA_GRADE_STRUCT, D399M.HESA_SENIOR_MGMT, D399M.HESA_SAL_PRIM_SOURCE, D399M.HESA_PROP_SALARY_CAGI,
D399M.HESA_SAL_SEC_SOURCE, D399M.HESA_SPINAL_POINT, D399M.HESA_SALARY, D399M.HESA_NHS_JOINT, D399M.HESA_NHS_CONTRACT_GRADE,
D399M.HESA_HEALTH_CARE_SPEC, D399M.HESA_JOINT_CONTRACT, D399M.HESA_CONTRACT_START_DATE, D399M.HESA_CONTRACT_END_DATE, D399M.HESA_ACT_CODE1,
D399M.HESA_COST_CENTRE1, D399M.HESA_PERC_COST_CENTRE1, D399M.HESA_ACT_CODE2, D399M.HESA_COST_CENTRE2, D399M.HESA_PERC_COST_CENTRE2,
D399M.HESA_ACT_CODE3, D399M.HESA_COST_CENTRE3, D399M.HESA_PERC_COST_CENTRE3, D399M.HESA_MODE_EMP
FROM D399M (NOLOCK) INNER JOIN (D500M (NOLOCK) INNER JOIN D550M (NOLOCK) ON D500M.PERSON_REF = D550M.PERSON_REF) ON D399M.PERSON_REF = D550M.PERSON_REF
WHERE ((D399M.HESA_YEAR)= '2006'))

 

[SQLDenis]

IIf is not a SQL Server function, lookup CASE in books on line

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[ptheriault]

I would also add that it would be a lot easier to read your code if you formated it a little better. Properly formated code makes debugging much easier. There are even sites like

http://www.sqlinform.com

that will do it for you.

SELECT  D399M.HESA_YEAR                                            ,
        D550M.EMPLOYEE_NUMBER                                      ,
        D500M.SURNAME                                              ,
        D399M.HESA_REC_ID                                          ,
        Substring([D399M.HESA_CONTRACT_ID],1,5)                                AS Expr1           ,
        Len(RTrim([D399M.HESA_CONTRACT_ID]))                                   AS CtractID_Length_,
        IIf([CtractID_Length_]= '13', Substring([D399M.HESA_CONTRACT_ID],7,6)) AS Len13           ,
        IIf([CtractID_Length_]= '12', Substring([D399M.HESA_CONTRACT_ID],6,6)) AS Len12           ,
        IIf([CtractID_Length_]= '14', Substring([D399M.HESA_CONTRACT_ID],8,6)) AS Len14           ,
        IIf(IsNull([Len13]),[Len14],[Len13])                                   AS LEN_Final       ,
        Len([D399M.HESA_CONTRACT_ID])                                          AS Expr2           ,
        D399M.HESA_CAMP_ID                                                                        ,
        D399M.HESA_STAFF_ID                                                                       ,
        D399M.HESA_CONTRACT_ID                                                                    ,
        D399M.HESA_TERMS                                                                          ,
        D399M.HESA_PRIM_EMP_FUNC                                                                  ,
        D399M.HESA_FTE                                                                            ,
        D399M.HESA_TEACH_WELSH                                                                    ,
        D399M.HESA_GRADE_STRUCT                                                                   ,
        D399M.HESA_SENIOR_MGMT                                                                    ,
        D399M.HESA_SAL_PRIM_SOURCE                                                                ,
        D399M.HESA_PROP_SALARY_CAGI                                                               ,
        D399M.HESA_SAL_SEC_SOURCE                                                                 ,
        D399M.HESA_SPINAL_POINT                                                                   ,
        D399M.HESA_SALARY                                                                         ,
        D399M.HESA_NHS_JOINT                                                                      ,
        D399M.HESA_NHS_CONTRACT_GRADE                                                             ,
        D399M.HESA_HEALTH_CARE_SPEC                                                               ,
        D399M.HESA_JOINT_CONTRACT                                                                 ,
        D399M.HESA_CONTRACT_START_DATE                                                            ,
        D399M.HESA_CONTRACT_END_DATE                                                              ,
        D399M.HESA_ACT_CODE1                                                                      ,
        D399M.HESA_COST_CENTRE1                                                                   ,
        D399M.HESA_PERC_COST_CENTRE1                                                              ,
        D399M.HESA_ACT_CODE2                                                                      ,
        D399M.HESA_COST_CENTRE2                                                                   ,
        D399M.HESA_PERC_COST_CENTRE2                                                              ,
        D399M.HESA_ACT_CODE3                                                                      ,
        D399M.HESA_COST_CENTRE3                                                                   ,
        D399M.HESA_PERC_COST_CENTRE3                                                              ,
        D399M.HESA_MODE_EMP
FROM    D399M (NOLOCK)
        INNER JOIN (D500M (NOLOCK)
                INNER JOIN D550M (NOLOCK)
                ON      D500M.PERSON_REF = D550M.PERSON_REF)
        ON      D399M.PERSON_REF         = D550M.PERSON_REF
WHERE ((D399M.HESA_YEAR)= '2006'))

- Paul
- If at first you don't succeed, find out if the loser gets anything.