Not able to run SQL queries in AS400, run into Invalid Token errors

Question

In AS400, how can i perform arithmetic operations (like +, -) on fields.

• 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."

Could someone please tell me what's wrong with my sql queries.

Answer 1

TABLE is a reserved word. SQL Reference: Reserved schema names and reserved words.

• Single quotes escape a string literal
• Double quotes escape a reserved word (similar to brackets in TSQL)

SQL Reference: Identifiers

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.

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 DEC instead of INT depending upon the definition of field sds.

SQL Reference: - INT - DEC - BETWEEN

Answer 2

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'.