MIDRANGE dot COM Mailing List Archive

Re: SQL query by object creation date


Chuck, your NTE version work perfectly. Many thanks for the education, I
still have lots more SQL to learn.

only two updates

QSYS2.OBJECT_STATISTICS('*ALL','*LIB') : *ALL in place of QSYS for all
library listing.
QSYS2.OBJECT_STATISTICS( lib.objname, 'PGM SRVPGM CMD FILE') : the "parm"
for object type is just a space delimited list(go figure) and the object
type can either be *PGM or PGM

thanks again,

On Tue, Jan 14, 2014 at 2:36 PM, CRPence <CRPbottle@xxxxxxxxx> wrote:

On 14-Jan-2014 09:26 -0800, bryan dietz wrote:

I have been playing with your code and ran across the following when
in RunSQL scripts:

SQL State: 42601
Vendor Code: -199
Message: [SQL0199] Keyword TABLE not expected.

The error appears to be around the third "table" word usage:

That was an issue for me on v5r3 also, but I figured that was a
problem since resolved. The definition of a table-reference in the
documentation allows for a table-function invocation. Yet the SQL seems
not to allow the reference in some places; I presume, incorrectly, thus
possibly a defect. Specifically, contextually the SQL is allowing the
table-function as table-reference directly after a FROM, but seems not
to allow the reference after a JOIN. I suppose one could infer that the
LATERAL specification, appearing only in the NTE syntax for a
table-reference, might make the restriction seem logical.





As such, perhaps with the following revision, whereby the
table-function is tucked-into a Nested Table Expression (NTE):

SELECT xlib.objlibrary, xobj.*
table( values (lib.objname ) ) AS xlib ( objlibrary )
( select zobj.*
) AS zobj
) 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

Still reading looking into..

to get the listing of *ALL libraries the first use needs to be
QSYS was incorrect, that was my mistake.

<<SNIPped doc links>>

Where is the documentation suggesting the capability for any special
values other than for object-types; notably, for any special values in
place of the library-name argument? The snipped doc links mentioned
nothing about that. The original invocation seemed reasonable enough
conceptually, though I could understand why it might not function; i.e.
per the subtle distinction between the use of the name QSYS as a library
name vs /Machine Context/ as the implied library.

But how are other users supposed to figure that out? How did you
figure that out? I suppose the person reading the doc reference is
supposed to divine that the inputs are as-defined by some other function
like DSPOBJD or perhaps the QGYOLOBJ or QUSLOBJ APIs, whence they can go
review those; but obviously not any of those, as each of those allows
effectively QSYS/*ALL for *LIB objects.

Regards, Chuck
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.

Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2015 by MIDRANGE dot COM and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available here. If you have questions about this, please contact