You could use a CASE statement, but it might just be easier to get the values separately and do the check and division in CF
On Jan 17, 2008 10:25 AM, morchella <[EMAIL PROTECTED]> wrote: > actually the + 0.0000001 was just my old was of avoiding / by zero error > thats why i want to test to see if result is zero & then not / into it. > > On Jan 17, 2008 10:14 AM, Mark Kruger <[EMAIL PROTECTED]> wrote: > > 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:296734 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
