...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
midrange.com
