Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
Bertrand82
Starting Member
7 Posts |
Posted - 2012-10-12 : 06:44:14
|
Hi, I have a union query. With the following code. I would like the first column "company" to switch some values to text.25 = Brazil60 = USA90 = KOREAWhat will the SQL code be and how should i put it together with the rest of my code as below?Br BertrandSELECT [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; |
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-10-12 : 06:59:35
|
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 |
|
|
Bertrand82
Starting Member
7 Posts |
Posted - 2012-10-12 : 07:22:27
|
Hi and thank you,Unfortunately I get "characters found after SQL statement"this is my code in my union querySELECT [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, |
|
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2012-10-12 : 07:48:00
|
You have an extra comma at the end of your statementELECT CASE CompanyWHEN 25 THEN 'Brazil'WHEN 60 THEN 'USA'WHEN 90 THEN 'Korea'ELSE 'Some other country'END ,JimEveryday I learn something that somebody else already knew |
|
|
Bertrand82
Starting Member
7 Posts |
Posted - 2012-10-12 : 08:09:02
|
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 CompanyWHEN 25 THEN 'Brazil'WHEN 60 THEN 'USA'WHEN 90 THEN 'Korea'ELSE 'Some other country'END, |
|
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2012-10-12 : 08:13:39
|
And it still has the comma in it. You really should remove it. Remove the semi-colons as well. JimEveryday I learn something that somebody else already knew |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-10-12 : 08:13:43
|
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:--- 1SELECT '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;--- 2SELECT 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; |
|
|
Bertrand82
Starting Member
7 Posts |
Posted - 2012-10-12 : 09:23:25
|
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 = Brazil60 = USA90 = KOREAThis is my copied from my SQL view in Access and it doesnt work. What might be the problem?SELECT [GI USA].CompanyFROM [GI USA]SELECT CASE [GI USA].Company WHEN 25 THEN 'Brazil' WHEN 60 THEN 'USA' WHEN 90 THEN 'Korea' ELSE 'Some other country' END |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-10-12 : 09:44:27
|
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' ENDFROM [GI USA] |
|
|
Bertrand82
Starting Member
7 Posts |
Posted - 2012-10-15 : 01:18:28
|
thank you! |
|
|
|
|
|
|
|