Re: How do I create a UDF?

[Vern Hamberg <vhamberg@xxxxxxxxxxx>]

Michael

Interactive SQL has not kept up with most of the new stuff in SQL on i - 
it does not even support the JOIN keywords. But, as Birgitta said, you 
do have the ability to execute a CREATE FUNCTION command.

Too bad they stopped improving that interface - I cut my SQL teeth in 
there and didn't even bleed all that much!

Vern

Koester, Michael wrote:
> (If this would be more appropriate to the RPG or WDSC lists, let me
> know.)
>
>  
>
> I noticed some articles that reference some very handy user defined
> functions that I'd like to incorporate and use in embedded SQL
> statements.  Having never created a UDF before, I thought I could copy
> some sample code and make it happen.  It appears that CREATE FUNCTION is
> not available to me.  Interactive SQL does not list FUNCTION as a
> supported argument to the CREATE verb.  Coding it into SQLRPGSRC makes
> the WDSCi syntax-checker mad (it doesn't like the "Language SQL"
> statement, among others), so I am wondering if I'm going about this all
> wrong, or if we don't have the necessary licensed programs on our
> machine, or ???
>
> The UDF I tried to create is:
>
> Create Function myLib/IsNumeric 
>
> (@TestData VarChar(64))
>
> Returns Char(1)
>
> Deterministic
>
> Not Fenced
>
> Language SQL
>
> Set Option Commit=*None, UsrPrf=*Owner 
>
> Begin
>
>  Declare @Double Double Not Null Default 0;
>
>  Declare Exit Handler For SQLException 
>
>   Return 'N'; 
>
>  If @TestData Is Null Then 
>
>   Return 'N';
>
>  End If; 
>
>  Set @Double=Cast(@TestData As Double); 
>
>  Return 'Y';
>
> End
>
> I didn't see anything that ran counter to the syntax in the WDSCi
> Reference.
>
> Running V5R4 and WDSCi 7.0.0.8  Been doing embedded SQL programming here
> for over a year. 
>
> TIA.
> --Michael Koester
>
>  
>
>