Re: why is order by silly in pl/sql?
Date: Wed, 06 May 1998 12:17:02 +0100
Message-ID: <6ipd91$9et_at_news.Informatik.Uni-Oldenburg.DE>
Chris Eastwood wrote:
> HiYa
>
> I am wanting to do a cursor that has a union of two selects:
>
> select cl.ORDER_CREATED_BY who,
> cl.COMMIT_AMOUNT amt,
> cl.COMMITMENT_DESCRIPTION descr,
> cl.MERCHANT_NAME shop,
> pu.EMAIL_ADDRESS email,
> 'COMMITMENT' type
> [blurg]
> UNION
> select ca.USER_NAME who,
> sd.AMOUNT amt,
> sd.TRANSACTION_DESCRIPTION descr,
> sd.MERCHANT_NAME shop,
> pu.EMAIL_ADDRESS email,
> 'STATEMENT' type
> [blah]
> order by who,type;
>
> but it seems that I have to to
> order by 1, 6;
>
> HUH?? is there a reason for this? I would be interested to know this
ca.USER_NAME is different from cl.ORDER_CREATED_BY and the
union only works 'cause of your column renaming to "who".
So ORACLE has to sort after selecting and joining and now you can only
reference by column no.
Remove the UNION ... -part ant the who and make an
"order by cl.ORDER_CREATED_BY" and you'll see it works.
Received on Wed May 06 1998 - 13:17:02 CEST