I've got a table that contains payment detail records and I've found that
something like the following workaround is necessary when adding up a column
contains the dollar amount. This query always returns a single record, but
when there are zero payment records found, the 'total' field returns a
zero-length string rather than returning 0. Of course, if I later attempt
to user that value in a CF arithmetic expression, CF chokes and gives an
'unable to convert' to numeric error. I've got a lot of these summing
queries and was wondering if there's a way within the SQL statement (MS
SQL7) to return 0 if there are no records found.
<!--- Get total amount this registrant has paid --->
<cfquery name="paid" datasource="#dsn#">
SELECT Sum(paymentamt) AS total
FROM payments
WHERE userid = #form.userid#
</cfquery>
<!--- Handle the case where the query found no payments --->
<cfif paid.total is "">
<cfset dummy = QuerySetCell(paid, "total", "0.00")>
</cfif>
Thanks,
Jim
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
