Hello, i have a database that came with MYSQL statements to get it working but we are using MSSQL and have managed to convert a large proportion of them but there are two statements we are having trouble with and wondered if anyone could help. The first is below:
SELECT DISTINCT
COALESCE (s3.ADM_CODE, s1.ADM_CODE) AS Expr1, COALESCE (s3.FULLNAME, COALESCE (COALESCE (CASE s2.shortform WHEN '' THEN NULL
ELSE s2.shortform END, s2.FULLNAMEND), COALESCE (CASE s1.shortform WHEN '' THEN NULL ELSE s1.shortform END, s1.FULLNAMEND)))
AS name
FROM worldcities_pro_states AS s1 LEFT OUTER JOIN
worldcities_pro_states AS s2 ON s2.CC1 = s1.CC1 AND s2.UFI = s1.UFI AND s2.LC = 'eng' LEFT OUTER JOIN
worldcities_pro_states AS s3 ON s3.CC1 = 'uk' AND s3.ADM_PARENT = s1.ADM_CODE AND s3.DSG = 'adm3'
WHERE (s1.CC1 = 'UK') OR
(s1.CC1 = 'uk') AND (s1.ADM_PARENT = 'UK')
AND CASE s1.cc1 WHEN 'uk' THEN s1.dsg='adm2' ELSE s1.dsg='adm1' END
AND s1.nt='n'
ORDER BY name
Currently the above doesn't work, but as soon as we remove "AND CASE s1.cc1 WHEN 'uk' THEN s1.dsg='adm2' ELSE s1.dsg='adm1' END AND s1.nt='n'"
then it produces some results. Does anyone know how to get the above statement working in MSSQL?
many thanks,
Martin