Chuck, thanks so much for the clearer explanation. I printed the first one to read later but after this one I think I understand.
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 CRPence
Sent: Thursday, October 08, 2015 5:15 PM
To: rpg400-l@xxxxxxxxxxxx
Subject: 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'
;
--
Regards, Chuck
--
This is the RPG programming on the IBM i (AS/400 and iSeries) (RPG400-L) mailing list To post a message email: RPG400-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit:
http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives at
http://archive.midrange.com/rpg400-l.
midrange.com
