Posted by --CELKO-- on 10/31/07 14:07

>> I am trying to order the results of the UNIONs by the absolute value
in the column Difference. 'Difference' is a reserved word which is
why it appears in square brackets. <<

The Standard SQL convention is to use double quotes, not proprietary
brackets. Likewise, the Standard SQL convention is that columns in a
UNION result do not have names; you have to give them names in an AS
clause. Finally, the Standard SQL convention is that the ORDER BY
clause reference column names in the SELECT clause of the cursor, not
expressions.

SELECT X.book_name, X.external_id, X.d1mtm, X.poolmtm,
X.difference_abs, X.comments
FROM (#Revreps
UNION
SELECT book_name, external_id, d1mtm, poolmtm,
ABS("difference"), comments
FROM #RevrepDeals
) AS X (book_name, external_id, d1mtm, poolmtm, difference_abs,
comments)
ORDER BY external_id DESC, book_name ASC, difference_abs DESC;

A little minor effort and you have portable SQL instead of dialect!

[Back to original message]