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

On 08-Oct-2015 15:31 -0600, Booth Martin wrote:

On 10/8/2015 11:02 AM, Dave Boettcher wrote:

I'm trying this out in interactive SQL and can't seem to find the
qsys2/users table. <<SNIP>>

I had to UPPERCASE "qsys2/users" for it to be found.

Had to upper-case the undelimited "qsys2/users"? That is, is the
claim made above, that QSYS2/USERS had to be specified instead of
qsys2/users being specified? Perhaps no matter, per seemingly something
is amiss with that claim. Seeing what was the failing request [and the
error details] and what was the successful request, likely would expose
a difference beyond the casing.

FWiW:

Undelimited identifiers in SQL are automatically folded to upper
case, just like the CL command-line operates. None of the [presumably
functional] offers of examples for the proper invocation included
upper-case names, so whatever was the origin for any "not found" error,
apparently the issue was not due to upper vs lower vs mixed case.

If delimited names [aka identifiers], then "QSYS2" and "USERS", with
the appropriate [specific to the NAMING OPTION] qualifier-character;
e.g. the slash. The specification "QSYS2"/"USERS" is no different than
specifying Qsys2/Users because the latter is implicitly folded to
upper-case and for the former the double-quote as delimiters are
superfluous [thus in essence, the double-quotes are stripped], but the
specification of "Qsys2"/"Users" is different from specifying
"qsys2"/"users" because the double-quote as delimiter dictates no
folding of the names. Since some revision level [TR, PTF, ??], on V7
[IBM i 7.#], the ability exists to use the period as qualifier-character
irrespective the chosen SQL naming-option; in some earlier releases, in
my experience, scalar functions could not even be library-qualified when
using System [*SYS] naming, so they were limited to being found
according to the PATH.

As other replies have offered, what was referenced by qualified name
is not a TABLE or a VIEW, but a User Defined Table Function (UDTF). As
a User Defined Function (UDF) [table-function or scalar-function], the
name must be followed by parentheses [to distinguish the identifier as a
function vs a column-name or a variable-name], and any required
comma-separated arguments specified betwixt the left-paren and
right-paren characters. The function will be found when correctly
specified with both a compatible number of arguments and a compatible
data-type for each argument, irrespective the casing of non-delimited
names; in the given example, apparently zero-arguments is at least one
supported invocation. I do not know [nor really care much] why the SQL
insists the table-function reference be enclosed in a TABLE() for a
table-reference, instead of just intuiting the intent, I just appease
the picky language.