Re: [Q] Why cant I ORDER BY on a SELECT...UNION in PL/SQL

From: Jurij Modic <jurij.modic_at_mf.sigov.mail.si>
Date: 1998/02/23
Message-ID: <34f1977f.25140739@www.sigov.si>#1/1

On Mon, 23 Feb 1998 13:21:50 -0000, "Quentin North" <quentinn_at_epic.co.uk> wrote:

>Why wont this work in pl/sql (but will otherwise):
>
>select col1,col2 from tab1
>union
>select col1,col2 from tab2
>order by col1;
>
>Get message PLS-00423: ORDER BY item must be the number of a SELECT-list
>expression
>
>Oracle support in the UK dont appear to know why it wont work. Everything
>works until you add the order by clause.

As the message tells, you can not reference columns in ORDER BY of the UNION queries by their names, you must use the positional notation, e.g.

select col1,col2 from tab1
union
select col1,col2 from tab2
order by 1; -- orderd by the first column of the result set

• (tab1.col1 and tab2.col2)

This is logical because the coresponding columns in different selects of the UNION can have different names, so you must reference them by their position in the column list of the select.

Regards,

---

Jurij Modic                             Republic of Slovenia
jurij.modic_at_mf.sigov.mail.si		Ministry of Finance
============================================================

The above opinions are mine and do not represent any official standpoints of my employer Received on Mon Feb 23 1998 - 00:00:00 CST