[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