Re: I need a CTE with QSYS2.OPEN_FILES, QSYS2.ACTIVE_JOB_INFO

Thank You. that works for me...
(except I needed to say "cross join table(..." )

with T1(jobInfo) as
(SELECT J.Job_Name
FROM TABLE(QSYS2.ACTIVE_JOB_INFO(
subsystem_list_filter => 'QINTER',
detailed_info => 'ALL')) J
where function_type = 'PGM'
order by open_files DESC
LIMIT 10 )

select *
from T1
cross join table(QSYS2.OPEN_FILES(JobInfo)) x;

On Mon, Jun 14, 2021 at 11:02 AM Tim Fathers <tim@xxxxxxxxxxxxx> wrote:

...actually, you'll probably need to select from T1 and cross join to the
table function, as the order of the statements is probably important. I'm
not on-line to try it, but it will be something like this:


with T1(jobInfo) as
(SELECT J.Job_Name
FROM TABLE(QSYS2.ACTIVE_JOB_INFO(
subsystem_list_filter => 'QINTER',
detailed_info => 'ALL')) J
where function_type = 'PGM'
order by open_files DESC
LIMIT 10 )

select *
from T1
cross table(QSYS2.OPEN_FILES(JobInfo)) x;

Tim.

________________________________
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> on behalf of Tim
Fathers <tim@xxxxxxxxxxxxx>
Sent: 14 June 2021 16:59
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: Re: I need a CTE with QSYS2.OPEN_FILES, QSYS2.ACTIVE_JOB_INFO

...add "cross join T1" at the end, that should do it, otherwise you're not
referring to the CTE.

Tim

Get Outlook for Android<https://aka.ms/AAb9ysg>

________________________________
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> on behalf of
Gerald Magnuson <gmagqcy.midrange@xxxxxxxxx>
Sent: Monday, June 14, 2021 4:43:03 PM
To: midrange-l@xxxxxxxxxxxxxxxxxx <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: Re: I need a CTE with QSYS2.OPEN_FILES, QSYS2.ACTIVE_JOB_INFO

I have tried...
with T1(jobInfo) as
(SELECT J.Job_Name
FROM TABLE(QSYS2.ACTIVE_JOB_INFO(
subsystem_list_filter => 'QINTER',
detailed_info => 'ALL')) J
where function_type = 'PGM'
order by open_files DESC
LIMIT 10 )
select *
from table(QSYS2.OPEN_FILES(JobInfo));

but "JobInfo was not found as a column of table *N"



On Mon, Jun 14, 2021 at 9:33 AM Gerald Magnuson <
gmagqcy.midrange@xxxxxxxxx>
wrote:

I am not skilled enough with CTE's to pull this off...

for this query:
SELECT J.*
FROM TABLE(QSYS2.ACTIVE_JOB_INFO(
subsystem_list_filter => 'QINTER',
detailed_info => 'ALL')) J
where function_type = 'PGM'
order by open_files DESC
LIMIT 10;

I want to list the open file info:
select *
from table(QSYS2.OPEN_FILES('000001/USERID/JOBNAME'));

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: https://amazon.midrange.com
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: https://amazon.midrange.com
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: https://amazon.midrange.com