0

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?

3
Contributors
4
Replies
5
Views
11 Years
Discussion Span
Last Post by Sulley's Boo
1

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?

Votes + Comments
agreed
0

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.

0

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.

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

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.