Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: CASE WHEN propagating two columns in a SELECT
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
3 WHEN COL_A > 1 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 red
HTH Cheers.
Carlos. Received on Thu Aug 16 2007 - 07:40:50 CDT