无法在 AS400 中运行 SQL 查询，遇到无效令牌错误
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16587863/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Not able to run SQL queries in AS400, run into Invalid Token errors
提问by Developer
In AS400, how can i perform arithmetic operations (like +, -) on fields.
在 AS400 中，我如何对字段执行算术运算（如 +、-）。
- For the query
Select id, sum(field1+field2) as Total from table group by id, getting the following error msg in German "[IBM][System i Access ODBC-Treiber][DB2 für i5/OS]SQL0104 - Token & ungültig. Gültige Token: + - AS<IDENTIFIER>." English Translation is something like "[IBM] [System i Access ODBC Driver] [DB2 for i5/OS] SQL0104 - Token<END Instruction>invalid. Valid tokens: CL AS IN LOG OUT DATA<identifier>." - For the query
Select count(*) from (select distinct field1 from table where field2="abc", getting the following error msg "[IBM][System i Access ODBC-Treiber][DB2 für i5/OS]SQL0104 - Token<ENDE DER ANWEISUNG>ungültig. Gültige Token: AS CL IN LOG OUT DATA<IDENTIFIER>." - For a query with sub-query got the following error msg
"[IBM][System i Access ODBC-Treiber][DB2 für i5/OS]SQL0104 - Token & ungültig. Gültige Token:
<>=<><=!<!>!=>=?<?>?=IN NOT."
- 对于查询
Select id, sum(field1+field2) as Total from table group by id，在德语“[IBM][System i Access ODBC-Treiber][DB2 für i5/OS]SQL0104 - Token & ungültig. Gültige Token: + - AS<IDENTIFIER>”中获得以下错误消息。英文翻译类似于“[IBM] [System i Access ODBC Driver] [DB2 for i5/OS] SQL0104 - 令牌<END Instruction>无效。有效令牌：CL AS IN LOG OUT DATA<identifier>。” - 对于查询
Select count(*) from (select distinct field1 from table where field2="abc"，收到以下错误消息“[IBM][System i Access ODBC-Treiber][DB2 für i5/OS]SQL0104 - Token<ENDE DER ANWEISUNG>ungültig.Gültige Token: AS CL IN LOG OUT DATA”<IDENTIFIER>。 - 对于带有子查询的查询，得到以下错误消息“[IBM][System i Access ODBC-Treiber][DB2 für i5/OS]SQL0104 - Token & ungültig。Gültige Token:
<>=<><=!<!>!=>=?<?>?=IN NOT。”
Could someone please tell me what's wrong with my sql queries.
有人可以告诉我我的 sql 查询出了什么问题。
回答by James Allman
TABLEis a reserved word. SQL Reference: Reserved schema names and reserved words.
TABLE是保留字。 SQL 参考：保留模式名称和保留字。
- Single quotes escape a string literal
- Double quotes escape a reserved word (similar to brackets in TSQL)
- 单引号转义字符串文字
- 双引号转义保留字（类似于 TSQL 中的括号）
The queries could be re-written as:
查询可以重写为：
SELECT ID, SUM(FIELD1 + FIELD2) AS TOTAL FROM "TABLE" GROUP BY ID
SELECT COUNT(*) FROM (SELECT DISTINCT FIELD1 FROM "TABLE" WHERE FIELD2 = 'ABC')
UPDATE
更新
DB/2 for i does not support your method of numeric to character conversion or the type of character comparison used in your LIKE query.
DB/2 for i 不支持您的数字到字符转换方法或 LIKE 查询中使用的字符比较类型。
The query can be re-written as:
查询可以重写为：
SELECT eds, SUM(INT(sds)) AS totalh
FROM tbl1
WHERE eds BETWEEN 20130500 AND 20130599
AND siteds IN (
SELECT DISTINCT site
FROM tbl2
WHERE H_04 IN ('1234') AND PERIOD = 201305
)
GROUP BY eds
ORDER BY eds
You may need to use DECinstead of INTdepending upon the definition of field sds.
您可能需要使用DEC而不是INT取决于 field 的定义sds。
回答by David G
Is the table's name really 'table'?
桌子的名字真的是“桌子”吗？
Odd as it seems, if I use the SQL statement you indicated as is, I get the same error... but if I change 'table' to 'table1', it just complains that it can't find 'table1'.
看起来很奇怪，如果我按原样使用 SQL 语句，我会得到同样的错误……但是如果我将“table”更改为“table1”，它只会抱怨找不到“table1”。
