I just assumed the server would be smart enough to look at the
variable it was going into (double) and cast accordingly. After all,
it handles RET_RESULT = 0.234 + 1 correctly (1.234) so it knows to
handle those as floats...

well well, that last statement made me think: how does it KNOW that
there are floats to deal with instead of integers... it must look at
the values... if all are integers then cast as integer... if there is
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.

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."