Subject | Re: St proc refuses to divide - solution |
---|---|

Author | csswa |

Post date | 2002-05-28T17:43:36Z |

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.

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