Re: Querying an IBM supplied UDTF where some columns are reserved words

I did a "Generate SQL" against the function to see if perhaps the doc is
wrong.

CREATE FUNCTION QSYS2.DISPLAY_JOURNAL(JOURNAL_LIBRARY VARCHAR(10) ,
JOURNAL_NAME VARCHAR(10) ,\
...
RETURNS TABLE(ENTRY_TIMESTAMP TIMESTAMP, SEQUENCE_NUMBER DECIMAL(21,
0),
...
"CURRENT_USER" VARCHAR(10) FOR SBCS DATA , JOB_NAME VARCHAR(10) FOR SBCS
DATA ,
JOB_USER VARCHAR(10) FOR SBCS DATA , JOB_NUMBER VARCHAR(6) FOR SBCS DATA ,
...

Looks like the doc should be right.

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: Rob Berendt <rob@xxxxxxxxx>
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
Date: 04/27/2017 10:16 AM
Subject: RE: Querying an IBM supplied UDTF where some columns are
reserved words
Sent by: "MIDRANGE-L" <midrange-l-bounces@xxxxxxxxxxxx>



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

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: http://amzn.to/2dEadiD
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: http://amzn.to/2dEadiD


--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: http://amzn.to/2dEadiD


--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: http://amzn.to/2dEadiD