RE: Set SQL variable from select

When using VALUES ... INTO exactly 1 Job must be returned, but the query may return multiple jobs (at least with the passed parameters).
If you only want to have the first job, you need to add LIMIT 1 or FETCH FIRST ROW ONLY at the end of the SELECT-Statement.

If you want to read all returned jobs, you need to declare a cursor and then loop through the cursor

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 Charles Wilt
Sent: Thursday, 29 January 2026 18:13
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: Re: Set SQL variable from select

May have missed a set of parens there ¯\_(ツ)_/¯

Charles

On Thu, Jan 29, 2026 at 10:12 AM Charles Wilt <charles.wilt@xxxxxxxxx>
wrote:

That assumes you're trying to do this in an SQL procedure or function...

If you're embedding the statement into RPG, then VALUES INTO is what
you want.

exec sql VALUES
select JOB_NAME
from table(
QSYS2.active_job_info(
job_name_filter *=>* '*'
)
) into :MYJOB;

Charles

On Thu, Jan 29, 2026 at 10:06 AM Charles Wilt <charles.wilt@xxxxxxxxx>
wrote:

WITH

starts a CTE definition, a CTE by itself is not valid.

select * from myjob;

Would complete the statement.
WITH MYJOB AS(
(select JOB_NAME
from table(
QSYS2.active_job_info(
job_name_filter *=>* '*'

)
)))
select * from myjob;

But since you're trying to get the value into a variable, I suspect
you want to use SET

SET MYJOB =
(select JOB_NAME
from table(
QSYS2.active_job_info(
job_name_filter *=>* '*'

)
));

HTH,
Charles

On Thu, Jan 29, 2026 at 9:42 AM Jim Oberholtzer <
midrangel@xxxxxxxxxxxxxxxxx> wrote:

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.

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