Re: SQL User Defined Functions

[Colin Williams <colin.williams@xxxxxxxxxxxx>]

Sorted out my SQL User Defined Functions library list problem.
Basically what I was trying to do was create a UDF that refered to an RPG
program, but searched *LIBL for the program rather than hardcoding the
library in the CREATE FUNCTION statement.

The trick is to not put the EXTERNAL NAME in quotes(no idea why I did that).
Eg

CREATE FUNCTION COLINDEV/CVTDAT (DEC (8,0)) RETURNS DEC(8,0)
LANGUAGE RPGLE NO SQL PARAMETER STYLE DB2SQL                  
RETURNS NULL ON NULL INPUT NO EXTERNAL ACTION                 
EXTERNAL NAME 'SQL0020' DETERMINISTIC FINAL CALL

Gives error message SQL0449 - External program name for routine SQL0020 in
BERUSRPGM not valid

But 

CREATE FUNCTION COLINDEV/CVTDAT (DEC (8,0)) RETURNS DEC(8,0)
LANGUAGE RPGLE NO SQL PARAMETER STYLE DB2SQL                  
RETURNS NULL ON NULL INPUT NO EXTERNAL ACTION                 
EXTERNAL NAME SQL0020 DETERMINISTIC FINAL CALL

Creates the function and by checking file SYSFUNCS, EXTERNAL NAME is
*LIBL/SQL0020

So I can have test version of my SQL UDF's, that do not affect the
production versions, yippe

Found the answer in www.ignite400.org/news/pdf/news2003031001.pdf

Colin.W
For the latest on Bertram Books products and services and for up-to-the-minute 
book news visit www.bertrams.com


This e-mail and any attachments may contain information that is confidential 
and privileged and is solely for the use of the intended recipient. If you are 
not the intended recipient please notify the sender and delete this e-mail and 
any attachments immediately