Re: CASE WHEN propagating two columns in a SELECT

On Aug 16, 7:40 am, Carlos <miotromailcar..._at_netscape.net> wrote:
> On 16 ago, 09:23, Björn Wächter <bwc_at_p3-solutionsKILL_SPAM.de> wrote:
>
>
>
>
>
> > Hi all,
>
> > is there a way to propagate two columns with a CASE WHEN
> > statement in a SELECT. What I tried and I think it shows
> > what I want to do is:
>
> > SELECT
> > CASE
> > WHEN TASK_ID > 1 THEN (1,'green')
> > ELSE (0,'red')
> > END AS (COL_A, COLOR)
> > FROM
> > TEST_TABLE
>
> > I can to this by doing this but in a complex query it is
> > not clearly arranged and I always have the same WHEN twice:
>
> > SELECT
> > CASE
> > WHEN TASK_ID > 1 THEN 1
> > ELSE 0
> > END AS COL_A,
> > CASE
> > WHEN TASK_ID > 1 THEN 'green'
> > ELSE 'red'
> > END AS COLOR
> > FROM
> > TEST_TABLE
>
> > Any Ideas?
>
> > Thanks Björn
>
> car..._at_db01.joe> CREATE TABLE TEST_TABLE AS SELECT 0 TASK_ID FROM DUAL
> 2 UNION ALL SELECT 1 TASK_ID FROM DUAL
> 3 UNION ALL SELECT 2 TASK_ID FROM DUAL;
>
> Tabla creada.
>
> car..._at_db01.joe> SELECT* FROM TEST_TABLE;
>
> TASK_ID
> ----------
> 0
> 1
> 2
> car..._at_db01.joe> SELECT COL_A,
> 2 CASE
> 3 WHEN COL_A > 1 THEN 'green'
> 4 ELSE 'red'
> 5 END AS COLOR
> 6 FROM (
> 7 SELECT
> 8 CASE
> 9 WHEN TASK_ID > 1 THEN 1
> 10 ELSE 0
> 11 END AS COL_A
> 12 FROM TEST_TABLE );
>
> COL_A COLOR
> ---------- -----
> 0 red
> 0 red
> 1 red
>
> HTH
>
> Cheers.
>
> Carlos.- Hide quoted text -
>
> - Show quoted text -

I believe you actually meant to code this:

SQL> CREATE TABLE TEST_TABLE AS SELECT 0 TASK_ID FROM DUAL   2 UNION ALL SELECT 1 TASK_ID FROM DUAL   3 UNION ALL SELECT 2 TASK_ID FROM DUAL; Table created.

SQL>
SQL> SELECT* FROM TEST_TABLE;    TASK_ID

         0
         1
         2

SQL>
SQL> SELECT COL_A,
  2 CASE

  3     WHEN COL_A > 0 THEN 'green'
  4     ELSE 'red'

  7     SELECT
  8        CASE
  9           WHEN TASK_ID > 1 THEN 1
 10           ELSE 0
 11        END AS COL_A
 12       FROM TEST_TABLE );

     COL_A COLOR
---------- -----
         0 red
         0 red
         1 green

SQL> COL_A has only two values, 0 and 1, therefore only two condtions can provide the proper results, either COL_A > 0 or COL_A = 1. I chose to code the inequality in case something changed later. Otherwise, nicely done.

David Fitzjarrell Received on Thu Aug 16 2007 - 08:22:33 CDT