Re: Dynamic Order By with Union

From: Sybrand Bakker <postbus_at_sybrandb.demon.nl>
Date: Tue, 13 Aug 2002 19:48:37 +0200
Message-ID: <mihilu08f9gdahqvnk9mnnrkhipch9jl9i@4ax.com>

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. Here's a simple example:
>
>This works:
>
>SELECT InvoiceNumber
>FROM Invoice
>WHERE BuyerName = 'x'
>ORDER BY DECODE(inOrderByVariable, 'invoiceNumber', 1);
>
>And this works:
>
>SELECT InvoiceNumber
>FROM Invoice
>WHERE BuyerName = 'x'
>UNION ALL
>SELECT InvoiceNumber
>FROM Invoice
>WHERE SellerName = 'y'
>ORDER BY 1;
>
>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

Specify 'doesn't work' or do you require us to guess the error?

Regards

Sybrand Bakker, Senior Oracle DBA

To reply remove -verwijderdit from my e-mail address Received on Tue Aug 13 2002 - 12:48:37 CDT