Hi I'm having problems creating a function in DB2 8 for z/OS that
contains basic procedural language such as "declares"
The only function I have been able to successfuly compile is a code
snippet I found for emulating the SQL Server ISNUMERIC function (Code
at end of post). The defining rule seems to be that only a block of
code that returns a single value can proceed the 'RETURN" function, the
example below has a case statement that returns either a 1 or 0. If I
try to place any code before the return such as declaring a variable
and then using the variable in the return statement, the code fails to
compile.
Does anyone have any insight.
Thanks
Mark.
CREATE FUNCTION MARK.ISNUMERIC2 ( SOURCE VARCHAR(40) FOR SBCS
DATA CCSID EBCDIC )
RETURNS INTEGER
SPECIFIC ISNUMERIC2
LANGUAGE SQL
NOT DETERMINISTIC
EXTERNAL ACTION
READS SQL DATA
CALLED ON NULL INPUT
RETURN CASE WHEN translate(
source,'','0123456789.-+') <'' THEN 0 WHEN
posstr(ltrim(
source),'-') 1 OR posstr(ltrim(
source),'+') 1 THEN 0 WHEN
length(rtrim(ltrim(translate(
source,'','0123456789.')))) 1 OR length(rtrim(ltrim(translate(
source,'','0123456789-+')))) 1 THEN 0 WHEN
posstr(ltrim(rtrim(translate(
source,'','-+'))),' ') 0 THEN 0 WHEN
translate(
source,'','.-+') = '' THEN 0 ELSE 1 END;