What exactly is your error message?
The following query works for me in ACS Run SQL Script and in STRSQL without any problems.
With MyJob as
(Select Job_Name
from Table(QSYS2.Active_Job_Info(SUBSYSTEM_LIST_FILTER => 'QSYSWRK',
JOB_NAME_FILTER => 'QSQSRVR')))
Select *
from MyJob;
Mit freundlichen Grüßen / Best regards
Birgitta Hauser
Modernization – Education – Consulting on IBM i
Database and Software Architect
IBM Champion since 2020
"Shoot for the moon, even if you miss, you'll land among the stars." (Les Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them and keeping them!"
"Train people well enough so they can leave, treat them well enough so they don't want to. " (Richard Branson)
"Learning is experience … everything else is only information!" (Albert Einstein)
-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Jim Oberholtzer
Sent: Thursday, 29 January 2026 17:42
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: Set SQL variable from select
I have this statement:
WITH MYJOB AS(
(select JOB_NAME
from table(
QSYS2.active_job_info(
job_name_filter => ’SOMEJOB',
subsystem_list_filter => 'QSYSWRK'
)
)))
I keep getting errors creating the variable MYJOB which will be used a bit later in the SQL. Says end of statement error. If I add “with data” that dies with not expected. So how do I get the results from the query into the variable MYJOB? (Only one row should be returned)
Since I am barely adequate with SQL I don’t see the answer and searching has been of no help.
--
Jim Oberholtzer
Agile Technology Architects
--
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.
midrange.com
