[thelist] Avoiding Divide by Zero within SQL...

Rory.Plaire at wahchang.com Rory.Plaire at wahchang.com
Fri Nov 30 13:10:15 CST 2001


Hi Anthony,

+| 
+| The fields that are "Null" turn the whole answer to "Null". 
+| If I convert 
+| the Nulls to Zeros during the creation of the tempEnrollment 
+| table, I get a 
+| divide by zero error since some times columns are all Null.

You want what, exactly?

+| So I need to get a result if the denominator is greater than zero; 
+| otherwise I just want 0%.

Oh, I see..

Try this:

SELECT

CASE	tempEnrollment.Full_Time_Men_Enrolled +
	tempEnrollment.Part_Time_Men_Enrolled +
	tempEnrollment.Full_Time_Women_Enrolled +
	tempEnrollment.Part_Time_Women_Enrolled
WHEN		0	THEN		0

ELSE		

Cast
(	Cast
	(	(
			(tempEnrollment.Full_Time_Women_Enrolled +
				tempEnrollment.Part_Time_Women_Enrolled) 
			/
			(tempEnrollment.Full_Time_Men_Enrolled +
				tempEnrollment.Part_Time_Men_Enrolled +
				tempEnrollment.Full_Time_Women_Enrolled +
				tempEnrollment.Part_Time_Women_Enrolled
				
			) * 100
		) As Int
	) As NvarChar
) + "%"

AS 	pctEnrollment

FROM tempEnrollment

<rory disposition="now you can see why I am a little hesitant to go to
Oracle... inertia." alt="80"/>




More information about the thelist mailing list