Ad:

MS SQL Discussion Thread
Unsolved
Views: 2585

Nov 15th, 2006

Altering data when reading it from a database

Expand Post »

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?

Similar Threads

Extracting data from large SINGLE-table database to MULT-table relational database in MySQL
Read data from Access Database through PHP in PHP
data sorts in reverse order!! help!! in C
How to Hash and Store in Database in ASP.NET
Filtering data from an access database in Visual Basic 4 / 5 / 6

Geek-Master

Reputation Points: 12

Solved Threads: 6

Junior Poster

Offline

156 posts
since Dec 2004

Permalink

Nov 16th, 2006

Re: Altering data when reading it from a database

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.

Click to Expand / Collapse Quote 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?

MCP

Reputation Points: 14

Solved Threads: 3

Light Poster

Offline

44 posts
since Sep 2006

Permalink

Nov 16th, 2006

Re: Altering data when reading it from a database

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.

Geek-Master

Reputation Points: 12

Solved Threads: 6

Junior Poster

Offline

156 posts
since Dec 2004

Permalink

Nov 17th, 2006

Re: Altering data when reading it from a database

Click to Expand / Collapse Quote 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.

MCP

Reputation Points: 14

Solved Threads: 3

Light Poster

Offline

44 posts
since Sep 2006

Permalink

Nov 21st, 2006

Re: Altering data when reading it from a database

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

Is this what you wanted?

If no then just ignore :o Just wanted to help ..

All the best ..

Sulley's Boo

Reputation Points: 529

Solved Threads: 10

Posting Pro in Training

Offline

450 posts
since Dec 2004

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in MS SQL Forum Timeline: SQL Queries/Report

Next Thread in MS SQL Forum Timeline: generate database maintenence plan script

DaniWeb Message

Cancel Changes

User Name
Password