Thanks.

-----Original Message-----
From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] On Behalf Of Helen Borrie
Sent: Thursday, May 26, 2005 8:33 PM
To: firebird-support@yahoogroups.com
Subject: Re: [firebird-support] Truncation and rounding 'feature' in SP

At 02:57 PM 26/05/2005 -0400, you wrote:

>DECLARE VARIABLE UNIT_CNT INTEGER;
>DECLARE VARIABLE OUTSTANDING DECIMAL(12,3); DECLARE VARIABLE NDC_QTY
>DECIMAL(12,3);
>
>OUTSTANDING = 180;
>NDC_QTY = 100;
>
>UNIT_CNT = CAST(OUTSTANDING / NDC_QTY AS INTEGER);
>
>UNIT_CNT is returned as 2, so FB is rounding when it should be
>truncating.
>
>SELECT CAST(180 / 100 AS INTEGER) from rdb$database
>
>Returns 1, which is correct.
>
>Is there any workaround, besides using select into statements to do all

>my math?!

Workaround? In SQL, you get what you ask for. What you need to ask for
depends a lot on the data types of the operands. Your two examples are
not equivalent. In your first example, a decimal was divided by a
decimal, which returns a decimal (180.000 / 100.000 = 1.800000).
Casting a decimal to an integer does a banker's rounding and returns 2.

In the second, an integer is divided by an integer. By SQL rules,
integer/integer division truncates any decimal portion from the result
and returns the integer portion alone. So the results you got were
correct in both cases.

Your solution is to be aware of how the arithmetic works for each data
type and use casting accordingly.

For example, if you *want* to truncate the result of decimal/decimal
division, don't try to cast it as an integer. Use some mechanism, e.g.
the UDF trunc(), to get the result you want.

./heLen




++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Visit http://firebird.sourceforge.net and click the Resources item on
the main (top) menu. Try Knowledgebase and FAQ links !

Also search the knowledgebases at http://www.ibphoenix.com

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Yahoo! Groups Links