Dynamic Order By with Union

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 Received on Tue Aug 13 2002 - 11:13:48 CDT