Re: Oracle 8i UNION problem with ORDER BY clause using the ABS function

jose.cuervo_at_lycos.co.uk (Dimitri) wrote in message news:<2452bd93.0410260219.1052f951_at_posting.google.com>...
> Hello Oracle freaks!
>
> I need your help. I'm trying to execute a query like this:
>
> SELECT fieldA,fieldB,fieldC FROM tableA
> UNION
> SELECT fieldA,fieldB,fieldC FROM tableB
> ORDER BY ABS(fieldA) DESC
>
> I recieve an ORA-01785: ORDER BY item must be the number of a SELECT
> -list expression
>
> The union works fine if I remove the order by clause.
> According to some posts the SQL92 standard stipulates that when you
> have a union you need to use numbers in the order by clause to
> indicate the column by which you want to order by like this:
>
> SELECT fieldA,fieldB,fieldC FROM tableA
> UNION
> SELECT fieldA,fieldB,fieldC FROM tableB
> ORDER BY 1 DESC
>
> This works fine, but what if I want the abs of column 1?
> Obviously ORDER BY ABS(1) DESC is not accepted.
>
> PS: Preferably I want to use the column names rather than column
> numbers since both union queries have the same column names and I
> don't want to rewrite a function I've written that forms the order by
> clause.
>
> Any help would be appreciated.
>
> Dimitri

How about adding the order by criteria to the query so you can sort on it and then filtering it out of the final select of the data?

UT1 > select * from marktest;

FLD1 FLD2 FLD3 FLD4

---------- ---------- --------- --------
Rec                 9 08-OCT-04 Test Rec
middle              2 26-OCT-04 ok
ROWTYPE             9 08-OCT-04 Test Rec

UT1 > get mark
  1 select fld2 from (
  2 select fld2, abs(fld2)
  3 from marktest
  4 union
  5 select fld2 * -1, abs(fld2 * -1)
  6 from marktest
  7 order by 2
  8* )
UT1 > /

      FLD2

        -2
         2
        -9
         9

HTH -- Mark D Powell -- Received on Tue Oct 26 2004 - 10:36:39 CDT