Error message on using union statement with order by using reserved words

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

signon77 - 30 Oct 2007 11:08 GMT

Hello,

The part of my stored procedure giving me problems is this:
***********************************************************************************************************************************
---------------------------------
-- FINAL OUTPUT - Two Record Sets
---------------------------------
--
-- Trades excluding trades with a corresponding REV/REPs
--
SELECT
d.BookName,
d.ExternalId, -- Deal
d.D1MTM, -- MTM on d-1
d.PoolMTM, -- Pool MTM
d.[Difference], -- Difference
d.Comments -- Comments
FROM
#AllDeals d
WHERE
d.ExternalId NOT IN
(SELECT ExternalID FROM #RevReps
UNION SELECT ExternalID FROM #RevRepDeals)
order by bookname asc, abs([Difference]) desc
--
-- Rev/reps and correspondimg REV/REP unwinds
--
SELECT
rr.BookName,
rr.ExternalId, -- Deal
rr.D1MTM, -- MTM on d-1
rr.PoolMTM, -- Pool MTM
rr.[Difference], -- Difference
rr.Comments -- Comments
FROM
#RevReps rr
UNION SELECT
d.BookName,
d.ExternalId, -- Deal
d.D1MTM, -- MTM on d-1
d.PoolMTM, -- Pool MTM
d.[Difference], -- Difference
d.Comments -- Comments
FROM
#RevRepDeals d
ORDER BY
ExternalId desc, Bookname asc, abs([Difference]) desc

*****************************************************************************************************************

I am trying to order the results of the UNIONs by the absolute value
in the column Difference. 'Diffference' is a reserved word which is
why it appears in square brackets. However every time I run this
stored procedure I get the following error message:

"ORDER BY items must appear in the select list if the statement
contains a UNION operator"

As 'Difference' is clearly being selected why am I getting this error
message?

Rob

Reply to this Message

Roy Harvey (SQL Server MVP) - 30 Oct 2007 12:45 GMT

> ORDER BY
> ExternalId desc, Bookname asc, abs([Difference]) desc
[quoted text clipped - 11 lines]
>As 'Difference' is clearly being selected why am I getting this error
>message?

Because what you are trying to ORDER BY is an expression,
abs([Difference]), not a column in the result set. You can get away
with an expression with a single SELECT, but not when using UNION.

To get that order you would have to either add a column with that
expression to each SELECT in the UNION, or place the entire query
inside a derived table and ORDER BY in the outer query:

SELECT *
FROM (<your query here without ORDER BY>)
ORDER BY ExternalId desc, Bookname asc, abs([Difference]) desc

Roy Harvey
Beacon Falls, CT

Reply to this Message

signon77 - 30 Oct 2007 17:44 GMT

Hi Roy,

Thanks for your help with this. My code now works looking like this:

        SELECT
    rr.BookName,
    rr.ExternalId, -- Deal
    rr.D1MTM, -- MTM on d-1
    rr.PoolMTM, -- Pool MTM
    abs(rr.[Difference]), -- Difference
    rr.Comments -- Comments
    FROM
    #RevReps rr
         UNION SELECT
    d.BookName,
    d.ExternalId, -- Deal
    d.D1MTM, -- MTM on d-1
    d.PoolMTM, -- Pool MTM
    abs(d.[Difference]), -- Difference
    d.Comments -- Comments
    FROM
    #RevRepDeals d
ORDER BY ExternalId desc, BookName asc, abs([Difference]) desc

Thanks again!!

Robert Ilechuku

Reply to this Message

--CELKO-- - 31 Oct 2007 15:07 GMT

>> 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!

Reply to this Message