The COUNT( ) function will never return NULL. So "isNull(count(*))" will never trigger. Count(*) will always return an int - so using isNULL and "count" together is not possible - plus you are mixing data types if you are trying to return a 0.00001 to a function call designed to return an int.
-----Original Message----- From: morchella [mailto:[EMAIL PROTECTED] Sent: Thursday, January 17, 2008 9:03 AM To: CF-Talk Subject: SQL: ISNULL, NULLIF problem i have been trying to figure out how to check for null like whats done in this url: http://www.bennadel.com/blog/984-Using-NULLIF-To-Prevent-Divide-By-Zero-Erro rs-In-SQL.htm SELECT ( ISNULL( (45 / NULLIF( 0, 0 )), 0 ) ) AS value how would i re write the code below to check for null i can then get rid of the + 0.00000001 (SELECT COUNT(*) FROM Emp WHERE emp_assID = rEmp.empID AND addSale = 1) * 100 / (SELECT COUNT(*) + 0.00000001 FROM Emp WHERE emp_assID = rEmp.empID AND ((Emp.Qal = 4) OR (Emp.addSale = 1))) AS perEmp i tried: (SELECT ISNULL( COUNT(*) FROM Emp WHERE emp_assID = rEmp.empID AND addSale = 1) / NULLIF( (SELECT COUNT(*) FROM Emp WHERE emp_assID = rEmp.empID AND ((Emp.Qal = 4) OR (Emp.addSale = 1))), 0 )), 0 ) ) AS perEmp but i get Incorrect syntax near the keyword 'FROM'. any help would be great... thanks ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:296729 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
