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

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 Received on Tue Oct 26 2004 - 05:19:55 CDT