Re: weird happening with Money field in view

On 15 Dec 2005 12:51:04 -0800, jda <adamski@graceland.edu> wrote: > We are having a weird situation with a money field that hopefully > someone can shine a light on to why it's happening and how to fix or > code around it. Which product? Which version? Which platform? > We have a proc that basically sums up a money field based on selection > criteria passed into it. The proc does a simple select (nvl (sum amt), > 0) on the field amt which is defined in the database as a money field. > > Using debug the amt for our test case sums to $335.21, which is correct > if we hand total the records. However when the proc does the let > return_var =, we get 335.2099999999999800 which is being interpreted by > the view that calls the proc as $335.20 It looks as if the evaluation process goes through a C double at some point and this loses some accuracy, and then a conversion occurs that truncates rather than rounds. The question is where. > Its not happening on every person only a handful. Why are we getting > these weird numbers. I'm not clear what the SELECT NVL(SUM AMT, 0) notation is supposed to mean. The space between SUM and AMT looks like a syntax error to my eyes. If you are trying NVL(SUM(amt), 0), then I think the NVL is redundant - unless you could ever have a summation over only null amt values. Can you show a data set and stored procedure that reproduces the problem - not using your real table but using the real amounts that add up to 335.21, and using a suitably transformed version of your SP? -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2005.02 -- http://dbi.perl.org/ sending to informix-list