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

db2ibm-midrangesql

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.

Best Answer

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

Related Topic