Problem with MySQL Union query

[ellenr]

I have a table [Officers] with column headings year, name, Pres, 1stVP, 2ndVP, Sec, Treas as column headings. I wish to construct a query with Lname, Fname and OfficeName as column headings where the result will contain only the current year's officers. I get the following error message as a result of the following union query:

Err:

ODBC--call failed.

(MySQL][ODBC 5.1 Driver][mysqld-5.5.42-MariaDB-cll-lve]You have an error in your SQL syntax; check the manual that corresponds to your MariaDB Server version for the right syntax to use near 'UNION (SELECT {FN SUBSTRING('1stVP',({fn locate(" ","1stVP",1))+1),{fn len' at line 1 (#1064)

the query:

SELECT Mid$([pres],(InStr([pres]," "))+1) AS Lname, Left$([pres],(InStr([pres]," "))-1) AS Fname, "President" AS OfficeName
FROM Officers
WHERE (((InStr([officeryear],Year(Date())))>1))

UNION SELECT Mid$([1stVP],(InStr([1stVP]," "))+1), Left$([1stVP],(InStr([1stVP]," "))-1), "1stVP"
FROM Officers
WHERE (((InStr([officeryear],Year(Date())))>1))

UNION SELECT Mid$([2ndVP],(InStr([2ndVP]," "))+1), Left$([2ndVP],(InStr([2ndVP]," "))-1), "2ndVP"
FROM Officers
WHERE (((InStr([officeryear],Year(Date())))>1))

UNION SELECT Mid$([Sec],(InStr([Sec]," "))+1), Left$([Sec],(InStr([Sec]," "))-1), "Secretary"
FROM Officers
WHERE (((InStr([officeryear],Year(Date())))>1))

UNION SELECT Mid$([Tres],(InStr([Tres]," "))+1), Left$([Tres],(InStr([Tres]," "))-1), "Treasurer"
FROM Officers
WHERE (((InStr([officeryear],Year(Date())))>1));

I can save the odbc [officers] table as a local table [OfficersL] and the query works, so there doesn't seem to be a syntax problem.
Conversely, I can run the odbc version with the first SELECT and any one of the UNION SELECTs and it works. I have dumbed it down to:

SELECT pres AS Lname,  "President" AS OName
FROM Officers

UNION  
SELECT [1stVP],"1stVP"
FROM Officers

UNION  
SELECT [2ndVP], "2ndVP"
FROM Officers

UNION  
SELECT [Sec], "Secretary"
FROM Officers

UNION SELECT [Tres],"Tresurer"
FROM Officers;

and I get "ODBC - call failed", but it doesn't complain if I do:

SELECT pres AS Lname,  "President" AS OName
FROM Officers

UNION SELECT [Tres],"Tresurer"
FROM Officers;

The #1064 err code indicates reserved words--I cannot find any. Ideas? Thanks.

 

[pbaldy]

Try taking out the semi colon at the end of the first query.

 

[ellenr]

Oops! My actual code does not have it. It was a paste error from an earlier trial.

 

[JHB]

If me, I would run the first select block, only for finding out if a error would pop up.

SELECT Mid$([pres],(InStr([pres]," "))+1) AS Lname, Left$([pres],(InStr([pres]," "))-1) AS Fname, "President" AS OfficeName FROM Officers WHERE (((InStr([officeryear],Year(Date())))>1))

It seems that Mid function in MySQL need 3 parameters, (else you've to use the SubString function), and you've only 2 parameter.
In MS-Access you can use the Mid function both with 2 and 3 parameters.

 

[ellenr]

Interesting thought about the mid$ function. However, not only does the first select block run error free by itself, but will run error free when any one of the union blocks are added. It errs if I add a third block. Also, when I removed all of the functions (see my "dumbed down" code in my original post) I experienced the same errors. As a temporary workaround, I programmatically make a local table and run my code on it. I would still like to know what I am doing wrong.

Thank you for taking the time to respond!

 

[John.Woody]

Not sure, but could Sec be a reserved word?


John.woody

 

[ellenr]

Don't know, but don't think so. It works with the first select and the "sec" block. Just not with a third added in, either before or after it.

 

[AccessBlaster]

I thought the purpose of the "union" was to bring two different tables together.
It looks to me you have a union on the same table.

And I am not sure your format is correct.

Try a single block like the sample below and see what the results are. Then increase the complexity as you move forward.

SELECT City FROM Customers
UNION
SELECT City FROM Suppliers
ORDER BY City;

 

[ellenr]

That is actually where I started setting up separate queries for each office, then a union query to combine them. Got the same err msg, so thought to simplify the query by putting all of the code into the one union query. The original [Officers] table was poorly designed in the first place--it was stuck in as an afterthought to allow a history report. I know of no other way to create an "uncross-tabbed" table, but would welcome suggestions!

 

[stopher]

Does the query work if you do it in the MySQL command line?

 

[stopher]

Thread here suggests sprinkling round brackets. Try round brackets around each select. Also try the recommendation in the thread - the last item.

 

[ellenr]

Stopher, thank you! So it is a bug in mysql, but can be overcome with ()'s. I did a workaround that seems to be working efficiently enough, so I am going to quit tinkering with this problem. I had used multiple unions in queries before and thought I knew what I was doing, but realize they were local tables, not mysql linked tables.