ACTIVE_JOB_INFO UDTF and LOCATE_IN_STRING scalar (was: Need Help with QGYOLJOB API (Please))

On 30-Sep-2016 03:20 -0500, Fred Keith wrote:

SELECT
SUBSTR( JOB_NAME, 1, 6) AS JOB_NUMBER
, SUBSTR( JOB_NAME, 8, POSSTR(SUBSTR(JOB_NAME, 8),'/')-1
) AS JOB_USER
, SUBSTR( SUBSTR( JOB_NAME, 8)
, POSSTR(SUBSTR( JOB_NAME, 8), '/')+1
) AS JOB_NAME
FROM TABLE
( QSYS2.ACTIVE_JOB_INFO( 'NO', 'QUSRWRK', '', '')
) AS X

Apparently per not being on IBM i 7.2 yet, this example is not of much help, but on that newer release, there is the scalar function LOCATE_IN_STRING that can make finding the second _occurrence_ of the slash character /cleaner/ looking; the following is a bit of overkill, per additionally obtaining the smallest VARCHAR for each component of the qualified job name, but shows that newer scalar function utilized within the Nested Table Expression (NTE):

SELECT job_name
, VARCHAR( SUBSTR( JOB_NAME, 01, 06) , 06 ) AS JobNbr
, VARCHAR( SUBSTR( JOB_NAME, 08, L2) , 10 ) AS JobUsr
, VARCHAR( SUBSTR( JOB_NAME, S3, L3) , 10 ) AS JobNam
FROM TABLE
( select JOB_NAME
, LOCATE_IN_STRING(JOB_NAME, '/', 1, 2) - 8 AS L2
, LOCATE_IN_STRING(JOB_NAME, '/', 1, 2) + 1 AS S3
, LENGTH(JOB_NAME) -
LOCATE_IN_STRING(JOB_NAME, '/', 1, 2) AS L3
FROM TABLE
( QSYS2.ACTIVE_JOB_INFO( 'NO', 'QUSRWRK', '', '')
) AS X
) as J