Re: Fw: Joblogs for jobs in jobq: Not sure this is the right group, but if not I am sure Mr Gibbs will point me to the right one.

On 18-Apr-2017 07:17 -0500, Rob Berendt wrote:

Someone figure out a way to tie these two queries together:

SELECT X.JOB_NAME
FROM TABLE(QSYS2.JOB_INFO(JOB_STATUS_FILTER => '*JOBQ',
JOB_USER_FILTER => '*ALL')) X
WHERE JOB_QUEUE_LIBRARY = 'ERPLXEC'
AND JOB_QUEUE_NAME = 'BPCSJOBQ'

SELECT Y.MESSAGE_ID, Y.MESSAGE_TEXT
FROM TABLE(QSYS2.JOBLOG_INFO('017044/DARREN/CST601J')) Y
WHERE Y.MESSAGE_TEXT LIKE '%ERPCSTROL1DARREN%'

You need to pass x.job_name from the first query into where you see
'017044/DARREN/CST601J'

[…]

Untested, but the [implied per TABLE keyword] LATERAL join should suffice to enable referring to X.JOB_NAME in the second of the subqueries:

SELECT X.JOB_NAME, Z.*
FROM TABLE(QSYS2.JOB_INFO( JOB_STATUS_FILTER => '*JOBQ'
, JOB_USER_FILTER => '*ALL')) as X
CROSS JOIN TABLE
( SELECT Y.MESSAGE_ID, Y.MESSAGE_TEXT
FROM TABLE(QSYS2.JOBLOG_INFO( X.JOB_NAME )) as Y
WHERE Y.MESSAGE_TEXT LIKE '%ERPCSTROL1DARREN%' ) as Z
WHERE JOB_QUEUE_LIBRARY = 'ERPLXEC'
AND JOB_QUEUE_NAME = 'BPCSJOBQ'

Or similarly [now referencing A.JOB_NAME]:

SELECT A.JOB_NAME, Z.*
FROM TABLE
( SELECT X.JOB_NAME
FROM TABLE QSYS2.JOB_INFO( JOB_STATUS_FILTER => '*JOBQ'
, JOB_USER_FILTER => '*ALL')) as X
WHERE JOB_QUEUE_LIBRARY = 'ERPLXEC'
AND JOB_QUEUE_NAME = 'BPCSJOBQ'
) as A
CROSS JOIN TABLE
( SELECT Y.MESSAGE_ID, Y.MESSAGE_TEXT
FROM TABLE(QSYS2.JOBLOG_INFO( A.JOB_NAME )) as Y
WHERE Y.MESSAGE_TEXT LIKE '%ERPCSTROL1DARREN%'
) as Z