Query help...

[shorti]

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?

[Jeroen van den Broek]

Re: Query help...

On Jul 4, 2:11 am, shorti <lbrya...@juno. comwrote:

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?

As an alternative to the solution provided by Jan, you may consider
defining a Status 'Lookup' table, containing status_id and
status_desc, and joining this with your original table.
This way it will probably be easier to add additional status values.
Depending on your environment, it may have performance consequences
(positive or negative).

HTH.

--
Jeroen

[Dave Hughes]

Re: Query help...

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.LABE L, '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.LABE L, 'INVALID') AS STATUS
FROM
TABLE1 T1 LEFT OUTER JOIN STATUSES S
ON T1.STATUS = S.CODE;


Cheers,

Dave.

[shorti]

Re: Query help...

Thanks for all your help!!