Re: UNION

Hamid Alavi wrote:
>
> Hi,
>
> I try to use union and order by first column of first select statment and
> also first column of second select statment but get error, Any Idea how to
> do this??
>
> SELECT A,B,C FROM TABLEABC
> UNION
> SELECT D,E,F FROM TABLEDEF
> ORDER BY A,D
>
> Hamid Alavi
> Office 818 737-0526
> Cell 818 402-1987
>

Hamid,

  Remember that the purpose of a union is to bring back rows from several tables as if they were coming from a single table - a bit like a join returns columns from several tables as if they were coming from a single table. By convention, the column names which are assigned come from the first table in the union. In your example, columns will be named (A,B,C) even if actually the first part of the UNION returns no row. Syntactically, to order the output of a union you must specified column by position number in the select list, not by name - on your example, it will be 'order by 1' ('1' refering to A or D indistinctly).

If you always want rows from TABLEABC to be returned before rows from TABLEDEF, you must cheat and add a dummy column :

SELECT 1 dummy, A, B, C FROM TABLEABC
UNION
SELECT 2, D, E, F FROM TABLEDEF
ORDER BY 1, 2 (you can make the dummy column disappear from the output with SQL*Plus by defining

   col dummy noprint
In a program, just ignore it).
Note that there is a drawback to the dummy column use: with a standard UNION (as opposed to UNION ALL) if a row in the first table is strictly identical to a row in another table from the UNION, it appears only once (duplicates are eliminated). With a dummy column, only duplicates from the same table can be removed.

HTH Stephane Faroult
Oriole Ltd

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Stephane Faroult
  INET: sfaroult_at_oriole.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).