I havn't tested this, but you should be able to do either of the following:
SELECT (Sum(paymentamt) + 0) AS total
or
SELECT CAST(Sum(paymentamt) AS int) AS total
Chris
----------------------------------------------
Original Message
From: "Jim McAtee"<[EMAIL PROTECTED]>
Subject: MS SQL Shortcut?
Date: Wed, 22 Nov 2000 15:14:34 -0700
>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
>
_____________________________________________
Free email with personality! Over 200 domains!
http://www.MyOwnEmail.com
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
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
