Sql Server Collate Syntax Error

I have searched using collate and am using it in the same way the answers say to use it; however, I am getting an error

Incorrect syntax near 'COLLATE'

Here is my query:

SELECT
    P.FIRST_NAME_SRCH,
    P.LAST_NAME_SRCH,
    ' ' as Title,
    CASE E.FULL_PART_TIME
       WHEN 'F' THEN 'Full Time'
       WHEN 'P' THEN 'Part Time'
       WHEN 'O' THEN 'Occasional'
       ELSE E.FULL_PART_TIME
    END AS FULL_PART_TIME,
    ' ' as Capacity,
    REPLACE(E.HOME_PHONE,'/','-') as HOMEPHONE,
    ' ' as MobilePh,
    ' ' as Email,
    CONVERT(char(10),E.BIRTHDATE,101) as 'BIRTHDATE',
    CASE 
       WHEN E.HIRE_DT > E.REHIRE_DT THEN CONVERT(char(10),E.HIRE_DT,101)
       WHEN E.REHIRE_DT > E.HIRE_DT THEN CONVERT(char(10),E.REHIRE_DT,101)
       ELSE CONVERT(char(10),E.HIRE_DT,101)
    END as 'HIRE_DT',
    ' ' as CommPref,
    RTRIM(K.LEVEL3) as 'JOBCODE',
    E.EMPLID
FROM
    HRPROD..PS_EMPLOYEES E, HRPROD..PS_PERSONAL_DATA P, TKCSDB..CTRLEVEL3CFG K
WHERE
    E.COMPANY = 'WSQ'
    AND E.EMPLID = P.EMPLID
    AND K.VAL106 COLLATE DATABASE_DEFAULT = E.JOBCODE COLLATE DATABASE_DEFAULT
ORDER BY
    P.LAST_NAME_SRCH

I have tried (with same syntax error)

AND 
   UPPER(K.VAL106) COLLATE DATABASE_DEFAULT = UPPER(E.JOBCODE) COLLATE DATABASE_DEFAULT

and (returns Cannot resolve collation conflict for equal to operation.)

UPPER(K.VAL106) = UPPER(E.JOBCODE)

What am I doing wrong in the syntax?

9

1 Answer

This must be a version thing. Executing via the SQL SERVER 2012 Mgt Studio query tool returns the expected results without error.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Marcus Vance

Marcus Vance

Cybersecurity & Digital Privacy Researcher

Marcus Vance is a cybersecurity auditor and technology writer dedicated to educating the public about online safety, data privacy regulations, enterprise security, and emerging cyber threats.

Share this article
Twitter Facebook Pinterest