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/
