Getting rounded off values from database.

Nesh has asked for the wisdom of the Perl Monks concerning the following question:

Hello monks.. I connect to a database through this script to pull out the values from the database and write them to a text file. The database looks like this:

BA    1/1/2005    12/31/2999    11.3700000000000016
BO    1/1/2005    12/31/2999    7.5400000000000000
EQ    1/1/2005    12/31/2999    12.0700000000000000
FI    1/1/2005    12/31/2999    3.0000000000000000
MO    1/1/2005    12/31/2999    3.8000000000000000
SP    1/1/2005    12/31/2999    12.9900000000000000
[download]

My script looks like:

use warnings;
use strict;
use DBI;
use POSIX qw(strftime);

my $currSysDate =strftime ("%m/%d/%Y", localtime);
my $currYear =strftime("%Y", localtime);
my $dbh;
my $dsn = "dbi:Oracle:hod3";
my $user = 'dbo';
my $password = 'dbo';
my $mffundclass;
my $mfstartdate;
my $mfenddate;
my $mfror;
my $mdstartdate;
my $mdenddate;
my $mddiscountrate;

if($currSysDate eq "06/29/$currYear"){
    
    open ROR, ">>RORTableValues_Nav.txt" or die "Error in opening RORT
+ableValues_Nav.txt";

    open DISCOUNT, ">>DiscountRateTableValues_Nav.txt" or die "Error i
+n opening DiscountRateTableValues_Nav.txt";
    printf ROR ("MF_FUND_CLASS \t MF_START_DATE \t MF_END_DATE \t MF_R
+OR \n");
    printf DISCOUNT ("MD_START_DATE \t MD_END_DATE \t MD_DISCOUNT_RATE
+ \n");
    
    $dbh = DBI->connect($dsn, $user, $password, {PrintError => 1, Rais
+eError => 1}) or die ("Can't execute statement: $DBI::errstr\n");
    
    my $sql = qq{select * from T_LIMF_MRD_FUND_ROR};
    my $sth = $dbh->prepare($sql);
    $sth->execute();
    $sth->bind_columns(\$mffundclass,\$mfstartdate,\$mfenddate,\$mfror
+);
    while ($sth->fetch()){
        print "$mffundclass \t $mfstartdate \t $mfenddate \t $mfror \n
+";
        printf ROR "$mffundclass \t $mfstartdate \t $mfenddate \t $mfr
+or \n";
    };
    $sth->finish();
    
    my $sql1 = qq{select * from T_LIMD_MRD_DISCOUNT_RATE};
    my $sth1 = $dbh->prepare($sql1);
    $sth1->execute();
    $sth1->bind_columns(\$mdstartdate,\$mdenddate,\$mddiscountrate);
    while ($sth1->fetch()){
        printf DISCOUNT "$mdstartdate \t $mdenddate \t $mddiscountrate
+ \n";
    };
    $sth1->finish();
    
    $dbh->disconnect();
}
[download]

Now this script instead of giving the 4th column exactly as it is rounds of the value and returns the rounded value. What changes do I need to make to get the exact values. Thanks Neshat

Comment on Getting rounded off values from database. Select or Download Code

Replies are listed 'Best First'.
Re: Getting rounded off values from database. by Codon (Friar) on Jul 07, 2005 at 00:16 UTC
You are hitting the limits of Perl's default precision. You will need to devise some way to either force perl to think that your numbers are strings (default values in you bound variables?) or make Perl use a greater precision. See the following examples: `$ perl -le '$foo = 11.3700000000000016; print $foo' 11.37 $ perl -le '$foo = .00000000000016; print $foo' 1.6e-13 $ perl -le '$foo = 1.00000000000016; print $foo' 1.00000000000016 $ perl -le '$foo = 1.3700000000000016; print $foo' 1.37 $ perl -le '$foo = 1.370000000000016; print $foo' 1.37000000000002 $ perl -le '$foo = .370000000000016; print $foo' 0.370000000000016` [download] Ivan Heffner Sr. Software Engineer, DAS Lead WhitePages.com, Inc.	[reply] [d/l]
Re: Getting rounded off values from database. by monarch (Priest) on Jul 06, 2005 at 23:19 UTC
My question is this. Does the first row print all 16 digits to the right of the decimal point? Then the others print 0, 1, or 2 digits to the right of the decimal point? e.g. is the output something like this?: `1/1/2005 12/31/2999 11.3700000000000016 1/1/2005 12/31/2999 7.54 1/1/2005 12/31/2999 12.07 1/1/2005 12/31/2999 3 1/1/2005 12/31/2999 3.8 1/1/2005 12/31/2999 12.99` [download] ..because if it is then I suspect that what you want to do is print 16 digits to the right of the decimal point regardless of whether they are significant or not. In which case do something like this for your printing line: `printf( DISCOUNT "%s\t%s\t%.16f\n" $mdstartdate,$mdenddate,$mddiscountrate );` [download]	[reply] [d/l] [select]
Re^2: Getting rounded off values from database. by Nesh (Beadle) on Jul 06, 2005 at 23:26 UTC
No,the output looks like this: `BA 01-JAN-05 31-DEC-99 11.37 BO 01-JAN-05 31-DEC-99 7.54 EQ 01-JAN-05 31-DEC-99 12.07 FI 01-JAN-05 31-DEC-99 3 MO 01-JAN-05 31-DEC-99 3.8 SP 01-JAN-05 31-DEC-99 12.99` [download]	[reply] [d/l]
Re: Getting rounded off values from database. by anonymized user 468275 (Curate) on Jul 07, 2005 at 11:01 UTC
Looking at the code, the rounding is being done by the fetch, not after populating the local variables, so the above solution would be too late. How about converting the data to string in situ before the fetch using the DBMS, i.e. change the line assigning $sql to `my $sql = 'select MF_FUND_CLASS, MF_START_DATE, MF_END_DATE, convert( char(64), MF_ROR ) from T_LIMF_MRD_FUND_ROR';` [download] The above example is for Sybase. For mysql, the parameters to the convert function are in the reversed order. One world, one people	[reply] [d/l]