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/list%40list.dfwcfug.org/ http://www.mail-archive.com/list%40dfwcfug.org/ 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/list%40list.dfwcfug.org/ http://www.mail-archive.com/list%40dfwcfug.org/ DFWCFUG Sponsors: www.HostMySite.com www.teksystems.com/
