Re: Set SQL variable from select

[Charles Wilt <charles.wilt@xxxxxxxxx>]

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.