Re: DECODE

From: Igor V. Podolsky <igoryok_at_soft-review.kiev.ua>
Date: Fri, 4 Feb 2000 12:54:16 +0200
Message-ID: <AA_zgcuS80O@soft-review.kiev.ua>

Chua Boon Yiang <chuaby_at_hotmail.com> wrote in message news:87do47$k2t$1_at_newton.pacific.net.sg...
> Hi,
> in the statement :
> select
> DECODE (deptno,10, 'ACCOUNTING',
> 20, 'RESEARCH',
> 30, 'OPERATION',
> 40, 'OPERATION',
> 'NONE')
> count(*) from tableA
> group by deptno
>
> is there anyway for me to group by the decoded values rather than
> the original stored values ?
> eg i have 2 diff dept numbers, but i want to group them as 1 dept,
> using the above statement, there will be 2 rows of OPERATION.
> col alias cannot be used in the group by.

You'd copy full decode call to group by clause. Just like this:

select

DECODE (deptno,10,      'ACCOUNTING',
                                 20,     'RESEARCH',
                                 30,     'OPERATION',
                                 40,     'OPERATION',
                                         'NONE')
count(*) from tableA
group by DECODE (deptno,10,      'ACCOUNTING',
                                 20,     'RESEARCH',
                                 30,     'OPERATION',
                                 40,     'OPERATION',
                                         'NONE')

If You want avoid code copying, You'd use subquery:

select dep_name, count(*) from
(
select

DECODE (deptno,10,      'ACCOUNTING',
                                 20,     'RESEARCH',
                                 30,     'OPERATION',
                                 40,     'OPERATION',
                                         'NONE') as dep_name

from tableA
)
group by dep_name

But note that indexes can't be used in the both cases. I suppose You'd better create depatments table with code and name columns.

--
Is There A God Or Any Kind Of Justice Under The Sky... (Queen'91)

Igor V. Podolsky (igoryok_at_soft-review.kiev.ua) Received on Fri Feb 04 2000 - 04:54:16 CST