RE: Order of output for QUSLJOB API

Rob,

That worked fine on my V7R3 LPAR, syntax error on V7R1 LPAR.
Is it possible this will not work on a V7R1 LPAR.

V7R3 R&D LPAR
SELECT * FROM TABLE(QSYS2.JOB_INFO(JOB_USER_FILTER => 'MSROBOT',
JOB_STATUS_FILTER => '*OUTQ')) X
where substr(job_name,16,10) = 'DEFREVENUE'
order by JOB_End_time desc
SELECT statement run complete.

V7R1 Production LPAR.
SELECT * FROM TABLE(QSYS2.JOB_INFO(JOB_USER_FILTER => 'MSROBOT',
JOB_STATUS_FILTER => '*OUTQ')) X
where substr(job_name,16,10) = 'DEFREVENUE'
order by JOB_End_time desc
Token => was not valid. Valid tokens: ) ,.

Paul

-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Rob Berendt
Sent: Monday, February 05, 2018 10:49 AM
To: Midrange Systems Technical Discussion
Subject: RE: Order of output for QUSLJOB API

You know you can still use a WHERE clause, and an ORDER BY clause, on a table function even if it has it's own special selection criteria, right?

So, it's quite easy to combine these:

SELECT * FROM TABLE(QSYS2.JOB_INFO(JOB_USER_FILTER => 'MSROBOT',
JOB_STATUS_FILTER => '*OUTQ')) X

select * from qgpl.jobinfo where substr(job_name,16,10) = 'DEFREVENUE' order by JOB_E00001 desc FETCH FIRST 1 ROWS only

Into

SELECT * FROM TABLE(QSYS2.JOB_INFO(JOB_USER_FILTER => 'MSROBOT',
JOB_STATUS_FILTER => '*OUTQ')) X where substr(job_name,16,10) = 'DEFREVENUE'
order by JOB_End_time desc
FETCH FIRST 1 ROWS only

Unless you've found it a real performance boost to sort/select a temporary file versus doing it from a UDTF.
And I have a small criticism about using QGPL to hold temporary files, and naming them close enough to the UDTF that I had to take a second look to see what was going on. If IBM comes out with a new UDTF or some such thing named qgpl/jobinfo you could be hosed. Think of the RUNSQL fun we had a few releases back.

Oh and <expletive deleted> 10 character column names and using DSPFFD to look them up IOW, don't use JOB_E00001 instead of JOB_END_TIME.


Rob Berendt