Professional Web Applications Themes

ORDER BY conflicts with UNION operator - Microsoft SQL / MS SQL Server

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" ...

  1. #1

    Default 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
    CASE orderby WHEN 1 THEN marketname ELSE NULL END ASC,
    CASE orderby WHEN 2 THEN orderbyfield ELSE NULL END DESC

    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.
    JJA Guest

  2. #2

    Default Re: 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:

    ORDER BY SUBSTRING (marketname,1, ABS(orderby - 2) * LENGTH(marketname))
    ASC,
    SUBSTRING (orderbyfield, 1, ABS(orderby - 1) *
    LENGTH(orderbyfield)) DESC

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

    FWIW and totally untested,
    Russell Fields


    "JJA" <com> wrote in message
    news:google.com... 


    Russell Guest

  3. #3

    Default Re: 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
    CASE orderby WHEN 1 THEN T1.marketname ELSE NULL END ASC,
    CASE orderby WHEN 2 THEN T1.orderbyfield ELSE NULL END DESC

    Hope this helps,
    Gert-Jan


    JJA wrote: 
    Gert-Jan Guest

Similar Threads

  1. Conflicts or Known bad ones?
    By Jefferis NoSpamme in forum Macromedia Exchange Dreamweaver Extensions
    Replies: 1
    Last Post: May 10th, 04:34 AM
  2. ORDER BY in UNION query
    By Antony Paul in forum PostgreSQL / PGSQL
    Replies: 4
    Last Post: January 17th, 11:59 AM
  3. 'distinct on' and 'order by' conflicts of interest
    By stephen@thunkit.com in forum PostgreSQL / PGSQL
    Replies: 4
    Last Post: December 31st, 09:51 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139