MS SQL Server, ORDER BY conflicts with UNION operator

JJ
#1 / 3

ORDER BY conflicts with UNION operator

I am stumped with this error msg:
ORDER BY items must appear in the select list if the statement
contains a UNION operator

Here is the body of my stored procedure:

select
OrderByField,
"MarketName" = MarketName,
"SMSACode" = smsacode,
"Statecode" = Statecode,
"CountyCode" = countycode,
"Conv_MarketShare" = Conv_MarketShare,
"Conv_TotalMarketSize" = Conv_TotalMarketSize ,
"Nbr_Mtg" = Nbr_MTg,
"Amt_Mtg" = Amt_Mtg
from #Finaltable FT, #SubsetCounty

WHERE FT.MarketName IS NULL -- SELECT single row comming from
AllLenders

UNION -- now APPEND the body of the resultset per SUBSET allowed
select
OrderByField,
"MarketName" = MarketName,
"SMSACode" = smsacode,
"Statecode" = Statecode,
"CountyCode" = countycode,
"Conv_MarketShare" = Conv_MarketShare,
"Conv_TotalMarketSize" = Conv_TotalMarketSize ,
"Nbr_Mtg" = Nbr_MTg,
"Amt_Mtg" = Amt_Mtg ,
from #Finaltable FT, #SubsetCounty

WHERE FT.Statecode = #SubsetCounty.State_Cd AND FT.CountyCode =
#SubsetCounty.County_Cd
-- and order the final UNIONed result per user choice
ORDER BY

I think this should work and yet I am getting this error. It works if
I remove CASE statements from the ORDER BY and just code ORDER BY
MARKETNAME ASC but that means I'll have to maintain more SELECT
statements just to provide a variation on ordering the final result.

Fri, 06 Jan 2006 18:28:41 GMT

Russell Field
#2 / 3

ORDER BY conflicts with UNION operator

JJA,

I would think that your code is leaving the optimizer unsure if it has the
column. Perhaps you could get rid of the CASE by doing something like this:

ASC,

LENGTH(orderbyfield)) DESC

Maybe add: ",marketname, orderbyfield" after that.

FWIW and totally untested,
Russell Fields

Quote:

> I am stumped with this error msg:
> ORDER BY items must appear in the select list if the statement
> contains a UNION operator

> Here is the body of my stored procedure:

> select
> OrderByField,
> "MarketName" = MarketName,
> "SMSACode" = smsacode,
> "Statecode" = Statecode,
> "CountyCode" = countycode,
> "Conv_MarketShare" = Conv_MarketShare,
> "Conv_TotalMarketSize" = Conv_TotalMarketSize ,
> "Nbr_Mtg" = Nbr_MTg,
> "Amt_Mtg" = Amt_Mtg
> from #Finaltable FT, #SubsetCounty

> WHERE FT.MarketName IS NULL -- SELECT single row comming from
> AllLenders

> UNION -- now APPEND the body of the resultset per SUBSET allowed
> select
> OrderByField,
> "MarketName" = MarketName,
> "SMSACode" = smsacode,
> "Statecode" = Statecode,
> "CountyCode" = countycode,
> "Conv_MarketShare" = Conv_MarketShare,
> "Conv_TotalMarketSize" = Conv_TotalMarketSize ,
> "Nbr_Mtg" = Nbr_MTg,
> "Amt_Mtg" = Amt_Mtg ,
> from #Finaltable FT, #SubsetCounty

> WHERE FT.Statecode = #SubsetCounty.State_Cd AND FT.CountyCode =
> #SubsetCounty.County_Cd
> -- and order the final UNIONed result per user choice
> ORDER BY

> I think this should work and yet I am getting this error. It works if
> I remove CASE statements from the ORDER BY and just code ORDER BY
> MARKETNAME ASC but that means I'll have to maintain more SELECT
> statements just to provide a variation on ordering the final result.

Fri, 06 Jan 2006 20:08:16 GMT

Gert-Jan Stri
#3 / 3

ORDER BY conflicts with UNION operator

Hi JJA,

this is a syntax issue (not an optimizer issue). When using a UNION
(ALL), the ANSI-SQL 92 syntax is used for the ORDER BY clause. This
means, that you can only directly reference columns from the resultset.
It is not possible to use expressions.

One way to work around this, is to make the UNION Select a derived
table. For example:

SELECT T1.*
FROM (
select
OrderByField,
"MarketName" = MarketName,
...
from #Finaltable FT, #SubsetCounty
...
UNION -- now APPEND the body of the resultset per SUBSET allowed
select
OrderByField,
"MarketName" = MarketName,
...
from #Finaltable FT, #SubsetCounty
WHERE FT.Statecode = #SubsetCounty.State_Cd
AND FT.CountyCode = #SubsetCounty.County_Cd
) AS T1
-- and order the final UNIONed result per user choice
ORDER BY

Hope this helps,
Gert-Jan

Quote:

> I am stumped with this error msg:
> ORDER BY items must appear in the select list if the statement
> contains a UNION operator

> Here is the body of my stored procedure:

> select
> OrderByField,
> "MarketName" = MarketName,
> "SMSACode" = smsacode,
> "Statecode" = Statecode,
> "CountyCode" = countycode,
> "Conv_MarketShare" = Conv_MarketShare,
> "Conv_TotalMarketSize" = Conv_TotalMarketSize ,
> "Nbr_Mtg" = Nbr_MTg,
> "Amt_Mtg" = Amt_Mtg
> from #Finaltable FT, #SubsetCounty

> WHERE FT.MarketName IS NULL -- SELECT single row comming from
> AllLenders

> UNION -- now APPEND the body of the resultset per SUBSET allowed
> select
> OrderByField,
> "MarketName" = MarketName,
> "SMSACode" = smsacode,
> "Statecode" = Statecode,
> "CountyCode" = countycode,
> "Conv_MarketShare" = Conv_MarketShare,
> "Conv_TotalMarketSize" = Conv_TotalMarketSize ,
> "Nbr_Mtg" = Nbr_MTg,
> "Amt_Mtg" = Amt_Mtg ,
> from #Finaltable FT, #SubsetCounty

> WHERE FT.Statecode = #SubsetCounty.State_Cd AND FT.CountyCode =
> #SubsetCounty.County_Cd
> -- and order the final UNIONed result per user choice
> ORDER BY

> I think this should work and yet I am getting this error. It works if
> I remove CASE statements from the ORDER BY and just code ORDER BY
> MARKETNAME ASC but that means I'll have to maintain more SELECT
> statements just to provide a variation on ordering the final result.

Fri, 06 Jan 2006 21:05:20 GMT