Re: sort by decode in union all

From: Alex Filonov <afilonov_at_pro-ns.net>
Date: 2000/05/31
Message-ID: <8h3k83$i6i$1@nnrp1.deja.com>#1/1

In article <9ZbY4.11$CH4.1905_at_news.flash.net>,   "Peter Chang" <jchang_at_flash.net> wrote:
> Hi,
> I have a query like select a.empno empno,
> a.status status,
> a.sallary sal
> from table_a a
> union
> select b.empno empno,
> b.status status,
> b.sallary sal
> from table_b b
> order by 2,1,3 ----------- I know do
> that

>


>                  But how do you do order  by (decode

> &p,1,'status',2,empno,sallary)

>

> Thanks you all

>

You have to put your decode into select clause of both selects in union, then use order by 4. If you don't want that decode to be printed (I suppose it's sqlplus), then you can do:

column ord_col noprint

select a.empno empno,

            a.status status,
            a.sallary sal,
            decode(&p,1,status,2,empno,sallary) ord_col
            from table_a  a

union
select b.empno empno,

          b.status   status,
          b.sallary    sal
          decode(&p,1,status,2,empno,sallary) ord_col

from table_b b
order by 4

I hope sallary and status have the same datatype.

Alex Filonov
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Wed May 31 2000 - 00:00:00 CDT