|
try SUM(coalesce(field,0))
>>> "Tim Starling" <[EMAIL PROTECTED]> 7/21/2006 6:12 pm >>> Thanks everyone! I will give those a try. Tim -----Original Message----- From: [EMAIL PROTECTED] <[EMAIL PROTECTED]> To: Dallas/Fort Worth ColdFusion User Group Mailing List <[email protected]> Sent: Fri Jul 21 17:17:53 2006 Subject: Re: [DFW CFUG] coalesce Assuming this is a scalar result (only at most one row, one column in the return query) you can do this: -- sql server select coalesce( SELECT sum(mytable.Amount) FROM etc,... , 0) AS myAmount ; -- oracle select coalesce( SELECT sum(mytable.Amount) FROM etc,... , 0) myAmount from dual ; basically ensuring that a NULL (resulting from zero rows in your base query) is represented as a zero if you have multiple columns, you can do this: select coalesce(a.c1, 0) c1 , coalesce(a.c2, 0) c2 from ( select sum(c1) as c1 , sum(c2) as c2 from tbl ) as a - dave Tim Starling wrote: > I was under the impression that > > <cfquery name="qMyquery" datasource="#dsn#"> > > SELECT COALESCE(sum(mytable.Amount),0) AS myAmount > > FROM etc,... > > would return a numeric value of 0 if the query returned no records and > therefore had nothing to sum. So, that when I try something like: > > <cfoutput>#DollarFormat(qMyquery.myAmount+qMyQuery2.ccAmount)#</cfoutput> > > And it tries to add the two values together it wouldn't give me the > error of: > > *The value "" cannot be converted to a number* > > Is that not the case or am I doing something wrong in the way I have > this written? I basically have to sum the amount of some transactions > and add them together with other sums and subtract out some other fees. > In one of the tables most users will not have transactions and therefore > the query will be empty and therefore there will not have anything to add. > > Can anyone tell me what am I overlooking? > > Tim Starling > **Global Aid Network*** > *//A ministry of Campus Crusade for Christ International/// > /972-234-0800, extension 1335 > www.gainusa.org <http://www.gainusa.org> _______________________________________________ Reply to DFWCFUG: [email protected] Subscribe/Unsubscribe: http://lists1.safesecureweb.com/mailman/listinfo/list List Archives: http://www.mail-archive.com/[email protected]/ http://www.mail-archive.com/[email protected]/ DFWCFUG Sponsors: www.HostMySite.com www.teksystems.com/ ##################################################################################### This e-mail message has been scanned for Viruses and Content and cleared by MailMarshal ##################################################################################### _______________________________________________ Reply to DFWCFUG: [email protected] Subscribe/Unsubscribe: http://lists1.safesecureweb.com/mailman/listinfo/list List Archives: http://www.mail-archive.com/[email protected]/ http://www.mail-archive.com/[email protected]/ DFWCFUG Sponsors: www.HostMySite.com www.teksystems.com/ ** Confidentiality Notice: This e-mail and any files transmitted with it are confidential to the extent permitted by law and intended solely for the use of the individual or entity to whom they are addressed. If you have received this e-mail in error please notify the originator of the message and destroy all copies. ** |
_______________________________________________ Reply to DFWCFUG: [email protected] Subscribe/Unsubscribe: http://lists1.safesecureweb.com/mailman/listinfo/list List Archives: http://www.mail-archive.com/list%40list.dfwcfug.org/ http://www.mail-archive.com/list%40dfwcfug.org/ DFWCFUG Sponsors: www.HostMySite.com www.teksystems.com/
