Re: Dynamic Order By with Union

On 13 Aug 2002 09:13:48 -0700, joseph_m_miller_at_yahoo.com (Joseph Miller) wrote:

>I'm having trouble using a DECODE statement in the ORDER BY clause
>with a UNION ALL in PL/SQL.

...
>But this doesn't work:
>
>SELECT InvoiceNumber
>FROM Invoice
>WHERE BuyerName = 'x'
>UNION ALL
>SELECT InvoiceNumber
>FROM Invoice
>WHERE SellerName = 'y'
>ORDER BY DECODE(inOrderByVariable, 'invoiceNumber', 1);
>
>any ideas? thanks. Joe

The value from DECODE will be interpreted as a data value, not a column number. So you are ordering by the literal value 1, which will probably make the output come out in arbitrary order.  

When combining complicated queries with ORDER BY, I usually bundle the whole thing into a subselect to avoid problems with column names/numbers. So something like:

select InvoiceNumber from
(
...select with lots of unions...
)
order by ...

And since you've firmly established that the name of column 1 is InvoiceNumber for all result rows, you can order by DECODE(inOrderByVariable, 'invoiceNumber', InvoiceNumber).

You probably don't strictly need the subselect to do this, but I usually feel more comfortable this way, in case some of the union columns are results from SUBSTR() etc.

John

--
Got an Oracle database question?
Try the search engine for the database docs at:
http://tahiti.oracle.com/

The opinions expressed above are mine and do not
necessarily reflect those of Oracle Corporation.