#1  
Old 18th January 2006, 10:35 PM
Brenton Wildman
 
Posts: n/a
Default 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




  #2  
Old 19th January 2006, 07:51 AM
Hannes Danzl[NDD]
 
Posts: n/a
Default Re: Sum, Coalesce and Null fields

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


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Seconds are not being returned in DateTime fields Michael Duncan nexusdb.public.support.adoprovider 1 12th August 2004 06:18 AM
BLOB fields Michael Duncan nexusdb.public.support.adoprovider 3 20th July 2004 06:00 AM


All times are GMT +11. The time now is 03:23 PM.


Powered by vBulletin® Version 3.6.8
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.