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

Problem with using UNION in the where clause.

Hi, I'm using the JDBC:ODBC bridge but am having a problem using it with MS
Access.
I want to run the following thru the bridge;
SELECT * FROM Horse WHERE horseName Like 'MINORS UNION'
I can run this query in Access with no probs. I get an SQL.Exception when
running thru the bridge. I am running thru a try catch block.
I am assuming the problem lies with the keyword UNION being used in the
value.
any help appreciated,
Michael.
I haven't done very much with the JDBC/ODBC bridge. I would think that as
long as the expression 'MINOR UNION' is a valid string delimited by quotes,
the reserved word theory should not be a factor. Can you post the exact
SQLException that you receive?
Carb Simien, MCSE MCDBA MCAD
Microsoft Developer Support - Web Data
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
Are you secure? For information about the Strategic Technology Protection
Program and to order your FREE Security Tool Kit, please visit
http://www.microsoft.com/security.
| From: "Finbar Saunders" <finbar@.netspace.net.au>
| Newsgroups: microsoft.public.sqlserver.jdbcdriver
| Subject: Problem with using UNION in the where clause.
| Date: Fri, 16 Apr 2004 17:46:42 +0930
| Organization: Netspace Internet
| Lines: 16
| Message-ID: <c5o4p6$2i17$1@.otis.netspace.net.au>
| NNTP-Posting-Host: dsl-203-113-238-176.sa.netspace.net.au
| X-Trace: otis.netspace.net.au 1082103398 84007 203.113.238.176 (16 Apr
2004 08:16:38 GMT)
| X-Complaints-To: usenet@.otis.netspace.net.au
| NNTP-Posting-Date: Fri, 16 Apr 2004 08:16:38 +0000 (UTC)
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
| Path:
cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!newsfeed 00.sul.t-online.de!t-onlin
e.de!newsfeed.wirehub.nl!surfnet.nl!newsfeed.kabel foon.nl!195.129.110.21.MIS
MATCH!bnewsfeed00.bru.ops.eu.uu.net!bnewsinpeer01. bru.ops.eu.uu.net!emea.uu.
net!ash.uu.net!duster.adelaide.on.net!news.melbour ne.pipenetworks.com!news.l
abyrinth.net.au!news.netspace.net.au!not-for-mail
| Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.jdbcdriver:5892
| X-Tomcat-NG: microsoft.public.sqlserver.jdbcdriver
|
| Hi, I'm using the JDBC:ODBC bridge but am having a problem using it with
MS
| Access.
|
| I want to run the following thru the bridge;
| SELECT * FROM Horse WHERE horseName Like 'MINORS UNION'
|
| I can run this query in Access with no probs. I get an SQL.Exception when
| running thru the bridge. I am running thru a try catch block.
|
| I am assuming the problem lies with the keyword UNION being used in the
| value.
|
| any help appreciated,
| Michael.
|
|
|

Problem with uniqueidentifeir and union

Hi,
I have a report and a list of products in a parameter field. I also need a
"All" option there, but have problems doing it...
the sql query is following
Select name, productid from filteredproduct where new_team_lty = @.team and
new_alkupaivamaara_lty between convert(smalldatetime, '1/1/' + @.year) and
convert(smalldatetime, '12/31/' + @.year)
union all
Select 'All', 'All'
This would work fine if there was only a name selected. But the productid is
uniqueidentifier type and I get a error message:
"An Error occurred while reading data from the query result set. Syntax
error converting from a character string to uniqueidentifier"
I understand what that means, but the problem is how can I avoid it? I can't
drop the productid out of the query and also need the 'All' option as well...
And I'm out of ideas how to have them both...
Thanks in advance!
RegardsWell...
The solution to this one was pretty simple after all...
CONVERT(varchar(255),productid) was the solution :)|||Ok,
problems again. That convert helped to make that one query to work, but in
the next query where I need that productid, it wont work anymore...

Problem with Union ALl

I want to combine 4 columns and map those 4 to respective fileds in the target database.For this..when i used "Union All " its going for cross join and filling the columns with Null vlaues..
Like If each column has 5 rows ,iam getting (4* 5 =20) 20 rows

Is there any solution to avoid for this and to get the 5 rows?

Thanks
Kumar
sorry small mistake . when using UnionAll getting output like this

Like If each column has 5 rows ,iam getting (5+5+5+5)= 20 rows instaead of 5

|||

That's what its supposed to do. What exactly is your requirement?

-Jamie

|||

Sounds like you should be using the Merge Join transformation

from BOL:

The Merge Join transformation provides an output that is generated by joining two sorted datasets using a FULL, LEFT, or INNER join. For example, you can use a LEFT join to join a table that includes product information with a table that lists the country/region in which a product was manufactured. The result is a table that lists all products and their country/region of origin

|||I need only 5 rows with out any null values (making sure that there is no null values in the source)

when i combine ..iam getting ouput like

ID FirstName LastName
1 Null Null
2 Null Null
3 Null Null
Null Kumar Null
Null Jackson Null
Null Tom Null
Null Null Niranjan
Null Null McDonald
Null Null Harry

But I want like this ..is there any solution

ID FirstName LastName

1 Kumar Niranjan
2 Jackson McDonald
3 Tom Harry

I hope you understand what i want to say
|||
I can't use Merge Join Cynic ,Bcoz i have 4 inputs to combine...it will help only when your dealing with 2.
To use Merge join for this scenario i have to sort 4 columns indvidually and then again i have to use 3 merge joins which will be heavy work

|||

Niru wrote:

I need only 5 rows with out any null values (making sure that there is no null values in the source)

when i combine ..iam getting ouput like

ID FirstName LastName
1 Null Null
2 Null Null
3 Null Null
Null Kumar Null
Null Jackson Null
Null Tom Null
Null Null Niranjan
Null Null McDonald
Null Null Harry

But I want like this ..is there any solution

ID FirstName LastName

1 Kumar Niranjan
2 Jackson McDonald
3 Tom Harry

I hope you understand what i want to say

That makes no sense. For example, where is it defined that "1", "Kumar" & "Niranjan" form a single record? Nowhere that I can see.

Perhaps you are implying that the order in which the values appear is important. i.e. RecordX from dataset1 joins with RecordX from dataset2 and RecordX from dataset3. If this is the case you will have to give each row a number (here is how: http://www.sqlis.com/default.aspx?37) and then use the MERGE JOIN component.

-Jamie

|||Thanks Jamie for the hint ..got it solved with that idea.

Thats what i need

Thanks
Niru

Problem with UNION ALL

Hi,
I have a procedure that has 18 'SELECT' queries with 'UNION ALL'.
Among them one SELECT query is making more time.
If the same query is run seperately, it works fine in much less time
and the row count in the execution plan is small with a inner loop
join.
But when used with other select statements using UNION ALL,
the row count goes in thousands resulting in a HASH join.
Could u suggest any possible reason?
Thanks,
Dutt.Dutt
Do the SELECTs have WHERE conditions? How big is the output? Can you post
am example here?
"Dutt" <Mr.Dutt@.gmail.com> wrote in message
news:1176178291.856296.203170@.w1g2000hsg.googlegroups.com...
> Hi,
> I have a procedure that has 18 'SELECT' queries with 'UNION ALL'.
> Among them one SELECT query is making more time.
> If the same query is run seperately, it works fine in much less time
> and the row count in the execution plan is small with a inner loop
> join.
> But when used with other select statements using UNION ALL,
> the row count goes in thousands resulting in a HASH join.
> Could u suggest any possible reason?
> Thanks,
> Dutt.
>|||Hi Uri,
Yeah, some SELECTs have WHERE conditions and other SELECTs are just
SELECT NULLs.
Output is 4line XML.(17 rows without XML).
XML option has nothing to do with it.
Thanks,
Dutt.
On Apr 10, 12:12 pm, "Uri Dimant" <u...@.iscar.co.il> wrote:
> Dutt
> Do the SELECTs have WHERE conditions? How big is the output? Can you pos
t
> am example here?
> "Dutt" <Mr.D...@.gmail.com> wrote in message
> news:1176178291.856296.203170@.w1g2000hsg.googlegroups.com...
>
>
> - Show quoted text -|||Dutt
As I see here you also have some JOIN within UNION ALL statement, have you
tried to created a nonclusterd indexes
on referenced tables (FK)?
"Dutt" <Mr.Dutt@.gmail.com> wrote in message
news:1176189934.257427.236990@.o5g2000hsb.googlegroups.com...[vbcol=seagreen]
> Hi Uri,
> Yeah, some SELECTs have WHERE conditions and other SELECTs are just
> SELECT NULLs.
> Output is 4line XML.(17 rows without XML).
> XML option has nothing to do with it.
> Thanks,
> Dutt.
>
> On Apr 10, 12:12 pm, "Uri Dimant" <u...@.iscar.co.il> wrote:
>|||Yeah, Uri,
Here the joining key is Primary Key itself and no other key is
available between those tables.
And one doubt, is there any problem if we have too many UNION ALLs.
Thanks
Dutt
On Apr 10, 2:07 pm, "Uri Dimant" <u...@.iscar.co.il> wrote:
> Dutt>> > the row count goes in thousands resulting in a HASH join.
> As I see here you also have some JOIN within UNION ALL statement, have you
> tried to created a nonclusterd indexes
> on referenced tables (FK)?
> "Dutt" <Mr.D...@.gmail.com> wrote in message
> news:1176189934.257427.236990@.o5g2000hsb.googlegroups.com...
>
>
>
>
>
>
>
> - Show quoted text -

Problem with UNION ALL

Hi,
I have a procedure that has 18 'SELECT' queries with 'UNION ALL'.
Among them one SELECT query is making more time.
If the same query is run seperately, it works fine in much less time
and the row count in the execution plan is small with a inner loop
join.
But when used with other select statements using UNION ALL,
the row count goes in thousands resulting in a HASH join.
Could u suggest any possible reason?
Thanks,
Dutt.Dutt
Do the SELECTs have WHERE conditions? How big is the output? Can you post
am example here?
"Dutt" <Mr.Dutt@.gmail.com> wrote in message
news:1176178291.856296.203170@.w1g2000hsg.googlegroups.com...
> Hi,
> I have a procedure that has 18 'SELECT' queries with 'UNION ALL'.
> Among them one SELECT query is making more time.
> If the same query is run seperately, it works fine in much less time
> and the row count in the execution plan is small with a inner loop
> join.
> But when used with other select statements using UNION ALL,
> the row count goes in thousands resulting in a HASH join.
> Could u suggest any possible reason?
> Thanks,
> Dutt.
>|||Hi Uri,
Yeah, some SELECTs have WHERE conditions and other SELECTs are just
SELECT NULLs.
Output is 4line XML.(17 rows without XML).
XML option has nothing to do with it.
Thanks,
Dutt.
On Apr 10, 12:12 pm, "Uri Dimant" <u...@.iscar.co.il> wrote:
> Dutt
> Do the SELECTs have WHERE conditions? How big is the output? Can you post
> am example here?
> "Dutt" <Mr.D...@.gmail.com> wrote in message
> news:1176178291.856296.203170@.w1g2000hsg.googlegroups.com...
>
> > Hi,
> > I have a procedure that has 18 'SELECT' queries with 'UNION ALL'.
> > Among them one SELECT query is making more time.
> > If the same query is run seperately, it works fine in much less time
> > and the row count in the execution plan is small with a inner loop
> > join.
> > But when used with other select statements using UNION ALL,
> > the row count goes in thousands resulting in a HASH join.
> > Could u suggest any possible reason?
> > Thanks,
> > Dutt.- Hide quoted text -
> - Show quoted text -|||Dutt
>> > the row count goes in thousands resulting in a HASH join.
As I see here you also have some JOIN within UNION ALL statement, have you
tried to created a nonclusterd indexes
on referenced tables (FK)?
"Dutt" <Mr.Dutt@.gmail.com> wrote in message
news:1176189934.257427.236990@.o5g2000hsb.googlegroups.com...
> Hi Uri,
> Yeah, some SELECTs have WHERE conditions and other SELECTs are just
> SELECT NULLs.
> Output is 4line XML.(17 rows without XML).
> XML option has nothing to do with it.
> Thanks,
> Dutt.
>
> On Apr 10, 12:12 pm, "Uri Dimant" <u...@.iscar.co.il> wrote:
>> Dutt
>> Do the SELECTs have WHERE conditions? How big is the output? Can you
>> post
>> am example here?
>> "Dutt" <Mr.D...@.gmail.com> wrote in message
>> news:1176178291.856296.203170@.w1g2000hsg.googlegroups.com...
>>
>> > Hi,
>> > I have a procedure that has 18 'SELECT' queries with 'UNION ALL'.
>> > Among them one SELECT query is making more time.
>> > If the same query is run seperately, it works fine in much less time
>> > and the row count in the execution plan is small with a inner loop
>> > join.
>> > But when used with other select statements using UNION ALL,
>> > the row count goes in thousands resulting in a HASH join.
>> > Could u suggest any possible reason?
>> > Thanks,
>> > Dutt.- Hide quoted text -
>> - Show quoted text -
>|||Yeah, Uri,
Here the joining key is Primary Key itself and no other key is
available between those tables.
And one doubt, is there any problem if we have too many UNION ALLs.
Thanks
Dutt
On Apr 10, 2:07 pm, "Uri Dimant" <u...@.iscar.co.il> wrote:
> Dutt>> > the row count goes in thousands resulting in a HASH join.
> As I see here you also have some JOIN within UNION ALL statement, have you
> tried to created a nonclusterd indexes
> on referenced tables (FK)?
> "Dutt" <Mr.D...@.gmail.com> wrote in message
> news:1176189934.257427.236990@.o5g2000hsb.googlegroups.com...
>
> > Hi Uri,
> > Yeah, some SELECTs have WHERE conditions and other SELECTs are just
> > SELECT NULLs.
> > Output is 4line XML.(17 rows without XML).
> > XML option has nothing to do with it.
> > Thanks,
> > Dutt.
> > On Apr 10, 12:12 pm, "Uri Dimant" <u...@.iscar.co.il> wrote:
> >> Dutt
> >> Do the SELECTs have WHERE conditions? How big is the output? Can you
> >> post
> >> am example here?
> >> "Dutt" <Mr.D...@.gmail.com> wrote in message
> >>news:1176178291.856296.203170@.w1g2000hsg.googlegroups.com...
> >> > Hi,
> >> > I have a procedure that has 18 'SELECT' queries with 'UNION ALL'.
> >> > Among them one SELECT query is making more time.
> >> > If the same query is run seperately, it works fine in much less time
> >> > and the row count in the execution plan is small with a inner loop
> >> > join.
> >> > But when used with other select statements using UNION ALL,
> >> > the row count goes in thousands resulting in a HASH join.
> >> > Could u suggest any possible reason?
> >> > Thanks,
> >> > Dutt.- Hide quoted text -
> >> - Show quoted text -- Hide quoted text -
> - Show quoted text -