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

I think that's what I tried first and again now
SELECT * FROM qsys2/user_info
USER_INFO in QSYS2 type *FILE not found.

I did get the table function to work but I had to have the x like this:
SELECT * FROM Table(Qsys2/Users()) x
Guess I should search out table functions to see what that's for.

Thanks,

Dave B

Everybody is ignorant, only on different subjects. - Will Rogers


-----Original Message-----
From: RPG400-L [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Glenn Gundermann
Sent: Thursday, October 08, 2015 2:01 PM
To: RPG programming on the IBM i (AS/400 and iSeries) <rpg400-l@xxxxxxxxxxxx>
Subject: Re: AW: Retrieve a user's full name (not the User ID)

Hi Dave,

users() is not a table. It is a user-defined table function (UDTF).
See how Birgitta and others are using it below.

If you are looking for a table, you can look at qsys2.user_info as someone has already pointed out.


Yours truly,

Glenn Gundermann
Email: glenn.gundermann@xxxxxxxxx
Work: (416) 675-9200 ext. 89224
Cell: (416) 317-3144


On 8 October 2015 at 12:02, Dave Boettcher <Dave.Boettcher@xxxxxxxxxxxxxxxxx

wrote:

I'm trying this out in interactive SQL and can't seem to find the
qsys2/users table. Is it only available for embedded SQL?
Quite often I need to search if an employee has an ID on the IBM i and
this could be handy. Right now we create a file of user profiles each
week but if this file is there it would work too.
Right now I use a statement like this on our file of users:
SELECT * FROM qgpl/userfile WHERE upper(uptext) like '%DAVE%'

Any questions, let me know please.

Thanks,

Dave B

Everybody is ignorant, only on different subjects. - Will Rogers


-----Original Message-----
From: RPG400-L [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of
darren@xxxxxxxxx
Sent: Thursday, October 08, 2015 10:27 AM
To: RPG programming on the IBM i (AS/400 and iSeries) <
rpg400-l@xxxxxxxxxxxx>
Subject: Re: AW: Retrieve a user's full name (not the User ID)

I was amazed by this function, but confused because I hadn't seen it
before. I went looking for it in a reference chart I have from IBM
online, and I didn't find this table function, but I did find the
QSYS2.USER_INFO view. This view seems more restrictive by my
authority to this data, possibly because there are a lot of fields in
there. This QSYS2.USER() table function returns more results,
possibly because the name of the user isn't subject to tight profile controls. I find that interesting.

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;