Union in Access generates bad SQL [ODBC217]

Submitted by: Nathan Waite (nater)

Is this an ODBC bug? When I union two Access queries [that have criteria] bad SQL is generated.

Create a table in Firebird and link to it in Access. I created table TEST with integer ID and varchar VAL.

In Access create two queries to your linked table selecting a single row each. (ID=1 and ID=2) Now create a union query.

select * from qry_Test1 UNION select * from qry_Test2;

You'll receive an ODBC error due to a malformed statement:

SELECT "ID" ,"VAL" FROM "TEST" WHERE ("ID" = 1 ) UNION (SELECT "ID" ,"VAL" FROM "TEST" WHERE ("ID" = 2 ) )

Interestingly if you remove the first query's criteria, it no longer wraps the 2nd query in parenthesis and works ok.

SELECT "ID" ,"VAL" FROM "TEST" UNION SELECT "ID" ,"VAL" FROM "TEST" WHERE ("ID" = 2 )

Also, try changing the two Access queries to group by all fields. Now the first query has bad syntax.

SELECT "ID" ,"VAL" FROM "TEST" WHERE ("ID" = 1 GROUP BY "ID" ,"VAL" ) UNION (SELECT "ID" ,"VAL" FROM "TEST" WHERE ("ID" = 2 ) GROUP BY "ID" ,"VAL" )

FirebirdSQL / firebird-odbc-driver