Re: dificult SQL

In article <93fc5f$r03$1_at_nnrp1.deja.com>,   chemarey_at_my-deja.com wrote:
> Hi. I'd like to know how can i order the elements of a query with
 UNION.
>
> For example:
>
> select customer,inc from table1
> UNION
> select customer,inc from table2
>
> The idea is order by the inc item, but DBMS returns an error if a put
> the order by clause in the two different selects like
>
> select customer,inc from table1 order by inc asc
> UNION
> select customer,inc from table2 order by inc asc.
>
> Do you know if is possiblke to do this and how can i do.
>
> Thanks.
>
> Sent via Deja.com
> http://www.deja.com/
>

It is extremely important to read the manual. Searching the SQL Reference manual for 'Order By'

Expressions, Conditions, and Queries 5-21

Sorting Query Results

You can use the ORDER BY clause to order the rows selected by a query. Sorting by position is useful in the following cases: <> To order by a lengthy select list expression, you can specify its position, rather than duplicate the entire expression, in the ORDER BY clause.
<> For compound queries (containing set operators UNION, INTERSECT, MINUS,or UNION ALL), the ORDER BY clause must use positions, rather than explicit expressions. Also, the ORDER BY clause can appear only in the last component query. The ORDER BY clause orders all rows returned by the entire compound query.

The mechanism by which Oracle sorts values for the ORDER BY clause is specified either explicitly by the NLS_SORT initialization parameter or implicitly by the NLS_LANGUAGE initialization parameter. For information on these parameters, see Oracle8i National Language Support Guide.

Thus your example is :

  select customer,inc from table1
  UNION
  select customer,inc from table2
  order by 1,2

--
Michael Krolewski
Rosetta Inpharmatics
mkrolewski_at_rii.com
              Usual disclaimers



Sent via Deja.com
http://www.deja.com/