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.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Switch function

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 = 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;

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
Go to Top of Page

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 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
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2012-10-12 : 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
Go to Top of Page

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 Company
WHEN 25 THEN 'Brazil'
WHEN 60 THEN 'USA'
WHEN 90 THEN 'Korea'
ELSE 'Some other country'
END,
Go to Top of Page

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.


Jim

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

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:
--- 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 - 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 = 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
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'
END
FROM [GI USA]
Go to Top of Page

Bertrand82
Starting Member

7 Posts

Posted - 2012-10-15 : 01:18:28
thank you!
Go to Top of Page
   

- Advertisement -