Watch, Follow, &
Connect with Us

QualityCentral

Public Report
Report From: InterBase/Server    [ Add a report in this area ]  
Report #:  62403   Status: Resolved
COALESCE() and SUM() or provide ISNULL() alike function
Project:  InterBase Build #:  8.0
Version:    8.0 Submitted By:   isaac sam
Report Type:  Basic functionality failure Date Reported:  5/21/2008 5:55:25 AM
Severity:    Serious / Highly visible problem Last Updated: 6/27/2008 5:17:18 PM
Platform:    All platforms Internal Tracking #:  
Resolution: Retest (Resolution Comments) Resolved in Build: : None
Duplicate of:  None
Voting and Rating
Overall Rating: No Ratings Yet
0.00 out of 5
Total Votes: None
Description
Recently I tried to make a stored procedure which has many selects contains the sum() function

After executing all the selects they all stored the result into variables

if on select return null all the equation will be null too so I tried many times to use the function
COALESCE with sum like that

SELECT SUM(COALESCE(Quantity,0))

OR

SELECT SUM(COALESCE(Quantity, cast (0 as float)))

but none worked I get error message (-902 )
(I lost the message text)

-or error-
Unsuccessful execution caused by system error that does not preclude successful execution of subsequent statements.
data type not supported for arithmetic.

I tried also to solve the problem with the expert stuff from team B but did not reach to a reasonable solution

the only way now is test NULL using many  IFs which make the proc very long and very ugly

so I hope to modify COALESCE to work with SUM() or make a new function work like ISNULL() in other databases

Thanks
Steps to Reproduce:
None
Workarounds
None
Attachment
None
Comments

Sriram Balasubramanian at 6/2/2008 2:06:05 PM -
In the following sample, you'll notice how to get a non-null integer value using coalesce for your SUM operation. Hope it helps.

>>>
create table t1 (n integer);
/*
insert into t1 values (1);
insert into t1 values (2);
insert into t1 values (3);
*/
insert into t1 values (NULL);
commit;
select sum(n) from t1;

                  SUM
=====================

               <null>

select sum(coalesce(n,0)) from t1;

                  SUM
=====================

                    0

commit;
<<<

isaac sam at 1/22/2009 12:07:59 PM -
is there a solution that you sum expression return 6 instead of 0 ??
this is the first place is why I report this problem, because only one null value will result in off result

some time admin of database may forget to apply not null or he cant because of some certain requirement so the programmer expect to see the sum of all records

In SQL server there is at least the function ISNULL which is very handy , I really believe that Interbase need to have alot of built in function instead of the user implement or use extrnal dll function for that

Server Response from: ETNACODE01

Copyright© 1994 - 2013 Embarcadero Technologies, Inc. All rights reserved.