Re: Set SQL variable from select

Your have a paranteses to much… it will give you a scalar value ! Is that
your intention?

If so you need “limit 1”



tors. 29. jan. 2026 kl. 19.37 skrev Daniel Gross <daniel@xxxxxxxx>:

Hi Jim,

I think we should first clear the semantics up.

When you write:

with XYZ as (
select ...
)

then XYZ is not a variable - it's a CTE - short for Common Table
Expression. That mean - simplified - you define a "temporary table" that
only exists for that single statement.

I assume that you want to do something like:

with MYJOB as (
select job_name
from table(
qsys2.active_job_info(
job_name_filter => ’SOMEJOB',
subsystem_list_filter => 'QSYSWRK'
)
)
)
select ...
from ...

Now MYJOB is a table with 1 column named JOB_NAME. So if you want to
re-use that value, you will have to JOIN the table MYJOB.

But I assume, that you want to select some data with that job name - so
maybe it might be easier to use a sub-select:

select ...
from ...
where the_field_with_the_job_name = (
select job_name
from table(
qsys2.active_job_info(
job_name_filter => ’SOMEJOB',
subsystem_list_filter => 'QSYSWRK'
)
)
limit 1
)

The "limit 1" clause limits the sub-select to definitely 1 row only - even
of the sub-select might result in more than one row.

If you need more help, I would need a little more context on what you want
to do. Like the rest of your select statement.

HTH
Daniel

Am 29.01.2026 um 18:31 schrieb Charles Wilt <charles.wilt@xxxxxxxxx>:

My example using
job_name_filter => '*'

Will return exactly one job, and Jim's OP says "(Only one row should be
returned)"

Charles

On Thu, Jan 29, 2026 at 10:24 AM Birgitta Hauser <

Hauser@xxxxxxxxxxxxxxx>

wrote:

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.


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

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