UNION statement doesn't work

[loRdK]

Hi,

I have a database stored in MS SQL 2000 and an application written in VB5, which connects the database via JET/ODBC.

I have a problem with the UNION statement.

When I run a simple query like:

"SELECT field1 FROM table1 UNION SELECT field2 FROM table2"

I get the following error:

"Runtime error 3078 - The Microsoft Jet database engine cannot find the input table or query 'select field1 from table1'. Make sure it exists and that its name is spelled correctly."

I can run the queries separately "SELECT field1 FROM table1" and "SELECT field1 FROM table2", so that I'm sure table and field names are correct and I have permission to access them.

Both field1 and field2 are the same type (int).

If I run the query in MS SQL Query Analyzer, it works fine.

It doesn't work only when I run it from VB/JET/ODBC.

Has anyone already had this kind of problem?

Any help will be highly appreciated!

Thank you so much for the attention.

 

[RiazKhanmohamed]

in your first part you said your query was:

"SELECT field1 FROM table1 UNION SELECT field2 FROM table2"

which failed.

in the second part you said your test queries individually were :

"SELECT field1 FROM table1" and "SELECT field1 FROM table2"

Does your table2 have a column called field1 or field2, and which one do you want? In a union the output fields need to be the same or it cannot connect them.

 

[loRdK]

Thank you for the reply, FredPerry.

Sorry, I made a mistake when I wrote the message.

The individual queries are:
"SELECT field1 FROM table1" and "SELECT field2 FROM table2"

I'm sure tables and fields exist, I have permission to access them, and both Field1 and Field2 are the same data type (int).

I've tried to run UNION queries using other tables and they all have failed.

 

[dswitzer]

I think the issue is SQL Server needs to name the union like this:

select * from
(select Field1 from table1
union all select Field2 from table2) myalias

That myalias - or whatever you name it is important....

 

[RiazKhanmohamed]

my concern is that you are using a union to join two different fields. I believe that they have to have the same name. perhaps if you try this:

"SELECT field1 as XX FROM table1 UNION SELECT field2 as XX FROM table2"

Hope it helps.

 

[loRdK]

Thanks dswitzer.

The query you've suggested works perfectly if I run it from MS SQL Server Query Analyser.

And it also works if I run it from Visual Basic using an ODBCDirect data source. The original query ("SELECT field1 FROM table1 UNION SELECT field2 FROM table2&quot works this way either.

But I can't use pass-through queries, I must preserve Jet database engine SQL syntax, so I have to use A Jet-connected ODBC data source.

And "select * from (select Field1 from table1 union all select Field2 from table2) myalias" doesn't match Jet's syntax.

Jet accepts UNION queries on Jet databases, but just doesn't work with my Jet-connected ODBC MSSQLServer data source.

I found nothing about that on MS site.