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

Mark.Powell_at_eds.com (Mark D Powell) wrote in message news:<2687bb95.0410260736.62da59a8_at_posting.google.com>...
> 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 --

Mark,

I know this would work but I don't want to have the abs function in the SELECT list as it would over complicate my design. I have two procedures: One that generates queries dynamically and another one that forms the order by clause which is appended to the query depending on the parameters passed to it (alias name,abs or standard,asc or desc...n times). The problem is that sometimes the query needs to use unions and other times not. When no unions are required I can use abs in the order by. With unions it breaks. If I implement your suggestion, my main procedure would have to parse the orderby parameter, check to see if the query needs to return the abs version of the column, add two columns in the select list, one with abs(column) and another just column, wrap everything with a select that shows only the fields I want to show etc... This is inconsistent with what I alrady have which works for non-union queries and too much hassle just because I cannot use abs with unions.

Dimitri Received on Wed Nov 03 2004 - 08:48:29 CST