?
Solved

Union query limit?

Posted on 2009-04-28
16
Medium Priority
?
1,657 Views
Last Modified: 2012-05-09
Is there a limit to the number of UNION queries I can run?  I'm fine at 2 but with 3 or more, I get this error:

"ODBC -- call failed.
[MySQL][ODBC 3.51 Driver][mysqld-5.0.51a-log] You have an error in your SQL syntax; ..."

I am using MySQL as my backend.  I can run the queries with 3 or more UNIONS in the mysql query browser.  It just fails with Access.  The number of records is small (6 total rows returned with 3 unions).


0
Comment
Question by:mock5c
  • 6
  • 6
  • 2
  • +2
16 Comments
 
LVL 75
ID: 24256628
No specific limit per se.  I have one union query with 22 unions ... runs with no issue.  No clue what would be causing your issue.

mx
0
 
LVL 11

Expert Comment

by:LambertHeenan
ID: 24261662
Please post the SQL that gives the error.
0
 

Author Comment

by:mock5c
ID: 24288470
DatabaseMX has answered my specific question which is there is no union limit.  Clearly my problem must be SQL syntax.  The following example query would fail.

select "a" from T
UNION
select "b" from T
UNION
select "c" from T

0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

Author Comment

by:mock5c
ID: 24295722
I'm going to increase points so I can get a solution to this problem.  DatabaseMX has already earned 50 points for answering there is no UNION limit.

My problem comes from my query.

If I typed:

select "a" from Table1
UNION
select "b" from Table1

I will get:
a
b

as the result

But if I throw another UNION in there,

select "a" from Table1
UNION
select "b" from Table1
UNION
select "c" from Table1

I get that ODBC error message. What is wrong with my syntax?
0
 
LVL 75
ID: 24296867
Here is the general format for a UNION query:

SELECT Table1.* FROM Table1
UNION SELECT Table2.* FROM Table2
UNION SELECT Table3.* FROM Table3
UNION SELECT Table4.* FROM Table4

and so on.

Note there are no double quotes ... and each table is a different table.

Also, the number of columns must be the same for each table (or query) data types for each column - in order - must match.  The names can be different.

mx
0
 

Author Comment

by:mock5c
ID: 24296989
I was just giving an example and the quotes are just for selecting a bogus piece of data.  All of the individual tables will come from the same query with a difference in the select clause for each of them.  The difference in the select clause is a name of a column (that's why I'm using quotes).  The datatypes and number of items in the select are the same.

I'm basically taking columns and transposing them into a single column and union'ing those queries together so all of the results are put in a single long vertical listbox instead of having a super wide result.

0
 
LVL 75
ID: 24297227
Sorry ... I don't know why you would be getting the ODBC error message.

Did you post this in the mySQL zone also


mx
0
 

Author Comment

by:mock5c
ID: 24297540
Is there a way to print all of my query to the screen?  If I could do that, then I could examine my query more closely or paste it hear.  I use MsgBox but there's a character limit.
0
 
LVL 75
ID: 24297626
Well, you could use Debug.Print  <some variable> ... and see the results in the Immediate Window.  I believe there is a limit there also, but more than the Message Box.

By 'print all ...' ... not sure what you mean?  results? SQL?


mx
0
 

Author Comment

by:mock5c
ID: 24297675
print the query string.  It's very large after many unions.
0
 
LVL 75

Accepted Solution

by:
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 200 total points
ID: 24297694
Try the Debug.Print  <Your SQL String> ... and see if it fits.  From the Immediate window, you can copy and paste into word or notepad, etc.

mx
0
 

Author Comment

by:mock5c
ID: 24630396
has anyone tried the following in Access?

substitute YourTable with any table you'd like:

select "a" from YourTable
union
select "b" from YourTable

I would expect this to work with the result:

a
b

Now, try the same thing by adding another union to the query

select "a" from YourTable
union
select "b" from YourTable
union
select "c" from YourTable

This simple little example illustrates my problem.   My query will fail whether it's this small query or if it's a large, complex query.

The error I get is:
"ODBC--call failed
[MySQL][ODBC 3.51 Driver][mysqld-5.0.26]You have an error in my SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UNION (SELECT 'b' FROM `YourTable`)) UNION (SELECT 'c' FROM `YourTable`)' at line 1(#1064)

Now, I can paste my above queries directly in the MySQL query browser and it works.  Somehow this is related to Access.  Can anyone reproduce this?

0
 
LVL 75
ID: 24630463
It doesn't fail within an Access MDB ... so I would say the issue is related to Access interfacing with MySQL.

mx
0
 
LVL 61

Assisted Solution

by:mbizup
mbizup earned 200 total points
ID: 24631688
See the following thread:
http://bugs.mysql.com/bug.php?id=7684

It seems to be a bug, but the workaround I've seen in a few places is to use pass-through queries.

From Access's Query designer's toolbar (SQL View):
Query -> SQL Specific -> select "Pass Through"
 
(Disclaimer: I don't use MySQL, so I do not have personal experience with this.)
0
 
LVL 61

Expert Comment

by:mbizup
ID: 24631734
Also, based on the above info - using VBA to create your query (embedded SQL) may also work.
One final note, Access queries are bound to these limitations:
http://www.databasedev.co.uk/access_specifications.html#content3
UNION statements fall under the "nested queries" category.
If you have more than 50 UNION statements, the query will fail with a "Query is too complex" error.
0
 

Expert Comment

by:ComputerZorg
ID: 37948236
Same problem here, resolved by first making a union query with two tables, after that make a new union between the created unionquery and a new table. Works somehow, although the underlying sql is essentially the same. Good luck.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question