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 userdefined 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
