| Author |
 Topic  |
|
|
Bertrand82
Starting Member
7 Posts |
 Posted - 10/12/2012 : 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 = 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
5152 Posts |
Posted - 10/12/2012 : 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 - 10/12/2012 : 07:22:27
|
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, |
|
|
|
jimf
Flowing Fount of Yak Knowledge
USA
2868 Posts |
Posted - 10/12/2012 : 07:48:00
|
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 |
|
|
|
Bertrand82
Starting Member
7 Posts |
Posted - 10/12/2012 : 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 Company
WHEN 25 THEN 'Brazil'
WHEN 60 THEN 'USA'
WHEN 90 THEN 'Korea'
ELSE 'Some other country'
END, |
|
|
|
jimf
Flowing Fount of Yak Knowledge
USA
2868 Posts |
Posted - 10/12/2012 : 08:13:39
|
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 |
|
|
|
sunitabeck
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 10/12/2012 : 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:--- 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; |
|
|
|
Bertrand82
Starting Member
7 Posts |
Posted - 10/12/2012 : 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 = 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
|
|
|
|
sunitabeck
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 10/12/2012 : 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'
END
FROM [GI USA] |
|
|
|
Bertrand82
Starting Member
7 Posts |
Posted - 10/15/2012 : 01:18:28
|
| thank you! |
|
|
| |
Topic |
|
|
|
Switch function
