Re: CASE WHEN propagating two columns in a SELECT

From: Carlos <miotromailcarlos_at_netscape.net>
Date: Thu, 16 Aug 2007 12:40:50 -0000
Message-ID: <1187268050.431774.99840@g4g2000hsf.googlegroups.com>

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

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

carlos_at_db01.joe> SELECT* FROM TEST_TABLE;

   TASK_ID

---

         0
         1
         2

carlos_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. Received on Thu Aug 16 2007 - 07:40:50 CDT