Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Union in Access generates bad SQL [ODBC217] #199

Open
firebird-automations opened this issue Mar 10, 2020 · 0 comments
Open

Union in Access generates bad SQL [ODBC217] #199

firebird-automations opened this issue Mar 10, 2020 · 0 comments

Comments

@firebird-automations
Copy link

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" )

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests

3 participants