Re: udf with imbedded SQL

[rob@xxxxxxxxx]

I've cleaned this up a little with

drop function FAXNEWAREA

create function FAXNEWAREA (CHAR (3), CHAR (3))
returns CHAR (3)
simple call
language rpgle
deterministic
reads sql data
returns null on null input
no external action
allow parallel
external name 'ROUTINES/SRVPGM(FAXNEWAREA)'

But

SELECT CCUST, CPHON,
FAXA(CPHON) as FaxNumber,
FAXAREACODE(FAXA(CPHON)) as AreaCode,
FAXEXCHANGE(FAXA(CPHON)) as Exchange,
FAXNUMBER(FAXA(CPHON)) as Number,
FAXNEWAREA('219','347')
   as NewAreaCode
FROM ROB/RCMASH

still results in
Message ID . . . . . . :   SQL0204       Severity . . . . . . . :   30
Message type . . . . . :   Diagnostic
Message . . . . :   FAXNEWAREA in *LIBL type *N not found.
Cause . . . . . :   FAXNEWAREA in *LIBL type *N was not found. If this is
an
  ALTER TABLE statement and the type is *N, a constraint was not found. If
  this is not an ALTER TABLE statement and the type is *N, a function,
  procedure, or trigger was not found.
Recovery  . . . :   Change the name and try the request again.  If the
object
  is a node group, ensure that the DB2 Multisystem product is installed on
  your system and create a nodegroup with the CRTNODGRP CL command.

                     Display Service Program Information
                                                               Display 5 of
10
Service program  . . . . . . . . . . . . :   SRVPGM
  Library  . . . . . . . . . . . . . . . :     ROUTINES
Owner  . . . . . . . . . . . . . . . . . :   PROGRAMMER
Service program attribute  . . . . . . . :   RPGLE
Detail . . . . . . . . . . . . . . . . . :   *PROCEXP
                              Procedure Exports:
Procedure Name
ARGOPT
ADDDAYS                                                                 *NO
CENTER40                                                                *NO
CHECKPRF                                                                *NO
COMPANYNAME                                                             *NO
DATEEDIT                                                                *NO
DAYNAMEL                                                                *NO
DAYNAMES                                                                *NO
DAYOFWEEK                                                               *NO
DAYSDIFF                                                                *NO
EXECCMD                                                                 *NO
STRNETSVRP                                                              *NO
RUNCMDAPI                                                               *NO
SPLJOB                                                                  *NO
IFSTEMPFILE                                                             *NO
CENTER                                                                  *NO
CTON                                                                    *NO
FAXA                                                                    *NO
FAXN                                                                    *NO
FAXAREACODE                                                             *NO
FAXEXCHANGE                                                             *NO
FAXNUMBER                                                               *NO
FAXNEWAREA                                                              *NO


Rob Berendt
--
"They that can give up essential liberty to obtain a little temporary
safety deserve neither liberty nor safety."
Benjamin Franklin