By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
454,275 Members | 1,545 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 454,275 IT Pros & Developers. It's quick & easy.

z/OS UDB 8.1 and Functions vs Stored Procedures

P: n/a
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;

Sep 11 '06 #1
Share this question for a faster answer!
Share on Google+

This discussion thread is closed

Replies have been disabled for this discussion.