MS SQL

MS SQL RSS

Converting MYSQL statement to MSSQL

Please support our MS SQL advertiser: Intel Parallel Studio Home

•

Join Date: May 2008

Posts: 1

Reputation: dtmd is an unknown quantity at this point

Solved Threads: 0

dtmd

Offline

Newbie Poster

Converting MYSQL statement to MSSQL

May 30th, 2008

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:

 Help with Code Tags 
 MS SQL Syntax (Toggle Plain Text) 
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
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

•

Join Date: Feb 2008

Posts: 42

Reputation: huangzhi is an unknown quantity at this point

Solved Threads: 13

huangzhi

Offline

Light Poster

Re: Converting MYSQL statement to MSSQL

Jun 3rd, 2008

Try this code.

 Help with Code Tags 
 MS SQL Syntax (Toggle Plain Text) 
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
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