Re: Interactive SQL vs RUNSQL REQUEST submitted via AJS - syntax checking different

Is there different syntax checking between Interactive SQL vs RUNSQL REQUEST submitted via AJS.
The same SQL fails when run RUNSQL REQUEST submitted via AJS.

The RUNSQL is complaining about JOB_TYPE, which is used only in the WHERE.
I got the RUNSQL to work.
I had to add JOB_TYPE to both the SELECT and GROUP BY.
Why?

Interactive SQL works
SELECT REMOTE_ADDRESS , JOB_NAME , LOCAL_PORT, Count(*) FROM
QSYS2.NETSTAT_JOB_INFO WHERE LOCAL_PORT = 23 AND JOB_TYPE =
'INTERACTIVE'AND JOB_NAME NOT LIKE '%QPADEV%' GROUP BY
ROLLUP(REMOTE_ADDRESS,JOB_NAME,LOCAL_PORT) ORDER BY REMOTE_ADDRESS
, JOB_NAME

RUNSQL request submitted via AJS fails
Cause . . . . . : Command to execute is: RUNSQL REQUEST('SELECT
REMOTE_ADDRESS , JOB_NAME , LOCAL_PORT, Count(*) FROM QSYS2.NETSTAT_JOB_INFO
WHERE LOCAL_PORT = 23 AND JOB_TYPE = ''INTERACTIVE''AND JOB_NAME NOT LIKE
''%QPADEV%'' GROUP BY ROLLUP(REMOTE_ADDRESS,JOB_NAME,LOCAL_PORT) ORDER BY
REMOTE_ADDRESS , JOB_NAME') OUTPUT(*PRINT)

SQL0104 Diagnostic 30 01/06/17 09:02:56.941510 QSQRUN1 QSYS *STMT QSQRUN1 QSYS *STMT
From module . . . . . . . . : QSQPREP
From procedure . . . . . . : CLEANUP
Statement . . . . . . . . . : 11297
To module . . . . . . . . . : QSQPREP
To procedure . . . . . . . : CLEANUP
Statement . . . . . . . . . : 11297
Message . . . . : Token ANDJOB_TYPE was not valid. Valid tokens: FOR USE
SKIP WAIT WITH FETCH LIMIT ORDER UNION EXCEPT.
Cause . . . . . : A syntax error was detected at token ANDJOB_TYPE. Token
ANDJOB_TYPE is not a valid token. A partial list of valid tokens is FOR USE
SKIP WAIT WITH FETCH LIMIT ORDER UNION EXCEPT. This list assumes that the
statement is correct up to the token. The error may be earlier in the
statement, but the syntax of the statement appears to be valid up to this
point. Recovery . . . : Do one or more of the following and try the
request again: -- Verify the SQL statement in the area of the token
ANDJOB_TYPE. Correct the statement. The error could be a missing comma or
quotation mark, it could be a misspelled word, or it could be related to the
order of clauses. -- If the error token is<END-OF-STATEMENT>, correct the
SQL statement because it does not end with a valid clause.
QWM1201 Information 40 01/06/17 09:02:56.941758 QQXSRV01 QSYS *STMT QQXSRV01 QSYS *STMT
From module . . . . . . . . : QQXCPIMESS
From procedure . . . . . . : QQxCPIMessage__SendMessage
Statement . . . . . . . . . : 16
To module . . . . . . . . . : QQXSQLSTAT
To procedure . . . . . . . : QQxSQLStatement__HandleSQLError
Statement . . . . . . . . . : 28
Message . . . . : RUN QUERY command failed with SQLCODE -104.
Cause . . . . . : The SQLCODE is -104. The SQLSTATE is 42601. The DB2 for
i5/OS SQL message text for this error is: Token ANDJOB_TYPE was not valid.
Valid tokens: FOR USE SKIP WAIT WITH FETCH LIMIT ORDER UNION EXCEPT.
Recovery . . . : Check the job log for more information, correct the
error and try the request again.
QWM1102 Information 30 01/06/17 09:02:56.944638 QQXSRV01 QSYS *STMT QQXSRV01 QSYS *STMT
From module . . . . . . . . : QQXCPIMESS
From procedure . . . . . . : QQxCPIMessage__SendMessage
Statement . . . . . . . . . : 16
To module . . . . . . . . . : QQXPERMQUE

RUNSQL request submitted via AJS working with Job_Type added to SELECT and GROUP BY

Cause . . . . . : Command to execute is: RUNSQL REQUEST('SELECT
REMOTE_ADDRESS , JOB_NAME , LOCAL_PORT, JOB_TYPE , Count(*) FROM
QSYS2.NETSTAT_JOB_INFO WHERE LOCAL_PORT = 23 AND JOB_TYPE = ''INTERACTIVE''
AND JOB_NAME NOT LIKE ''%QPADEV%'' GROUP BY
ROLLUP(REMOTE_ADDRESS,JOB_NAME,LOCAL_PORT,JOB_TYPE) ORDER BY REMOTE_ADDRESS
, JOB_NAME') OUTPUT(*PRINT)


Thank You
_____
Paul Steinmetz
IBM i Systems Administrator

Pencor Services, Inc.
462 Delaware Ave
Palmerton Pa 18071

610-826-9117 work
610-826-9188 fax
610-349-0913 cell
610-377-6012 home

psteinmetz@xxxxxxxxxx
http://www.pencor.com/