Stored Proc precision

Goodz13 · #1 (**permalink**) 07-05-07, 10:48

Hello,

I have a stored procedure, but for some reason, the precision is incorrect.

Code:

CREATE PROCEDURE MYDB.MYPROC
	(IN INVAL VARCHAR(7), IN EFFDATE TIMESTAMP, OUT OUTVAL NUMERIC(19,9))
	LANGUAGE SQL
    BEGIN
    
        DECLARE NAR NUMERIC(19,9);
        DECLARE RATE NUMERIC(19,9);
        DECLARE FUNDS NUMERIC(19,9) DEFAULT 0.00;

        -- SELECT ATATEMENT FOR FUNDS

        SET NAR = FUNDS / 1.003674800; -- Constant Factor

        -- SELECT STATEMENT FOR RATE
	 	
        SET OUTVAL = ( RATE * NAR / 1000.000000000 / 12.000000000 ) ;	

END @

I ran the proc and the FUNDS was equal to 50000.000000000, RATE was equal to 3.58428 and the NAR was equal to 49816.932735583, so the OUTVAL should have been 14.879819639. BUT It was equal to 14.000000000. What is going on? Is there a way to set the precision for calculations?

If it makes a difference, I am running DB2 on AS400.