Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2007
    Posts
    2

    Exclamation Unanswered: Stored Proc precision

    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.

  2. #2
    Join Date
    Jun 2007
    Posts
    2
    Ok, this seems very odd. I changed the 1000.000000000 and the 12.000000000 to have only 4 decimal places and it worked fine.

  3. #3
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by Goodz13
    If it makes a difference, I am running DB2 on AS400
    Yes, that always makes a difference.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •