Sum, Coalesce and Null fields

Hi

With Version 2.03 of Nexus I have found I needed to add Coalesce() command
where before I thought it wasn't needed.

As before the upgrade,

select Sum(Coalesce(Amt_Invoiced, 0)) as Invoiced, Sum(Coalesce(Amt_Paid,
0) + coalesce(Amt_Allowed, 0)) as Paid from P_Jrntrn
where VENDOR_ID = :VENDOR_ID

result could be Invoiced = 0.00 and Paid = 0.00
If Nulls were encountered, would be converted to 0.00 before the SUM()
command was actioned.

It now needs

select coalesce(Sum(Coalesce(Amt_Invoiced, 0)), 0) as Invoiced,
coalesce(Sum(Coalesce(Amt_Paid, 0) + coalesce(Amt_Allowed, 0)), 0) as Paid
from P_Jrntrn
where VENDOR_ID = :VENDOR_ID

Am I correct in this logic?

Any help appreciated.
Brenton

Brenton Wildman wrote:

> Hi
>
> With Version 2.03 of Nexus I have found I needed to add Coalesce() command
> where before I thought it wasn't needed.
>
> As before the upgrade,

Was answered in nexusdb.public.support.sql

--

Hannes Danzl [NexusDB Developer]
Newsgroup archive at http://www.tamaracka.com/search.htm