I need to make a SQL call to a User Defined Table Function...aka UDTF.

I have never done one and can't seem to find anything in help contents that shows how.

I  tried it this way...

get styleSearchDetail with #sql{SELECT SYSTYP, SYSTYL, SYDESC2, SYACTV, SYCOLOR, SYMFGL, SYMFGS
                                                  FROM TABLE(FINDPMSTYD(:SYSTYP, :STYLECODE, :SYMFGN, :SYMFGS, :SYMFGL,
                                                                   :FINDDESC, :FLDNBR1, :FLDNBR2, :FLDNBR3, :FLDNBR4,
                                                                   :VALUE1, :VALUE2, :VALUE3, :VALUE4, 'Y', '1'
                                                                    )) AS X
                                                 ORDER BY SYSTYP, SYSTYL };

I get an Error: 

IWN.VAL.4513.e 0/0 Validation for EGL GET statement failed because the database manager returned this error: SQLException: [SQL0418] Use of parameter marker not valid. ErrorCode:  -418.

Hi,

here is an example from IBM:

-- 1. Add Where ConditionSELECT * FROM TABLE(USERS()) uWHERE ODOBNM LIKE 'TM%';-- 2. Calling a UDTF with Parameters: ListMember_Fnc (List Member)   SELECT * FROM TABLE(ListMember_Fnc('QRPGLESRC', 'HSCOMMON10')) a      WHERE     MbrType LIKE '%RPGLE%'            AND Mbr     LIKE '%LIST%' ;-- 3. Joining UDTFsSELECT *   FROM           TABLE(ListMember_Fnc('QRPGLESRC', 'HSCOMMON10')) a        FULL JOIN TABLE(ListMember_Fnc('QRPGLESRC', 'HSCOMMON05')) b               ON a.Mbr = b.Mbr      WHERE    a.MbrDescr LIKE '%File%'            OR b.MbrDescr LIKE '%File%';

Because of the error 'Use of parameter marker not valid' I think in your SQL statement the colons (':') are wrong.

Kind regards

Your supplied example is passing literal parms.  The problem seems to be that EGL does not know how to handle program variables when using a table function (UDTF).  Variables FLDNBR1, FLDNBR2, FLDNBR3 and FLDNBR4 are packed decimal, wonder if that's where EGL is struggling. The table function runs just fine when executed in an RPG program.

Hi,

If the use of a UDTF is not compatible with the get statement as you have it, you could try using the EGL prepare statement or create a stored procedure from the UDTF and call the stored procedure instead.

-Daron