In this gist, there was a mystery to be solved.... why did rows get eliminated when lateral correlation was used?

Using lateral correlation to combine SQL services.sql

--
-- Subject: Using lateral correlation to combine SQL services
-- Author: Scott Forstie  
-- Date  : January 9, 2022
-- Features Used : This Gist uses active_job_info, joblog_info, lateral correlation, and the values statement
--
--
stop;

--
-- What if we needed to:
-- a) Find active jobs using some filtering critera
--    (for example subsystem name)
-- b) Once we found the active jobs of interest, we needed to drill into the joblogs of these jobs and find something of interesst
--    (for example the most recent request type message)
-- c) Follow the best practice of going to the database one time with our query (when possible)
--
stop;

--
-- ========================================================
-- Step 1: Find the active jobs
-- ========================================================
--
select job_name, JOB_NAME_SHORT, AUTHORIZATION_NAME as USER_NAME, 
       FUNCTION_TYPE concat '-' concat "FUNCTION" as Function, JOB_STATUS
  from table (
      qsys2.active_job_info(SUBSYSTEM_LIST_FILTER => 'QINTER')
    );
stop;

--
-- =================================================================
-- Step 2: Laterally correlate to qsys2.joblog_info
--
-- Lateral allows values returned earlier in the query to be 
-- referenced later in the query.
-- =================================================================
--
select job_name, JOB_NAME_SHORT, AUTHORIZATION_NAME as USER_NAME, 
       message_text as last_request,
       FUNCTION_TYPE concat '-' concat "FUNCTION" as Function, JOB_STATUS
  from table (
         qsys2.active_job_info(SUBSYSTEM_LIST_FILTER => 'QINTER')
       ) AJ, lateral (
         select message_text
           from table (
               qsys2.joblog_info(AJ.JOB_NAME)
             ) jl
           where jl.MESSAGE_TYPE is not distinct from 'REQUEST'
           order by ordinal_position desc
           limit 1
       );
stop;

--
-- ========================================================
-- Look at the results closely!  There are fewer jobs.  
--
-- We lost the rows that have zero request messages!
-- ========================================================
--
stop;

--
-- =============================================================
-- Step 3: Adjust the lateral correlation to avoid losing rows
--
-- Regain the lost rows by unioning in a placeholder row using the VALUES statement
-- =============================================================
--
select job_name, JOB_NAME_SHORT, AUTHORIZATION_NAME as USER_NAME, 
       message_text as last_request,
       FUNCTION_TYPE concat '-' concat "FUNCTION" as Function, JOB_STATUS
  from table (
         qsys2.active_job_info(SUBSYSTEM_LIST_FILTER => 'QINTER')
       ) AJ, lateral (
         select ordinal_position, message_text
           from table (
               qsys2.joblog_info(AJ.JOB_NAME)
             ) jl
           where jl.MESSAGE_TYPE is not distinct from 'REQUEST'
         union all
         values (0, cast(null as varchar(80)))
           order by ordinal_position desc
           limit 1
       );
stop;

--
-- =============================================================================================================
-- Alternative: Use LOJ in combination with LATERAL
--
-- A left outer join (LOJ) includes the rows from the left table that were missing from the inner join.
-- =============================================================================================================
--
select job_name, JOB_NAME_SHORT, AUTHORIZATION_NAME as USER_NAME, 
       message_text as last_request,
       FUNCTION_TYPE concat '-' concat "FUNCTION" as Function, JOB_STATUS
  from table (
         qsys2.active_job_info(SUBSYSTEM_LIST_FILTER => 'QINTER')
       ) AJ  left outer join lateral (
         select message_text, aj.job_name as msg_job
           from table (
               qsys2.joblog_info(AJ.JOB_NAME)
             ) jl
           where jl.MESSAGE_TYPE is not distinct from 'REQUEST'
           order by ordinal_position desc
           limit 1
       ) on aj.job_name = msg_job;
stop;

Neat idea :)
I ended up moving the joblog_info to a (select ...) column in the main select.
That "is not distinct" is "new to me" so I'm going to look into that one.
Is there any potential performance advantage of one style over the other?

I would guess that the performance is data dependent, based upon the number of jobs and the number of messages in the joblogs.
So.... it depends.

This works even at 7.3 TR8 with the exception of JOB_NAME_SHORT. Thank you for this Scott.

You're very welcome Glenn. Its great to get this sort of feedback from the community.