But, remember that if you use either of these, you've tied yourself to specific database functionality. That may be perfectly OK for most applications, but if you want to be able to "unplug" one database and "plug in" another (like using an application written for SQL Server for another client who uses MySQL) you should avoid this type of thing and stick with CF tools.
On 8/9/02 6:29 AM, "Mark A. Kruger - CFG" <[EMAIL PROTECTED]> wrote: > 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. > >... > > 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 > ______________________________________________________________________ Get the mailserver that powers this list at http://www.coolfusion.com FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/[email protected]/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
