Posted by signon77 on 10/30/07 10:08

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

[Back to original message]