Re: C question
Why not use the ROUND function in SQL:
CREATE TEMP TABLE k
(
qty INTEGER,
cst DECIMAL(8,4)
) WITH no LOG;
INSERT INTO k VALUES (2, 1.002);
INSERT INTO k VALUES (2, 1.002);
INSERT INTO k VALUES (3, 1.002);
INSERT INTO k VALUES (1, 1.004);
SELECT SUM(cst * qty) ext_cst1 FROM k;
SELECT ROUND(cst * qty, 2) ext_cst2 FROM k;
SELECT SUM(ROUND(cst * qty, 2)) ext_cst3 FROM k;
Using my SQLCMD and OnLine 5.02.UC1 on SunOS 4.1.3, I get the required results:
sqlcmd -d stores -xf kk.sql
+ select sum(cst * qty) ext_cst1 from k;
8.0180
+ select round(cst * qty, 2) ext_cst2 from k;
2.00
2.00
3.01
1.00
+ select sum(round(cst * qty, 2)) ext_cst3 from k;
8.01
You might care to note that the sum 8.0180 would be printed out as 8.02 to
2 decimal places; ISQL and DBACCESS indeed print the result of the first
SELECT as 8.02.
Yours,
Jonathan Leffler (johnl@informix.com) #include <disclaimer.h>
>From: BrianG2621@aol.com
>Date: Fri, 15 Jul 94 01:04:40 EDT
>Subject: C question
>X-Informix-List-Id: <list.4302>
>We need to calculate (in a C program) a total cost that is the sum of an
>invoice's detail lines' extended costs (qty * cst). The problem is that the
>detail line cost is a decimal(8,4), and the total cost is a decimal(10,2).
>Selecting sum(qty*cst) doesn't work because the detail lines need to be
>rounded to the penny on each line--something like selecting sum(ext_cst),
>except that the database is fairly normalized, and there is no extended cost.
>There are several programs involved,
>a 4gl program and 2 C programs. in the 4gl program, I simply define a
>decimal(8,2) as an extended line cost, and spin through the array summing
>that to get the total.
>To illustrate, consider the following:
>qty cst ext_cst
>2 1.002 2.00
>3 1.002 2.01
>1 1.004 2.00
>totals calculating by sum(ext_cst)
> 8.01
>by sum (qty*cst)
> 8.02
> ...