Ray, Yes, Case statements can save a boatload of time in a query. Also "Isnull( )" if an ratecount is null.... as in:
select ISNULL(tblUDFs.Ratecount,0) It would return a 0 if the field was null. It's amazing when I look at my early CF how much time and effort I now save by adding some simple techniques to a query. -mk. -----Original Message----- From: Raymond Camden [mailto:[EMAIL PROTECTED]] Sent: Friday, August 09, 2002 7:58 AM To: CF-Talk Subject: cflib.org announcement Just a quick announcement in regards to some changes at cflib.org: *Added new registration system. Right now it just gives access to the udf newsletter subscription and.. *New Rating system. Should be pretty clear what it does. *Tee shirts for sale. Help support m.. um... the site. Yes, help support the site! Any comments are appreciated. Any bugs - please let me know. SQL Tip: So, I was working on adding "Rating" to the results set of the search page. I needed a way to get the average rating in the query. However, if I did: select ...., total/count as avg It would give me an error whenever a UDF had never been rated. A friend, Joel Mueller, told me about the CASE statement. Very nice and simple way to handle the issue: SELECT (deletia), ratingAvg = CASE tblUDFs.rateCount WHEN 0 THEN 0 ELSE cast(tblUDFs.rateTotal as float) / tblUDFs.rateCount END FROM etc... ======================================================================= Raymond Camden, ColdFusion Jedi Master for Macromedia Email : [EMAIL PROTECTED] Yahoo IM : cfjedimaster "My ally is the Force, and a powerful ally it is." - Yoda ______________________________________________________________________ Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.cfm FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists