Prev: Solution: Invalid STDGMEDIUM structure error in DTS package
Next: Dynamic date range for each row?
From: signon77 on 30 Oct 2007 06: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

From: Roy Harvey (SQL Server MVP) on 30 Oct 2007 07:45
On Tue, 30 Oct 2007 03:08:23 -0700, signon77 <signon77(a)yahoo.com>
wrote:

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

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
From: signon77 on 30 Oct 2007 12:44
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

From: --CELKO-- on 31 Oct 2007 10: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!

 | 
Pages: 1
Prev: Solution: Invalid STDGMEDIUM structure error in DTS package
Next: Dynamic date range for each row?