I just added the following to the SP:

SELECT CAST((:OUTSTANDING / :NDC_QTY) AS INTEGER) FROM
RDB$DATABASE INTO :UNIT_CNT;
-- FB rounds instead of truncating!!!
-- UNIT_CNT = CAST(OUTSTANDING / NDC_QTY AS INTEGER);
if (OUTSTANDING=180 AND NDC_QTY=100) THEN
BEGIN
EXCEPTION E 'UNIT_CNT=' || UNIT_CNT;
END

The exception that was thrown was:
UNIT_CNT=2

Which means that for some reason, the SELECT statement inside an SP
rounds, but when executed on its own it truncates correctly.
I tried using the TRUNCATE() UDF, but it too is returning 2 for
TRUNCATE(180/100).

Is there any workaround? This is a show-stopper for us.

-----Original Message-----
From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] On Behalf Of Rick Debay
Sent: Thursday, May 26, 2005 2:57 PM
To: firebird-support@yahoogroups.com
Subject: [firebird-support] Truncation and rounding 'feature' in SP

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?!


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

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