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?