ORDER BY items must appear in the select list if the statement contains a UNION operator.

Last Post 19 Aug 2009 01:13 AM by shijobaby. 4 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages
Jack_Kaye
New Member
New Member

--
15 Jan 2008 11:42 AM
I am trying to utilize a UNION join between 3 datasets (i.e. from 3 companies) and 2 of the companies require currency conversion. The conversion information is housed within a table within each company database.

However, when I try to input an ORDER BY clause in the currency conversion (so it will select the last entry in the currency list), I receive the above referenced error.

Here is the code that I'm using so far and it presently is only joining 2 companies (the US and the CANADA companies)

declare @StartDate varchar(10)
declare @EndDate varchar(10)
set @StartDate = '01/01/2007'
set @EndDate = '01/31/2007'

select
NavCompany = 'US'
,us.[Customer No_]
,us.[Charge-to Department Code]
,us.[Gen_ Prod_ Posting Group]
,us.[Total Cost] as 'LocCost'
,us.[Total Cost] as 'USDCost'
from
BIGBUCKS.NAVLIVE.dbo.[Blanchard US$Res_ Ledger Entry] us
where
us.[Posting Date] >= @StartDate and us.[Posting Date] <= @EndDate
and us.[Post Cost of Sales] = 1
and us.[Entry Type] = 0
union all
select
NavCompany = 'CAN'
,can.[Customer No_]
,can.[Charge-to Department Code]
,can.[Gen_ Prod_ Posting Group]
,can.[Total Cost] as 'LocCost'
,'USDCost' =
(
can.[Total Cost]/
(
select top 1 ex.[Exchange Rate Amount]
from BIGBUCKS.NAVLIVE.dbo.[Blanchard US$Currency Exchange Rate] ex
where
(
month(ex.[Starting Date]) = month(can.[Posting Date])
and year(ex.[Starting Date]) = year(can.[Posting Date])
)
and ex.[Currency Code] = 'CAN'
order by ex.[Starting Date] desc
)
)
from
BIGBUCKS.NAVLIVE.dbo.[Blanchard CANADA$Res_ Ledger Entry] can
where
can.[Posting Date] >= @StartDate and can.[Posting Date] <= @EndDate
and can.[Post Cost of Sales] = 1
and can.[Entry Type] = 0
order by
NavCompany
,[Customer No_]
,[Charge-to Department Code]
,[Gen_ Prod_ Posting Group]


SQLUSA
New Member
New Member

--
15 Jan 2008 10:05 PM
What if you enclose the offending SELECT in parenthesis?

Kalman Toth - Database, Data Warehouse & Business Intelligence Architect
SQLUSA: http://www.sqlusa.com/order2005repo...gservices/ The Best SQL Server 2005 Training in the World!
nosepicker
New Member
New Member

--
16 Jan 2008 10:39 AM
It's actually the ORDER BY in your subquery (where you do the SELECT TOP 1 ...) that is causing the problem. See if this works out for you:


select
NavCompany = 'US'
,us.[Customer No_]
,us.[Charge-to Department Code]
,us.[Gen_ Prod_ Posting Group]
,us.[Total Cost] as 'LocCost'
,us.[Total Cost] as 'USDCost'
from BIGBUCKS.NAVLIVE.dbo.[Blanchard US$Res_ Ledger Entry] us
where us.[Posting Date] >= @StartDate
and us.[Posting Date] <= @EndDate
and us.[Post Cost of Sales] = 1
and us.[Entry Type] = 0

union all

select
NavCompany = 'CAN'
,can.[Customer No_]
,can.[Charge-to Department Code]
,can.[Gen_ Prod_ Posting Group]
,can.[Total Cost] as 'LocCost'
,'USDCost' =
( can.[Total Cost]/
(SELECT MAX([Exchange Rate Amount])
FROM BIGBUCKS.NAVLIVE.dbo.[Blanchard US$Currency Exchange Rate] AS ex3
JOIN
(select MAX(ex.[Starting Date]) AS Max_Starting_Date
from BIGBUCKS.NAVLIVE.dbo.[Blanchard US$Currency Exchange Rate] ex
where ( month(ex.[Starting Date]) = month(can.[Posting Date])
and year(ex.[Starting Date]) = year(can.[Posting Date]) )
and ex.[Currency Code] = 'CAN') AS ex2
ON ex3.[Starting Date] = ex2.Max_Starting_Date)
)
from BIGBUCKS.NAVLIVE.dbo.[Blanchard CANADA$Res_ Ledger Entry] can
where can.[Posting Date] >= @StartDate
and can.[Posting Date] <= @EndDate
and can.[Post Cost of Sales] = 1
and can.[Entry Type] = 0
order by
NavCompany
,[Customer No_]
,[Charge-to Department Code]
,[Gen_ Prod_ Posting Group]
Jack_Kaye
New Member
New Member

--
17 Jan 2008 07:30 AM
Thanks for the suggestions nosepicker, that worked out exactly as I needed.
shijobaby
New Member
New Member

--
19 Aug 2009 01:13 AM
Hi

The reasons and ways to avoid this error have discussed in this

site with good examples. By making small changes in the query






Acceptable Use Policy
---