Re: Referencing fields from a UDTF

[Peter Dow <petercdow@xxxxxxxxx>]

Ah! Thanks Birgitta!

On 11/24/2020 8:28 PM, Birgitta Hauser wrote:
> 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