Re: why is order by silly in pl/sql?

Hi Chris,

it's because you are using a union. This is the only time when positional references are required in an order by. The problem is that the retrieved attributes from each query of the union can be different names from different tables, only the datatypes must match query to query.

For example
select to_char(56+78) a_number
from dual
UNION
select dummy
from dual

Therefore the names used in the first query would not be truly meaningful as the resulting set could be a combination of many attribute names and the ordering would have to be internally equated to positional references. [ I think :-) ]

regards

Rod Corderey

Lane Associates
Lane_Associates_at_Compuserve.com
http://www.Lane-Associates.com
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
>
> See Ya
> (when bandwidth gets better ;-)
>
> Chris Eastwood
> Photographer, Programmer email ua.ude.ug.cti_at_doowtsae.c
> Motorcyclist and dingbat WWW http://chrise.itc.gu.edu.au
>
> ************************************************************************
> Ohhh ... you work all day, slave over a hot stove all night
> yet you *still* have time for sadomasochism ... how do you do it?
>
> A little man hurts me.
>
> <ding>
> Oh Mister Mean ... Oh Mister Mean
> 15 Minutes with him and you'll be blue and green
> Studded belts and leather whips
> Bondage trousers on his hips
> Wack and punish as you dust with Mister Mean
> Ohhhh he whips me around the house in minutes ... lets get cracking ...
Received on Wed May 06 1998 - 11:53:15 CEST