Re: SQL to join two queries

[Daniel Gross <daniel@xxxxxxxx>]

Hi Jim,

in this case, I would it do like this:

   select jo.object_library, jo.object_name, jo.object_type, ifnull(os.objtext, '*NONE')
   from qsys2.journaled_objects as jo
   cross join lateral (
      select *
      from table (
         qsys2.object_statistics(jo.object_library, jo.object_type,  
      )
   ) as os
   where ...

This should give you a list with library, object, type and text (if there is one - if not "*NONE").

HTH
Daniel

> Am 17.04.2026 um 16:39 schrieb Jim Oberholtzer <midrangel@xxxxxxxxxxxxxxxxx>:
>
> SQL sages, (me not)
>
> I’m trying to get a list of journaled objects with object text into a result set.
>
> I can get the list of objects easily:
>
> SELECT  OBJECT_LIBRARY FROM QSYS2.JOURNALED_OBJECTS
>   WHERE JOURNAL_LIBRARY = '#MXJRN' AND
>         JOURNAL_NAME = 'MISC'
>   ORDER BY OBJECT_LIBRARY;
>
> I can get the text for each of the objects equally easily.
>
> SELECT OBJNAME, OBJTEXT FROM TABLE (QSYS2.OBJECT_STATISTICS('#MXJRN', '*ALL'));
>
> What I’m having a devel of a time doing is getting the query to show object name, object text.
>
> I know this can be done with a select inside another select, but I’ll be danged I can’t get the code right.
> What is the solution?
>
> 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.