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

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

On 10/8/2015 6:15 AM, Birgitta Hauser wrote:

IBM Provided an UDTF (User Defined Function) named USERS(),
located within the QSYS2 library.
You easily can access this UDTF with (embedded) SQL:

Exec SQL
Select ODOBTX
into :HostVariable
from Table(Qsys2.Users()) x
Where ODOBNM = :HostUsrPrf
;

I can not make it compile with the "where" clause.

exec SQL
select ODOBTX
into :wUserName
from Table(QSYS2/USERS() )
where ODOBNM = 'MARTIN'

MSG ID SEV RECORD TEXT
SQL0104 30 143 Position 38 Token ODOBNM was not valid.
Valid tokens: FOR WITH FETCH ORDER UNION EXCEPT OPTIMIZE.

Contrasted with what Birgitta offered, there are two entities [two specific characters] conspicuously missing [the omissions are obvious to me, moreso after my reformatting]. Note the lack of the "X" and the lack of the semicolon just above; the issue causing the syntax error is the former. I already explained that issue here [http://archive.midrange.com/rpg400-l/201510/msg00057.html]

The given example could have been clearer, having qualified the column name(s), and including the optional AS keyword to make the X appear less like a spurious data from a mis-type\finger-check.

Try the following revision to what you have quoted above; I used CID instead of X, hoping to make clear, that the text is intentionally included, and the identifier as qualifier on the column-name references helps elucidate the purpose of the AS-identifier:

exec SQL
select CID.ODOBTX
into :wUserName
from Table( QSYS2/USERS() ) AS CID
where CID.ODOBNM = 'MARTIN'
;