Problems converting Access UNION query to SQL

I've got a Union query that works in Access but I'm unable to get it to work
querying a SQL Server. Access was using linked tables so the data was
really coming from SQL anyway. I want to make this query work within
Reporting Services, but I'm receiving the following error message:
ADO error: ORDER BY items must appear in the select list if the statement
contains a UNION operator.
I was able to take the two Select statements from Access and make good SQL
queries with them, but I'm unable to UNION them together eventhough it works
fine in Access. Here's my two queries I'm trying to Union:
SELECT TOP 100 PERCENT dbo.SOP30200.SLPRSNID, dbo.SOP30200.DOCAMNT
FROM dbo.SOP30200 LEFT OUTER JOIN
dbo.RM00101 ON dbo.SOP30200.CUSTNMBR =
dbo.RM00101.CUSTNMBR
WHERE (dbo.SOP30200.DOCDATE >= CONVERT(DATETIME, '2006-02-15 00:00:00',
102)) AND (dbo.SOP30200.DOCDATE <= '2006-03-01') AND
(dbo.SOP30200.SOPTYPE = 3) AND (dbo.SOP30200.VOIDSTTS
= 0) AND (dbo.SOP30200.DOCID = 'inv')
ORDER BY dbo.SOP30200.SLPRSNID, dbo.SOP30200.CUSTNMBR
UNION
SELECT dbo.SOP30200.SLPRSNID, - (1 * dbo.SOP30200.DOCAMNT) AS expReturns
FROM dbo.SOP30200 LEFT OUTER JOIN
dbo.RM00101 ON dbo.SOP30200.CUSTNMBR =
dbo.RM00101.CUSTNMBR LEFT OUTER JOIN
dbo.viewSalesperson ON dbo.RM00101.SLPRSNID =
dbo.viewSalesperson.SLPRSNID
WHERE (dbo.SOP30200.DOCDATE >= CONVERT(DATETIME, '2006-02-15 00:00:00',
102)) AND (dbo.SOP30200.DOCDATE <= CONVERT(DATETIME,
'2006-03-01 00:00:00', 102)) AND
(dbo.SOP30200.VOIDSTTS = 0) AND (dbo.SOP30200.SOPTYPE = 4) AND
(dbo.SOP30200.DOCID = 'returns')order by of the first part of the union should be the problem.
Colin wrote:
>I've got a Union query that works in Access but I'm unable to get it to wor
k
>querying a SQL Server. Access was using linked tables so the data was
>really coming from SQL anyway. I want to make this query work within
>Reporting Services, but I'm receiving the following error message:
>ADO error: ORDER BY items must appear in the select list if the statement
>contains a UNION operator.
>I was able to take the two Select statements from Access and make good SQL
>queries with them, but I'm unable to UNION them together eventhough it work
s
>fine in Access. Here's my two queries I'm trying to Union:
>SELECT TOP 100 PERCENT dbo.SOP30200.SLPRSNID, dbo.SOP30200.DOCAMNT
>FROM dbo.SOP30200 LEFT OUTER JOIN
> dbo.RM00101 ON dbo.SOP30200.CUSTNMBR =
>dbo.RM00101.CUSTNMBR
>WHERE (dbo.SOP30200.DOCDATE >= CONVERT(DATETIME, '2006-02-15 00:00:00',
>102)) AND (dbo.SOP30200.DOCDATE <= '2006-03-01') AND
> (dbo.SOP30200.SOPTYPE = 3) AND (dbo.SOP30200.VOIDSTTS
>= 0) AND (dbo.SOP30200.DOCID = 'inv')
>ORDER BY dbo.SOP30200.SLPRSNID, dbo.SOP30200.CUSTNMBR
>UNION
>SELECT dbo.SOP30200.SLPRSNID, - (1 * dbo.SOP30200.DOCAMNT) AS expReturn
s
>FROM dbo.SOP30200 LEFT OUTER JOIN
> dbo.RM00101 ON dbo.SOP30200.CUSTNMBR =
>dbo.RM00101.CUSTNMBR LEFT OUTER JOIN
> dbo.viewSalesperson ON dbo.RM00101.SLPRSNID =
>dbo.viewSalesperson.SLPRSNID
>WHERE (dbo.SOP30200.DOCDATE >= CONVERT(DATETIME, '2006-02-15 00:00:00',
>102)) AND (dbo.SOP30200.DOCDATE <= CONVERT(DATETIME,
> '2006-03-01 00:00:00', 102)) AND
>(dbo.SOP30200.VOIDSTTS = 0) AND (dbo.SOP30200.SOPTYPE = 4) AND
>(dbo.SOP30200.DOCID = 'returns')
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...amming/200603/1|||Any suggestions on what I need to do to fix it?
"psychodad71 via webservertalk.com" <u2248@.uwe> wrote in message
news:5cf4869c57190@.uwe...
> order by of the first part of the union should be the problem.
> Colin wrote:
> --
> Message posted via webservertalk.com
> http://www.webservertalk.com/Uwe/Forum...amming/200603/1|||Syntax is:
SELECT ... FROM ...
UNION (ALL)
SELECT ... FROM ...
ORDER BY...
(I also wonder why a TOP 100 PERCENT. That should return everything and
requires an ORDER BY to work. Get rid of the TOP 100 PERCENT> )
RLF
"Colin" <legendsfan@.nospam.nospam> wrote in message
news:etPkz6sQGHA.5924@.TK2MSFTNGP09.phx.gbl...
> Any suggestions on what I need to do to fix it?
> "psychodad71 via webservertalk.com" <u2248@.uwe> wrote in message
> news:5cf4869c57190@.uwe...
>|||Thanks for the help and feedback. I've got it working now. Is there a
reason why Visual Studio or any other design interface doesn't support
Design mode for UNION of two queries?
"Russell Fields" <RussellFields@.NoMailPlease.Com> wrote in message
news:uWwHb5uQGHA.2816@.TK2MSFTNGP15.phx.gbl...
> Syntax is:
> SELECT ... FROM ...
> UNION (ALL)
> SELECT ... FROM ...
> ORDER BY...
> (I also wonder why a TOP 100 PERCENT. That should return everything and
> requires an ORDER BY to work. Get rid of the TOP 100 PERCENT> )
> RLF
> "Colin" <legendsfan@.nospam.nospam> wrote in message
> news:etPkz6sQGHA.5924@.TK2MSFTNGP09.phx.gbl...
>|||A few notes that may help you (and others):
1. TOP 100 Percent ... ORDER BY was a workaround/hack/creative piece of SQL
to try to get the optimizer to sort values at a point in the query plan.
Unfortunately, it didn't really work in all cases, and it actually isn't
being honored at all in SQL 2005. So, please consider removing this from
your code in the future.
2. As you've seen from the other posts, ORDER BY should be applied to the
end of the complete statement to affect the presentation order of the
results returned to the client (and not each block of the UNION).
3. The column binding rules for Jet Red (Access's engine) never really
conformed to the ANSI standards. Given the installed base, it's not likely
to be changed. So, just be aware that the queries you may have in your
Access application are sometimes interpreted in slightly different ways.
Best of luck,
Conor Cunningham
SQL Server Query Optimization Development Lead
"Colin" <legendsfan@.nospam.nospam> wrote in message
news:etPkz6sQGHA.5924@.TK2MSFTNGP09.phx.gbl...
> Any suggestions on what I need to do to fix it?
> "psychodad71 via webservertalk.com" <u2248@.uwe> wrote in message
> news:5cf4869c57190@.uwe...
>