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

[mlazarus <mlazarus@xxxxxxxx>]

Paul,

 Based on the text of the message, it calls out "ANDJOB_TYPE" as not 
being recogniged.  That tells me that either you are missing a space or 
the parser AJS is using is eliminating the space.

 -mark


On 1/6/2017 10:25 AM, Steinmetz, Paul wrote:
> 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/
>
>