Re: SQL query by object creation date

On 13-Jan-2014 15:08 -0800, TheBorg wrote:

To get a *list* of objects from the QSYS library, you need to to
specify '*ALL' as the object type in the table function:

Select *
From (
Select * from table(QSYS2.OBJECT_STATISTICS('QSYS', '*ALL')) as x
) objstat
Where objtype = '*PGM'

The special value *ALL is no more an object type than the special value *LIBL is a library name. And the documentation for the table function at the given link, mentioned nothing about support for any special values other than those that represent an [and presumably only external] object type. Are people supposed to just guess what is or is not supported with regard to special values ;-)

Is the above invocation verified to provide expected results, or is that based only on speculation that some special values other than actual external object types would be allowed in the object-type-list parameter [and if so, then perhaps also the library-name parameter]?

Not sure why the prior examples offered use a CTE, nor why the above NTE is not simply coded as a table-reference in the form of a table-function invocation; i.e. why not just the following?:

Select x.*
From table(QSYS2.OBJECT_STATISTICS('QSYS', '*ALL')) as x
Where x.objtype = '*PGM'

But even that would be silly if indeed *ALL were supported as a special value, because all of that data would be generated for all object types, but then the final query results would include only those objects of type *PGM. Surely it would be best to have the UDTF generate only the minimal amount of data that will be selected; i.e. why not just the following?:

Select x.*
From table(QSYS2.OBJECT_STATISTICS('QSYS', 'PGM')) as x
/* Where x.objtype = '*PGM' */ -- omit; implied by UDTF inputs