Re: ISNULL, NULLIF problem
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.001 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.1 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.0001 > > > > (SELECT COUNT(*) > > FROM Emp > > WHERE emp_assID = rEmp.empID AND addSale = 1) * 100 / (SELECT COUNT(*) + > > 0.0001 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
Re: ISNULL, NULLIF problem
> > actually the + 0.001 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. I think CASE is what you are after: CASE WHEN IsNull(.,0) = 0 THEN don't do divide ELSE do divide END -- Blog it up: http://fusion.dominicwatson.co.uk ~| 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:296735 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: ISNULL, NULLIF problem
actually the + 0.001 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.1 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.0001 > > (SELECT COUNT(*) > FROM Emp > WHERE emp_assID = rEmp.empID AND addSale = 1) * 100 / (SELECT COUNT(*) + > 0.0001 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:296731 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: ISNULL, NULLIF problem
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.1 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.0001 (SELECT COUNT(*) FROM Emp WHERE emp_assID = rEmp.empID AND addSale = 1) * 100 / (SELECT COUNT(*) + 0.0001 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
