× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



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'
;


As an Amazon Associate we earn from qualifying purchases.

This thread ...

Follow-Ups:
Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.