On Tue, 03 Jul 2007 17:11:00 -0700, shorti scribbled:
Is there a way to use SELECT to reassign the value of a column so it
displays differently?
For instance, I have a column called status. It is an integer value in
the database (i.e. valid values are 1, 2, 3, 4). I want to display them
for what them mean (i.e. 1 = NORMAL, 2 = DEGRADED, 3 = ERROR, 4 = DEAD).
So I want to display them for the name or maybe an initial (N =
normal).
So if my table looks like this
TABLE1
--------------
NAME STATUS
---------- ------------
ROW1 2
ROW2 3
ROW3 1
ROW4 1
ROW5 4
I would like to see it as
TABLE1
--------------
NAME STATUS
---------- ------------
ROW1 DEGRADED
ROW2 ERROR
ROW3 NORMAL
ROW4 NORMAL
ROW5 DEAD
Is there a way to do this?
Jan and Jeroen have already suggested perfectly valid ways of doing this,
but I thought I'd flesh out the suggestions a bit and demonstrate another
(rather esoteric) way of solving this in DB2's SQL dialect :-)
First, Jan's suggestion of use a CASE expression:
SELECT
NAME,
CASE STATUS
WHEN 1 THEN 'NORMAL'
WHEN 2 THEN 'DEGRADED'
WHEN 3 THEN 'ERROR'
WHEN 4 THEN 'DEAD'
ELSE 'INVALID'
END AS STATUS
FROM
TABLE1;
Note that an ELSE clause is used to ensure that even invalid status codes
will be meaningfully labelled in the result.
Next, Jeroen's suggestion of using a lookup table (which can eliminate
the possibility of an invalid status, i.e. the ELSE clause in the CASE
expression above, by defining a foreign key to the lookup table):
CREATE TABLE STATUSES (
STATUS_CODE INTEGER NOT NULL PRIMARY KEY,
STATUS_LABEL VARCHAR(8) NOT NULL
);
INSERT INTO STATUSES (STATUS_CODE, STATUS_LABEL)
VALUES
(1, 'NORMAL'),
(2, 'DEGRADED'),
(3, 'ERROR'),
(4, 'DEAD');
ALTER TABLE TABLE1
ADD CONSTRAINT STATUS_FK
FOREIGN KEY (STATUS) REFERENCES STATUSES(STATUS_CODE);
SELECT
T1.NAME,
S.STATUS_LABEL AS STATUS
FROM
TABLE1 T1 INNER JOIN STATUSES S
ON T1.STATUS = S.STATUS_CODE;
However, the INSERT syntax above gives a clue for another intriguing
possibility (although not as useful as having the status codes lookup
table): generate the lookup table on the fly...
SELECT
T1.NAME,
S.LABEL AS STATUS
FROM
TABLE1 T1 INNER JOIN (
VALUES
(1, 'NORMAL'),
(2, 'DEGRADED'),
(3, 'ERROR'),
(4, 'DEAD')
) AS S(CODE, LABEL)
ON T1.STATUS = S.CODE;
This isn't quite the same as using the lookup table: there's no foreign
key, hence invalid statuses would be eliminated by the INNER JOIN. This
could be changed to include invalid statuses (like the CASE expression in
the first example) like so:
SELECT
T1.NAME,
COALESCE(S.LABEL, 'INVALID') AS STATUS
FROM
TABLE1 T1 LEFT OUTER JOIN (
VALUES
(1, 'NORMAL'),
(2, 'DEGRADED'),
(3, 'ERROR'),
(4, 'DEAD')
) AS S(CODE, LABEL)
ON T1.STATUS = S.CODE;
Finally, one could make the query a bit neater by using a CTE (common
table expression) instead of a sub-query:
WITH
STATUSES (CODE, LABEL) AS (
VALUES
(1, 'NORMAL'),
(2, 'DEGRADED'),
(3, 'ERROR'),
(4, 'DEAD')
)
SELECT
T1.NAME,
COALESCE(S.LABEL, 'INVALID') AS STATUS
FROM
TABLE1 T1 LEFT OUTER JOIN STATUSES S
ON T1.STATUS = S.CODE;
Cheers,
Dave.
