Subject more FB arithmetic weirdness csswa 2002-05-29T03:32:04Z
earlier, I wrote:

> a float in there then cast all as floats...
>
> so I tried this line: RET_RESULT = 100 / 300.3;
>
> answer... 0.3! So the parser needs at least one float value in
there
> to guarantee automatic casting.
>
> So then I figured, what if you stick in a dummy value to force a
> float casting... here's the new procedure code I tried...
>
> begin
> ret_result = 0;
> if (par_days < 1) then exit;
> ret_result = (par_sample_count / (par_days + 0.0)) * 30.4;
> EXIT;
> end
>
> now the sql statement: EXECUTE PROCEDURE SP_SAMPLES_PER_QUARTER
> (300,100) correctly returns 9.12!! and without the horrible
> individual integer casting.

If you do the math, that answer is actually wrong: (100/300)*30.4 =
10.13333333333...

So obviously par_days in being cast to one decimal place. I tried it
with 'par_days + 0.00' and got the result 10.032 -- an extra decimal
place. It would seem that if I keep increasing the decimal places
of '0.0' that the result's decimal places would keep increasing,
right?

Nope. The result never returns a scale of greater than 3 places:

ret_result = (100 / (300 + 0.000000)) * 30.4

gives a result of 10.133 -- why no extra decimal places?

Also, this line:

ret_result = (cast(100 as double precision) / cast(300 as double
precision)) * 30.4;

returns a value of '10.133'. What happened to the recurring 3? It's
a double precision, for crying out loud! It can store a scale of up
to 15 yet I can't get it to return a scale greater than 3.

Back to trying to decipher that numerics document...

Regards,
Andrew Ferguson
-- A chicken in every pocket.

>
> I just have to remember to force casting in this manner.
>
> Thanks for the replies.
>
> Regards,
> Andrew Ferguson
> -- I know what's best for you but I'm not telling.
>
>
>
> --- In ib-support@y..., "Martijn Tonies" <m.tonies@u...> wrote:
> > Hi,
> >
> > > I have the following procedure. It takes two integers, divides
> them,
> > > returns a float:
> > >
> > > CREATE PROCEDURE SP_SAMPLES_PER_QUARTER (
> > > PAR_DAYS INTEGER,
> > > PAR_SAMPLE_COUNT INTEGER)
> > > RETURNS (
> > > RET_RESULT DOUBLE PRECISION)
> > > AS
> > > begin
> > > --1-- RET_RESULT = 100 / 300;
> > > --2-- RET_RESULT = CAST((100 / 300) AS DOUBLE PRECISION);
> > > --3-- RET_RESULT = 0.123;
> > > --4-- RET_RESULT = 1 + 1;
> > > EXIT;
> > > end
> > >
> > > The two test lines (3 and 4) correctly return their values.
> Lines 1
> > > and 2 return 0. What am I missing here? Do I need to cast
> > > something??
> >
> > 100 / 300 probably returns 0, right? Because both 100 and 300 are
> > integers, the formula returns 0 (0.3 rounded).
> >
> > Read some more on numerics in dialect 3...
> >
> > You can solve it by (not tested):
> >
> > ret_result = cast(100 as double precision) / cast(300 as double
> precision);
> >
> >
> > Martijn Tonies
> > InterBase Workbench - the developer tool for InterBase and
Firebird
> > http://www.interbaseworkbench.com
> >
> > Upscene Productions
> > http://www.upscene.com
> >
> > "This is an object-oriented system.
> > If we change anything, the users object."