使用 SELECT AS 别名和内部联接在 TSQL 上出现 DB2 错误
我正在使用链接服务器在 LINUX 机器 (DB2/LINUXX8664) 上的 DB2 数据库上编写 T-SQL 程序。我认为 DB2 的版本是 9.5.3 但不确定。我收到一个错误，我认为这可能是 DB2 问题，因为 T-SQL 中的语法检查正常。这是代码：
IF(SELECT(OBJECT_ID('TEMPDB..#TempFile))) IS NOT NULL DROP TABLE #TempFile
SELECT *
INTO #TempFile
FROM OPENQUERY(LinkedServer, '
SELECT F.LOAN_NUMBER,
(SELECT
SUM(EXP_CHILD_CARE_AMOUNT) + SUM(EXP_FOOD_AMOUNT) +
SUM(EXP_LIFE_INSURANCE_AMOUNT) + SUM(EXP_TRANSPORTATION_AMOUNT) + SUM(EXP_TUITION_AMOUNT)+
SUM(EXP_USER_1_AMOUNT) + SUM(EXP_USER_2_AMOUNT) + SUM(EXP_USER_3_AMOUNT) +
SUM(EXP_UTILITIES_AMOUNT)
FROM FINANCIAL F)
AS ExpenseTotal,
(SELECT
SUM(MORTGAGOR_NET_PAY_AMOUNT) + SUM(MORTGAGOR_OTHER_INCOME_AMOUNT) AS IncomeTotal
FROM FINANCIAL F
INNER JOIN BDE.LOAN_V a ON F.LOAN_NUMBER = A.LOAN_NUMBER)
WHERE A.FIRST_PRINCIPAL_BALANCE> 0
GROUP BY F.LOAN_NUMBER
ORDER BY F.LOAN_NUMBER,
FETCH ONLY WITH UR ')
这是错误：
链接服务器“LINKEDSERVER”的 OLE DB 提供程序“MSDASQL”返回消息“[IBM][CLI Driver][DB2/LINUXX8664] SQL0104N 在“BER) WHERE”之后发现意外标记“A”。预期标记可能包括：“来自”。 ”。 消息 7350，16 级，状态 2，第 4 行 无法从链接服务器“LINKEDSERVER”的 OLE DB 提供程序“MSDASQL”获取列信息。
I am writing a T-SQL program over a DB2 database on a LINUX box (DB2/LINUXX8664) using a linked server. I think the DB2 is Version 9.5.3 but not certain. I am receiving an error that I feel is likely a DB2 issue as the syntax checks out okay in T-SQL. This is the code:
IF(SELECT(OBJECT_ID('TEMPDB..#TempFile))) IS NOT NULL DROP TABLE #TempFile
SELECT *
INTO #TempFile
FROM OPENQUERY(LinkedServer, '
SELECT F.LOAN_NUMBER,
(SELECT
SUM(EXP_CHILD_CARE_AMOUNT) + SUM(EXP_FOOD_AMOUNT) +
SUM(EXP_LIFE_INSURANCE_AMOUNT) + SUM(EXP_TRANSPORTATION_AMOUNT) + SUM(EXP_TUITION_AMOUNT)+
SUM(EXP_USER_1_AMOUNT) + SUM(EXP_USER_2_AMOUNT) + SUM(EXP_USER_3_AMOUNT) +
SUM(EXP_UTILITIES_AMOUNT)
FROM FINANCIAL F)
AS ExpenseTotal,
(SELECT
SUM(MORTGAGOR_NET_PAY_AMOUNT) + SUM(MORTGAGOR_OTHER_INCOME_AMOUNT) AS IncomeTotal
FROM FINANCIAL F
INNER JOIN BDE.LOAN_V a ON F.LOAN_NUMBER = A.LOAN_NUMBER)
WHERE A.FIRST_PRINCIPAL_BALANCE> 0
GROUP BY F.LOAN_NUMBER
ORDER BY F.LOAN_NUMBER,
FETCH ONLY WITH UR ')
Here is the error:
OLE DB provider "MSDASQL" for linked server "LINKEDSERVER" returned message "[IBM][CLI Driver][DB2/LINUXX8664] SQL0104N An unexpected token "A" was found following "BER) WHERE". Expected tokens may include: "FROM". SQLSTATE=42601
".
Msg 7350, Level 16, State 2, Line 4
Cannot get the column information from OLE DB provider "MSDASQL" for linked server "LINKEDSERVER".
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
<代码>= A.LOAN_NUMBER)
其中 A.FIRST_PRINCIPAL_BALANCE> 0 - 该括号看起来不合适。
= A.LOAN_NUMBER)- that bracket looks out of place.WHERE A.FIRST_PRINCIPAL_BALANCE> 0
我注意到的第一件事是你有一个裸查询，这在 Microsoft SQL Server 中并不罕见。链接查询的简化版本如下所示：
在 DB2 中，任何查询中都必须有
FROM子句。 Microsoft 和其他一些 SQL 供应商允许使用不带FROM子句的SELECT，但这不是标准 SQL。在这种情况下，DB2 符合标准。我注意到的第二件事：
您需要关闭那个带引号的字符串吗？
The first thing I notice is that you have a naked query, which is not uncommon in Microsoft SQL Server. A simplified version of your linked query looks like this:
In DB2, you must have a
FROMclause in any query. Microsoft and some other SQL vendors permit aSELECTwith noFROMclause, but this isn't standard SQL. In this case, DB2 conforms to the standard.Second thing I notice:
Do you need to close that quoted string?