SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Switch function
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Bertrand82
Starting Member

7 Posts

Posted - 10/12/2012 :  06:44:14  Show Profile  Reply with Quote
Hi,

I have a union query. With the following code. I would like the first column "company" to switch some values to text.

25 = Brazil
60 = USA
90 = KOREA

What will the SQL code be and how should i put it together with the rest of my code as below?

Br Bertrand


SELECT [GI brazil].Company, [GI brazil].ID, [GI brazil].First_name, [GI brazil].Family_name, [GI brazil].[Hire_ date]
FROM [GI brazil];
UNION ALL SELECT [GI USA].Company, [GI USA].ID, [GI USA].First_name, [GI USA].Family_name, [GI USA].[Hire_ date]
FROM [GI USA];
UNION ALL SELECT [GI KOREA].Company, [GI KOREA].ID, [GI KOREA].First_name, [GI KOREA].Family_name, [GI KOREA].[Hire_ date]
FROM [GI KOREA]
ORDER BY Company, Family_name, First_name;

Edited by - Bertrand82 on 10/12/2012 06:45:15

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 10/12/2012 :  06:59:35  Show Profile  Reply with Quote
You can use a case expression like shown below:
SELECT CASE Company
            WHEN 25 THEN 'Brazil'
            WHEN 60 THEN 'USA'
            WHEN 90 THEN 'Korea'
            ELSE 'Some other country'
       END,
       .... other columns
Go to Top of Page

Bertrand82
Starting Member

7 Posts

Posted - 10/12/2012 :  07:22:27  Show Profile  Reply with Quote
Hi and thank you,

Unfortunately I get "characters found after SQL statement"

this is my code in my union query

SELECT [GI brazil].Company, [GI brazil].ID, [GI brazil].First_name, [GI brazil].Family_name, [GI brazil].[Hire_ date]
FROM [GI brazil];
UNION ALL SELECT [GI USA].Company, [GI USA].ID, [GI USA].First_name, [GI USA].Family_name, [GI USA].[Hire_ date]
FROM [GI USA];
UNION ALL SELECT [GI KOREA].Company, [GI KOREA].ID, [GI KOREA].First_name, [GI KOREA].Family_name, [GI KOREA].[Hire_ date]
FROM [GI KOREA]
ORDER BY Company, Family_name, First_name;
SELECT CASE Company
WHEN 25 THEN 'Brazil'
WHEN 60 THEN 'USA'
WHEN 90 THEN 'Korea'
ELSE 'Some other country'
END,
Go to Top of Page

jimf
Flowing Fount of Yak Knowledge

USA
2869 Posts

Posted - 10/12/2012 :  07:48:00  Show Profile  Reply with Quote
You have an extra comma at the end of your statement
ELECT CASE Company
WHEN 25 THEN 'Brazil'
WHEN 60 THEN 'USA'
WHEN 90 THEN 'Korea'
ELSE 'Some other country'
END ,

Jim

Everyday I learn something that somebody else already knew
Go to Top of Page

Bertrand82
Starting Member

7 Posts

Posted - 10/12/2012 :  08:09:02  Show Profile  Reply with Quote
Still doenst work. this is the total code...

SELECT [GI brazil].Company, [GI brazil].ID, [GI brazil].First_name, [GI brazil].Family_name, [GI brazil].[Hire_ date]
FROM [GI brazil];
UNION ALL SELECT [GI USA].Company, [GI USA].ID, [GI USA].First_name, [GI USA].Family_name, [GI USA].[Hire_ date]
FROM [GI USA];
UNION ALL SELECT [GI KOREA].Company, [GI KOREA].ID, [GI KOREA].First_name, [GI KOREA].Family_name, [GI KOREA].[Hire_ date]
FROM [GI KOREA]
ORDER BY Company, Family_name, First_name;
SELECT CASE Company
WHEN 25 THEN 'Brazil'
WHEN 60 THEN 'USA'
WHEN 90 THEN 'Korea'
ELSE 'Some other country'
END,
Go to Top of Page

jimf
Flowing Fount of Yak Knowledge

USA
2869 Posts

Posted - 10/12/2012 :  08:13:39  Show Profile  Reply with Quote
And it still has the comma in it. You really should remove it. Remove the semi-colons as well.


Jim

Everyday I learn something that somebody else already knew
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 10/12/2012 :  08:13:43  Show Profile  Reply with Quote
Try one of these. Very hard to suggest something useful because Jim or I are not able to look at your data and tables. These are just guesses:
--- 1
SELECT 'Brazil' AS Country,
       [GI brazil].Company,
       [GI brazil].ID,
       [GI brazil].First_name,
       [GI brazil].Family_name,
       [GI brazil].[Hire_ date]
FROM   [GI brazil]
UNION ALL 
SELECT 'USA' AS Country,
       [GI USA].Company,
       [GI USA].ID,
       [GI USA].First_name,
       [GI USA].Family_name,
       [GI USA].[Hire_ date]
FROM   [GI USA]
UNION ALL 
SELECT 'Korea' AS Country,
       [GI KOREA].Company,
       [GI KOREA].ID,
       [GI KOREA].First_name,
       [GI KOREA].Family_name,
       [GI KOREA].[Hire_ date]
FROM   [GI KOREA]
ORDER BY
       Company,
       Family_name,
       First_name;

--- 2

SELECT CASE [GI brazil].Company
            WHEN 25 THEN 'Brazil'
            WHEN 60 THEN 'USA'
            WHEN 90 THEN 'Korea'
            ELSE 'Some other country'
       END,
       [GI brazil].Company,
       [GI brazil].ID,
       [GI brazil].First_name,
       [GI brazil].Family_name,
       [GI brazil].[Hire_ date]
FROM   [GI brazil]
UNION ALL 
SELECT CASE [GI USA].Company
            WHEN 25 THEN 'Brazil'
            WHEN 60 THEN 'USA'
            WHEN 90 THEN 'Korea'
            ELSE 'Some other country'
       END,
       [GI USA].Company,
       [GI USA].ID,
       [GI USA].First_name,
       [GI USA].Family_name,
       [GI USA].[Hire_ date]
FROM   [GI USA]
UNION ALL 
SELECT CASE [GI KOREA].Company
            WHEN 25 THEN 'Brazil'
            WHEN 60 THEN 'USA'
            WHEN 90 THEN 'Korea'
            ELSE 'Some other country'
       END,
       [GI KOREA].Company,
       [GI KOREA].ID,
       [GI KOREA].First_name,
       [GI KOREA].Family_name,
       [GI KOREA].[Hire_ date]
FROM   [GI KOREA]
ORDER BY
       Company,
       Family_name,
       First_name;
Go to Top of Page

Bertrand82
Starting Member

7 Posts

Posted - 10/12/2012 :  09:23:25  Show Profile  Reply with Quote
OK, Thanks for the helt and the first one actually work. But still i need to get the SELECT CASE funtion to work.

I have made the example more simple.

I have a query with one field "Company" in table [GI USA]. The field contains rows with three different vaules. 25,60,90.

I would like to changes those values.

25 = Brazil
60 = USA
90 = KOREA

This is my copied from my SQL view in Access and it doesnt work. What might be the problem?

SELECT [GI USA].Company
FROM [GI USA]
SELECT CASE [GI USA].Company
WHEN 25 THEN 'Brazil'
WHEN 60 THEN 'USA'
WHEN 90 THEN 'Korea'
ELSE 'Some other country'
END

Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 10/12/2012 :  09:44:27  Show Profile  Reply with Quote
The case expression has to be in the select list; i.e., it must be followed by a FROM clause. So this?
SELECT [GI USA].Company,
	CASE [GI USA].Company
				WHEN 25 THEN 'Brazil'
				WHEN 60 THEN 'USA'
				WHEN 90 THEN 'Korea'
				ELSE 'Some other country'
		   END
FROM   [GI USA]
Go to Top of Page

Bertrand82
Starting Member

7 Posts

Posted - 10/15/2012 :  01:18:28  Show Profile  Reply with Quote
thank you!
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.2 seconds. Powered By: Snitz Forums 2000