MS SQL

MS SQL RSS

Altering data when reading it from a database

Please support our MS SQL advertiser: Intel Parallel Studio Home

•

Join Date: Dec 2004

Posts: 149

Reputation: Geek-Master is an unknown quantity at this point

Solved Threads: 6

Geek-Master Geek-Master is offline

Offline

Junior Poster

Altering data when reading it from a database

Nov 15th, 2006

Let's say I have a column within a table that contains codes {0,1,2,3}. These codes are for what color shirt are in stock. We can say 0 is for black, 1 is for white, 2 is for red, and 3 is for blue.

Then I run a SELECT query on this table and want to change the code to a more user friendly description like red or blue. This will be used for a View. How can I alter the data when it is read in?

If in doubt, reach into the trash can and remove the user guide.

•

Join Date: Sep 2006

Posts: 44

Reputation: MCP is an unknown quantity at this point

Solved Threads: 3

MCP

Offline

Light Poster

Re: Altering data when reading it from a database

Nov 16th, 2006

Well, the bad way would be to do something like:

select case color when 0 then 'black' when 1 then 'white' when 2 then 'red' when 4 then 'blue' end as color from myTable

ideally, you would create a table with 2 columns, one an id {0,1,2,3} and another with the name, and then you would just join (outer join if you have nulls in your base table). This will allow you to expand your colour offerings by just adding rows to the color table, as opposed to modifying your view, or views if you will hardcode this in more than one place.

•

Originally Posted by Geek-Master

Let's say I have a column within a table that contains codes {0,1,2,3}. These codes are for what color shirt are in stock. We can say 0 is for black, 1 is for white, 2 is for red, and 3 is for blue.

Then I run a SELECT query on this table and want to change the code to a more user friendly description like red or blue. This will be used for a View. How can I alter the data when it is read in?

•

Join Date: Dec 2004

Posts: 149

Reputation: Geek-Master is an unknown quantity at this point

Solved Threads: 6

Geek-Master Geek-Master is offline

Offline

Junior Poster

Re: Altering data when reading it from a database

Nov 16th, 2006

The project I'm working on involves using a vendor made database. So I don't have an option of editing their structures. The case statement is probably my best bet.

So when you use the "select case color" statement, color is a column from the "physical" table right? And not just a temporary column created just for the case statement.

If in doubt, reach into the trash can and remove the user guide.

•

Join Date: Sep 2006

Posts: 44

Reputation: MCP is an unknown quantity at this point

Solved Threads: 3

MCP

Offline

Light Poster

Re: Altering data when reading it from a database

Nov 17th, 2006

•

Originally Posted by Geek-Master

So when you use the "select case color" statement, color is a column from the "physical" table right? And not just a temporary column created just for the case statement.

Yup, that's correct, color is the "physical" name of the column.

•

Join Date: Dec 2004

Posts: 440

Reputation: Sulley's Boo will become famous soon enough

Solved Threads: 10

Sulley's Boo Sulley's Boo is offline

Offline

Posting Pro in Training

Re: Altering data when reading it from a database

Nov 21st, 2006

 Help with Code Tags 
 MS SQL Syntax (Toggle Plain Text) 
 
SQL> CREATE TABLE colors(color_id number(1));
TABLE created
 
SQL> INSERT INTO colors VALUES(1);
1 row inserted
 
SQL> INSERT INTO colors VALUES(2);
1 row inserted
 
SQL> INSERT INTO colors VALUES(3);
1 row inserted
 
SQL> commit;
 
Commit complete
 
SQL> SELECT * FROM colors;
COLOR_ID
--------
1
2
3
 
SQL> SELECT decode(color_id,1,'Red',2,'Blue',3,'Green') FROM colors;
 
DECODE(COLOR_ID,1,'RED',2,'BLU
------------------------------
Red
Blue
Green
 
 
SQL> create table colors(color_id number(1));
Table created
 
SQL> insert into colors values(1);
1 row inserted
 
SQL> insert into colors values(2);
1 row inserted
 
SQL> insert into colors values(3);
1 row inserted
 
SQL> commit;
 
Commit complete
 
SQL> select * from colors;
COLOR_ID
--------
1
2
3
 
SQL> select decode(color_id,1,'Red',2,'Blue',3,'Green') from colors;
 
DECODE(COLOR_ID,1,'RED',2,'BLU
------------------------------
Red
Blue
Green