Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> 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.Received on Tue Aug 13 2002 - 14:57:52 CDT