联合查询Interbase数据库上的访问 [英] Union query Access on an Interbase DB

问题描述

我正在通过ODBC(Easysoft)ver.7在Interbase数据库上从Access 2010执行查询.一切正常，除非我要触发这样的Union查询:

I am executing queries from Access 2010 on an Interbase database via ODBC (Easysoft) ver.7. Everything works fine except when i come to fire a Union query such as this:

SELECT TRIP.TRIPDATE, RESERVATION.BOOKINGREF, RESERVATION.LEADNAME, TRIP.DRIVERID, RESERVATION.STATUS, RESERVATION.DATECANCELLED, TRIP.TRANSPORTTYPEID
FROM TRIP INNER JOIN RESERVATION ON TRIP.TRIPID = RESERVATION.ARRIVALTRIPID
WHERE (((TRIP.TRIPDATE) Between #2/1/2012# And #2/29/2012#) AND ((TRIP.DRIVERID)=2) AND ((RESERVATION.DATECANCELLED) Is Null) AND ((TRIP.TRANSPORTTYPEID)=12))


UNION 

SELECT TRIP.TRIPDATE, RESERVATION.BOOKINGREF, RESERVATION.LEADNAME, TRIP.DRIVERID, RESERVATION.STATUS, RESERVATION.DATECANCELLED, TRIP.TRANSPORTTYPEID
FROM TRIP INNER JOIN RESERVATION ON TRIP.TRIPID = RESERVATION.DEPARTURETRIPID
WHERE (((TRIP.TRIPDATE) Between #2/1/2012# And #2/29/2012#) AND ((TRIP.DRIVERID)=2) AND ((RESERVATION.DATECANCELLED) Is Null) AND ((TRIP.TRANSPORTTYPEID)=12));

当我从Access运行此查询时，我会得到

When I run this query from Access I get

"ODBC-调用失败，[Easysoft] [Interbase]动态SQL错误，SQL错误 代码= -104，令牌未知-line1，字符0，((## 104)"

"ODBC --call failed, [Easysoft][Interbase]Dynamic SQL Error, SQL error code = -104, Token unknown -line1,char 0, ((#-104)"

当单独运行选择查询时，它们可以正常工作，但是当通过UNION加入时，会出现此错误.

When running the select queries on their own they work fine but when joined via UNION I get this error.

任何帮助将不胜感激.

谢谢

推荐答案

您没有提到查询是直通查询还是在Access查询中使用链接的ODBC表.

You don't mention if your query is a passthrough query or if you are using linked ODBC tables in an Access query.

在普通的Access查询中使用链接的ODBC表时，Access数据引擎将根据需要重写查询，以使其与其他数据库引擎兼容.
有时候，它可能会失败.

When using linked ODBC tables in a normal Access query, the Access data engine will rewrite the queries as necessary to make them compatible with the other database engine.
Sometimes, it can fail though.

1. 确保每个SELECT查询都能正常工作，并分别返回正确的数据.

1. Make sure each SELECT query works and returns correct data independently.

尝试一个更简单的UNION查询，以确保问题来自UNION关键字本身.

Try a simpler UNION query to make sure that the issue comes from the UNION keyword itself.

尝试UNION ALL

尝试使用传递查询.

如果您使用传递查询

直通查询将逐字发送到ODBC引擎，而Access只是收集结果而无需重写查询本身.

Pass-through queries are send verbatim to the ODBC engine, and Access just collects the results without rewriting the query itself.

1. 确保每个SELECT查询都作为传递查询，并独立返回正确的数据.

1. Make sure each SELECT query works as a pass-through query and returns correct data independently.

确保对于Interbase SQL，正确格式化字面日期格式.
您使用的数据库对于Access SQL是正确的，但是不同的数据库接受不同的格式.

Make sure that the literal dates are properly formatted for Interbase SQL.
The ones you use are correct for Access SQL, but different databases accept different formats.

使用仅包含1或3个字段的简单SELECT语句尝试更简单的UNION查询.

Try a simpler UNION query using simple SELECT statements involving 1 or 3 fields only.

尝试UNION ALL.

您没有在问题中显示它，但以防万一，如果您使用ORDER BY语句，则您 UNION查询.

You don't show it in your question, but just in case, if you used an ORDER BY statement, you have to wrap the UNION query.

尝试强制转换字段的数据类型. 某些字段的数据被错误地解释，并且该联合失败，因为它假定检索到的数据属于不同类型.

Try to cast the data types of your fields. It may be that some fields's data are incorrectly interpreted and that the union fails because it assumes that the data retrieved is of different types.

尝试改用标准Access查询.

Try using a standard Access query instead.

这篇关于联合查询Interbase数据库上的访问的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！