Re: SQL query by object creation date

On 13-Jan-2014 14:46 -0800, Bdietz400 wrote:

I have been playing with this new function. Making my own generic
DSPOBJD. Since it's SQL I can use better *generic* selections for
the object.

I have bee trying to figure out how to, in one SQL statement, use
the output from
SELECT * FROM TABLE (QSYS2.OBJECT_STATISTICS('QSYS','*LIB')) AS x
As input to a second statement for the object selection.

Any ideas?

My interpretation is that the desire is to effect a second invocation of the UDTF, not a second statement. That is, the 2nd invocation would be used to obtain a list of objects for each library obtained from the prior\first invocation, against which selection then can be performed across all of those libraries... in the single-statement.? If so, then perhaps the following which asks for all of the /statistics/ for all program and service program objects that have a non-blank program attribute and that have never been used... or possibly the last-used information was reset [sorry, I can not test]:

SELECT
lib.objname as lib_name
/* lib_name is redundant; i.e. lib.objname=obj.objlibrary */
, obj.*
FROM
TABLE(QSYS2.OBJECT_STATISTICS('QSYS','*LIB')) AS lib
INNER JOIN LATERAL
( /* While seemingly worthless included in return table...
Per lack of the OBJLIBRARY as a column in the UDTF,
the column is generated here from the prior results
in a Cartesian product, to enable the inner join */
SELECT xlib.objlibrary, xobj.*
FROM
table( values (lib.objname ) ) AS xlib ( objlibrary )
CROSS JOIN LATERAL
TABLE(QSYS2.OBJECT_STATISTICS(lib.objname,'PGM,SRVPGM')) AS xobj
) AS obj
ON lib.objname = obj.objlibrary
WHERE obj.objattribute <> ''
AND obj.last_used_timestamp is null
/* and obj.objtype in ('*PGM', '*SRVPGM') */ -- redundant; omitted


Had the UDTF OBJECT_STATISTICS included additionally in its RETURNS TABLE column-list, a column named OBJLIBRARY that was just a reflection of the value passed as the first argument, then the query is much simpler:

SELECT
obj.*
FROM
TABLE(QSYS2.OBJECT_STATISTICS('QSYS','LIB')) AS lib
INNER JOIN LATERAL
TABLE(QSYS2.OBJECT_STATISTICS(lib.objname,'PGM,SRVPGM')) AS obj
ON lib.objname = obj.objlibrary
WHERE obj.objattribute <> ''
AND obj.last_used_timestamp is null
/* and obj.objtype in ('*PGM', '*SRVPGM') */ -- redundant; omitted