Re: Set SQL variable from select

[Charles Wilt <charles.wilt@xxxxxxxxx>]

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.