Converting MYSQL statement to MSSQL

[dtmd]

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

[huangzhi]

Try this code.

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 s1.dsg = CASE s1.cc1 WHEN 'uk' THEN 'adm2' ELSE 'adm1' END
  AND s1.nt='n'
ORDER BY name