IBM DB2 Named Query won't Run

I’m trying to run a named query on a IBM DB2 db, but I keep getting an error. The query runs perfect while using the testing tab. I have table bindings that uses named queries to this same db without issue. When I try to run the query through a button (just for testing), I get this error

Traceback (most recent call last):

  File "<event:actionPerformed>", line 2, in <module>



com.inductiveautomation.ignition.client.gateway_interface.GatewayException: com.inductiveautomation.ignition.client.gateway_interface.GatewayException: com.ibm.as400.access.AS400JDBCSQLSyntaxErrorException: [SQL0199] Keyword FETCH not expected. Valid tokens: FOR USE SKIP WAIT WITH OPTIMIZE. Cause . . . . . :   The keyword FETCH was not expected here.  A syntax error was detected at keyword FETCH.  The partial list of valid tokens is FOR USE SKIP WAIT WITH OPTIMIZE. This list assumes that the statement is correct up to the unexpected keyword.  The error may be earlier in the statement but the syntax of the statement seems to be valid up to this point. Recovery  . . . :   Examine the SQL statement in the area of the specified keyword.  A colon or SQL delimiter may be missing. SQL requires reserved words to be delimited when they are used as a name. Correct the SQL statement and try the request again.


	caused by GatewayException: com.ibm.as400.access.AS400JDBCSQLSyntaxErrorException: [SQL0199] Keyword FETCH not expected. Valid tokens: FOR USE SKIP WAIT WITH OPTIMIZE. Cause . . . . . :   The keyword FETCH was not expected here.  A syntax error was detected at keyword FETCH.  The partial list of valid tokens is FOR USE SKIP WAIT WITH OPTIMIZE. This list assumes that the statement is correct up to the unexpected keyword.  The error may be earlier in the statement but the syntax of the statement seems to be valid up to this point. Recovery  . . . :   Examine the SQL statement in the area of the specified keyword.  A colon or SQL delimiter may be missing. SQL requires reserved words to be delimited when they are used as a name. Correct the SQL statement and try the request again.
	caused by Exception: com.ibm.as400.access.AS400JDBCSQLSyntaxErrorException: [SQL0199] Keyword FETCH not expected. Valid tokens: FOR USE SKIP WAIT WITH OPTIMIZE. Cause . . . . . :   The keyword FETCH was not expected here.  A syntax error was detected at keyword FETCH.  The partial list of valid tokens is FOR USE SKIP WAIT WITH OPTIMIZE. This list assumes that the statement is correct up to the unexpected keyword.  The error may be earlier in the statement but the syntax of the statement seems to be valid up to this point. Recovery  . . . :   Examine the SQL statement in the area of the specified keyword.  A colon or SQL delimiter may be missing. SQL requires reserved words to be delimited when they are used as a name. Correct the SQL statement and try the request again.

Ignition v7.9.10 (b2018112821)
Java: Oracle Corporation 1.8.0_191

this is the query:

SELECT PDPLNT
FROM PURCHASE.PURCHD
WHERE PDVEND = 808 and PDRECD = 0 and PDCMPL != 'C' and PDNUMB = :po
LIMIT 1

and my code to run the query:

system.db.runNamedQuery("BOM/PlantLookup", {'po':'129902'})

EDIT: I should also mention that this db is not my default for this project, I do have it selected as the db to run on in the named query section

Solved: It didn’t like the
LIMIT 1
I had to change it to
GROUP BY PDPLNT

1 Like