udf with imbedded SQL

[rob@xxxxxxxxx]

I am using UDF's (User Defined Functions) in SQL and loving it!
The following line of SQL
SELECT CCUST, CPHON,
FAXA(CPHON) as FaxNumber,
FAXAREACODE(FAXA(CPHON)) as AreaCode,
FAXEXCHANGE(FAXA(CPHON)) as Exchange,
FAXNUMBER(FAXA(CPHON)) as Number,
FAXNEWAREA(FAXAREACODE(FAXA(CPHON)),FAXEXCHANGE(FAXA(CPHON))) as
NewAreaCode
FROM ROB/RCMASH
Calls a subprocedure for each function.  The following subprocedures exist
in the service program SRVPGM:
FAXA
FAXAREACODE
FAXEXCHANGE
FAXNUMBER
FAXNEWAREA
...
The only problem is that the first 4 have no imbedded SQL and they work
fine.  If I test FAXNEWAREA by calling it from a RPG program it works
great.  So I am thinking that my problem is how I declared the function.
Here's how I declared FAXNUMBER:
create function FAXNUMBER (CHAR (30))
returns CHAR (4)
language rpgle
deterministic
no sql
returns null on null input
no external action allow parallel
simple call
external name 'ROUTINES/SRVPGM(FAXNUMBER)'

Here is how I declared FAXNEWAREA
create function FAXNEWAREA (CHAR (3), CHAR (3))
returns CHAR (3)
language rpgle
deterministic
sql
returns null on null input
no external action allow parallel
external name 'ROUTINES/SRVPGM(FAXNEWAREA)'

Note:  I had to drop the 'simple call', or I'd get:
Message ID . . . . . . :   SQL0104       Severity . . . . . . . :   30
Message type . . . . . :   Diagnostic

Message . . . . :   Token SIMPLE was not valid. Valid tokens: NOT
  DETERMINISTIC CONTAINS NO READS MODIFIES.

Granted, I don't know what half of these lines do.

Here is a sample without FAXNEWAREA
SELECT CCUST, CPHON,
FAXA(CPHON) as FaxNumber,
FAXAREACODE(FAXA(CPHON)) as AreaCode,
FAXEXCHANGE(FAXA(CPHON)) as Exchange,
FAXNUMBER(FAXA(CPHON)) as Number
FROM ROB/RCMASH
 Cust  Cust Phone       FAXNUMBER                       AREACODE  EXCHANGE
NUMBER
 No
 9999  347-3100         93473100                          219       347
3100
71317  (219) 587-9116   912195879116                      219       587
9116
 4013  (616)555-1212    916165551212                      616       555
1212

But when I run:
SELECT CCUST, CPHON,
FAXA(CPHON) as FaxNumber,
FAXAREACODE(FAXA(CPHON)) as AreaCode,
FAXEXCHANGE(FAXA(CPHON)) as Exchange,
FAXNUMBER(FAXA(CPHON)) as Number,
FAXNEWAREA(FAXAREACODE(FAXA(CPHON)),FAXEXCHANGE(FAXA(CPHON)))
   as NewAreaCode
FROM ROB/RCMASH
Query cannot be run.  See lower level messages.

So I simplified this into:
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
And now I am getting:
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.


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