Re: Referencing fields from a UDTF

XMLROW is a reserved word in SQL, try to embed it in double quotes: "XMLROW" (or use a different name)

Mit freundlichen Grüßen / Best regards

Birgitta Hauser


"Shoot for the moon, even if you miss, you'll land among the stars." (Les Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them and keeping them!"
„Train people well enough so they can leave, treat them well enough so they don't want to.“ (Richard Branson)


-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Peter Dow
Sent: Mittwoch, 25. November 2020 00:59
To: midrange-l@xxxxxxxxxxxxxxxxxx
Subject: Referencing fields from a UDTF

In this example of a UDTF, it shows using it by referring to the fields it returns:

CREATE FUNCTION DEPTEMPLOYEES (DEPTNO VARCHAR(3))
RETURNS TABLE (EMPNO CHAR(6),
LASTNAME VARCHAR(15),
FIRSTNAME VARCHAR(12))
LANGUAGE SQL
READS SQL DATA
NO EXTERNAL ACTION
DETERMINISTIC
RETURN
SELECT EMPNO, LASTNAME, FIRSTNME FROM EMPLOYEE
WHERE EMPLOYEE.WORKDEPT = DEPTEMPLOYEES.DEPTNO

The following is a SELECT statement that makes use of DEPTEMPLOYEES:

SELECT EMPNO, LASTNAME, FIRSTNAME FROM TABLE(DEPTEMPLOYEES('A00')) AS D



When I try something similar with my own UDTF,

create or replace function XMLFMTLOG
(varchar(64))
returns table
(
XMLrow varchar(2048)
)
language rpgle
parameter style db2sql
no sql
not deterministic
disallow parallel
external name 'QGPL/SVCSQL(XMLFMTLOG)';

it fails with

Keyword FROM not expected. Valid tokens: (.
select XMLrow
from table(xmlfmtlog(
'PROXY_2020-06-02-09.43.38.964000_log.txt')) x

It works just fine if I use

select *
from table(xmlfmtlog(
'PROXY_2020-06-02-09.43.38.964000_log.txt')) x

What am I doing wrong?

--
*Peter Dow* /
Dow Software Services, Inc.
909 793-9050
petercdow@xxxxxxxxx <mailto:petercdow@xxxxxxxxx> pdow@xxxxxxxxxxxxxx <mailto:pdow@xxxxxxxxxxxxxx> /
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related questions.

Help support midrange.com by shopping at amazon.com with our affiliate link: https://amazon.midrange.com