
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.