Sorry I am of no more use with this one. We are at 7.1 and that function is not on our system. Try renaming in it in a CTE.
-Matt
-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Rob Berendt
Sent: Thursday, April 27, 2017 8:16 AM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
Subject: RE: Querying an IBM supplied UDTF where some columns are reserved words
So is the doc wrong then?
SQL State: 42703 Vendor Code: -206 Message: [SQL0206] Column or global
variable Current_User not found. Cause . . . . . : Current_User was not
found as a column of table *N in *N and was not found as a global variable in *N. If the table is *N, Current_User is not a column of any table or view that can be referenced, or Current_User is a special register that
cannot be set in an atomic compound statement. Recovery . . . : Do one
of the following and try the request again: -- Ensure that the column and table names are specified correctly in the statement. -- If this is a SELECT statement, ensure that all the required tables were named in the FROM clause. -- If the column was intended to be a correlated reference, qualify the column with the correct table designator. -- If the column was intended to be a global variable, qualify the name with the schema where the global variable exists or ensure the schema is in the path. -- If this is a SET statement for a special register within an atomic compound dynamic statement, remove the statement or remove the ATOMIC keyword.
http://ibm.biz/DB2foriServices
Rob Berendt
--
IBM Certified System Administrator - IBM i 6.1 Group Dekko Dept 1600 Mail to: 2505 Dekko Drive
Garrett, IN 46738
Ship to: Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com
From: "Tyler, Matt" <matt.tyler@xxxxxxxxxxxxxx>
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
Date: 04/27/2017 10:07 AM
Subject: RE: Querying an IBM supplied UDTF where some columns are
reserved words
Sent by: "MIDRANGE-L" <midrange-l-bounces@xxxxxxxxxxxx>
Reserved Name as "Reserved Name"
select "Reserved Name" from Table
where "Reserved Name" = Value
order by "Reserved Name"
-Matt
-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Rob
Berendt
Sent: Thursday, April 27, 2017 7:57 AM
To: midrange-l@xxxxxxxxxxxx
Subject: Querying an IBM supplied UDTF where some columns are reserved
words
How do I query an IBM supplied UDTF where some of the columns are reserved
words?
SELECT journal_code, journal_entry_type,
"Current_User", job_name, Job_user, job_number,
Remote_port, Remote_address, system_name,
Entry_Timestamp, Receiver_name,
Program_name, Program_library,
OBJECT, object_type,
X.*
FROM TABLE(QSYS2.Display_Journal('QSYS', 'QAUDJRN', -- Journal library and
name STARTING_TIMESTAMP => CURRENT TIMESTAMP - 24 HOURS -- Time period
)) AS x
where "CURRENT_USER" in('TOM', 'DICK', 'HARRY') or
Job_user in('TOM', 'DICK', 'HARRY');
http://ibm.biz/DB2foriServices
Rob Berendt
midrange.com
