Re: AW: Retrieve a user's full name (not the User ID)

On 08-Oct-2015 16:53 -0600, Booth Martin wrote:

I suspect you are right in that my diagnosis and symptoms are very
likely wrong.

Sharing the request and the failure information is most helpful. And then sharing the revision to the statement along with the new\different failure for that revised request, further elucidates the issues for the reader. Otherwise the reader is more dependent on the ability of the afflicted, to describe their inferences about what are the symptoms and their own diagnosis.

If I knew what I was doing I would probably be able to fix it.

Thus not providing the specific request and the specific failure details, for that failing requests, is akin to the "blind leading the blind" ;-)

I will say though that upper casing and changing the period
to a slash did cause two other errors to disappear.

Hopefully my recent followups have sufficiently explained; not sure if the number of replies to this thread is overwhelming, or perhaps [some of] the replies are just not arriving either timely or at all... but the replies that I see on the NewsGroup also appear in the archive; the exacting examples, alluded to be functional, appear in several of the messages in the thread: [http://archive.midrange.com/rpg400-l/201510/threads.html#00040]

FWiW:

For what the SQL will provide via that table-function, and I presume potentially very expensively, is probably best satisfied more directly instead with an API call [per apparent use of RPG]. Given the apparent desire to use a WHERE clause with an Equal predicate on a specific user-name, understand that the USERS() invocation will generate a result that includes all user profiles on the system [though possibly limited by authority], and only later will that be pared down to a result-set of only the one row of information for the one user about which information is being requested.

Or perhaps the USERS() UDTF accepts an argument, a user-name [or generic-name], and nobody has yet mentioned that capability.? No surprise really that possibility has not been noted, because the USERS() UDTF is not a published interface [so one must look to the SYSPARMS and SYSROUTINE catalogs to see about any parameters for supported arguments]; see [https://www.ibm.com/developerworks/community/forums/html/topic?id=b89601ff-6a88-4dd9-93a2-afc94b484c42&ps=50] where on "Mar 2" krmilligan suggests coding a dependency on that feature might not be in the best interest of a production application. Avoiding the USERS() UDTF ensures there will be no dependencies on a feature not documented externally. Anyhow, consider Roll-Your-Own solution instead of using USERS().

I would suggest [optionally after learning the SQL capabilities discussed, just for edification], deferring to an API such as what was noted by Brian here [http://archive.midrange.com/rpg400-l/201510/msg00042.html]. Then perhaps additionally learn how to encapsulate the chosen API call [or CL command] in a scalar User Defined Function (UDF) to return the TEXT() attribute for just one user. And\Or review the VIEW_DEFINITION of the USER_INFO catalog file [if available on your release; since IBM i 6.1 db2 group level-29] for possible user-profile-name as an argument to the UDTF that implements that VIEW. Presumably that VIEW USER_INFO is implemented as a lateral join of the data from USERS() for input into another function that might also not be externally documented. Lacking access to any v7 system, I can not see the VIEW definitions of the catalog VIEWs nor review the ROUTINES and PARAMETERS, so I can only speculate rather than explicitly describe.