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

Problem with a user defined function

P: 2
HI,

I am completely new to DB2 and I am trying to get this user defined function to work. It doesn't seem to like the way I am using my input variables in the body of the function. I get the following error.

Create user-defined function returns -206.
DB15USER.random: 13: [IBM][CLI Driver][DB2/NT] SQL0206N "V_A" is not valid in the context where it is used. LINE NUMBER=13. SQLSTATE=42703


Also, how do I set the default values of my parameters, I would like to set them to null.

Any help would appreciated.

Thanks.


CREATE FUNCTION DB15USER.random (v_source INTEGER, v_distribution CHAR(3), v_a DOUBLE, v_b DOUBLE, v_c DOUBLE) RETURNS
INTEGER

LANGUAGE SQL
MODIFIES SQL DATA
CALLED ON NULL INPUT

BEGIN ATOMIC

DECLARE v_random INTEGER;
DECLARE v_s DOUBLE;

IF v_a IS NULL THEN
SET v_a = 0;
END IF;

IF v_b IS NULL THEN
SET v_b = 1;
END IF;

IF v_distribution IS NULL THEN
SET v_distribution = 'Uni';
END IF;

IF v_c IS NULL THEN
SET v_c = (v_a + v_b) / 2;
END IF;

SET v_s = CAST (v_source AS DOUBLE) / 100000000;

--SET v_s = 1;


IF v_distribution = 'Exp' THEN
IF (v_source = 0) THEN
SET v_source = 1;
END IF;

SET v_random = -1 * LN(v_s) * v_a;
RETURN v_random;
END IF;

IF v_distribution = 'Nor' THEN
IF (v_s = 0) THEN
SET v_s = 1;
END IF;
SET v_random = SQRT(-2 * LN(v_s)) * SIN(2 * MS7.PI() * (CAST (v_c AS DOUBLE) / CAST (100000000 AS DOUBLE))) * v_b + v_a;
RETURN v_random;
END IF;

IF v_distribution = 'Sk1' THEN
SET v_s = SQRT(v_s);
END IF;

IF v_distribution = 'Sk2' THEN
SET v_s = MS7.SQUARE(v_s);
END IF;

IF (v_s < 0) THEN
SET v_s = 0;
END IF;

IF (v_s > 1) THEN
SET v_s = 0.99999999;
END IF;

SET v_random = v_s * (v_b - v_a + 1) + v_a;

RETURN v_random;

END
Oct 29 '07 #1
Share this question for a faster answer!
Share on Google+

Post your reply

Sign in to post your reply or Sign up for a free account.