•
•
•
•
What is DaniWeb IT Discussion Community?
You're currently browsing the MS SQL section within the Web Development category of DaniWeb, a massive community of 425,791 software developers, web developers, Internet marketers, and tech gurus who are all enthusiastic about making contacts, networking, and learning from each other. In fact, there are 3,185 IT professionals currently interacting right now! Registration is free, only takes a minute and lets you enjoy all of the interactive features of the site.
Please support our MS SQL advertiser: Programming Forums
Views: 693 | Replies: 1
 |
•
•
Join Date: May 2008
Posts: 1
Reputation: 
Rep Power: 0
Solved Threads: 0
Newbie Poster
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:
Currently the above doesn't work, but as soon as we remove
then it produces some results. Does anyone know how to get the above statement working in MSSQL?
many thanks,
Martin
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 nameCurrently 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: 18
Reputation:
Rep Power: 1
Solved Threads: 5
Newbie Poster
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 Hence Wijaya
www.ex-Soft.tk
www.ex-Soft.tk
|
•
•
•
•
•
•
•
•
DaniWeb MS SQL Marketplace
•
•
•
•
Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
Linear Mode
Other Threads in the MS SQL Forum
- Previous Thread: How to create an SQL CE Mobile Application
- Next Thread: CASE function
