Altering data when reading it from a database

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]

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.

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?

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.

[MCP]

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.

[Sulley's Boo]

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 ..