ORDER BY conflicts with UNION operator 
Author Message
 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
 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
 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
 
 [ 3 post ] 

 Relevant Pages 

1. ORDER By CASE @SortField.... - with UNION operator

2. UNION Set Operator & Group/Order by

3. UNION operator

4. UNION operator and CREATE VIEW help, please.....

5. UNION Operator and VIEWS

6. Error 104: Using the UNION operator

7. XPath Union Operator and Projection: any update?

8. XPath Union operator

9. Need help with the union operator

10. Do mapping schema queries support the union operator?

11. Using The UNION Operator

12. more than one union or intersect operator


 
Powered by phpBB® Forum Software