ORDER BY

[Guest]

Hi i have this
(CASE WHEN BB.TotalOuts = '0, 0,' THEN BB.Runs / BB.TotalOuts ELSE BB.Runs / 1 END) AS "AVER"
from a QofQ

this works fine but i need to order by AVER which dosnt work, i have 3 results

104
0
5

i think because of the "0" this is why it wont order by, if i am right how can i get over this problem?

full code is

[Dan_Bracuk]

Try ordering by your case expression.

[Guest]

yes thats what i thought i was doing?

is there another way i can do it?

[Dan_Bracuk]

Upon further review, are you sure it works at all? First of all, you seem to attempting to divide by a string instead of a number. Secondly, if you ignore the first part, you appear to be attempting to divide by 0.

[Guest]

1

[Guest]

yes but in the else clause i have divide by 1 if 0 appears, maybe i have this code wrong? what would the best way be?

[Dan_Bracuk]

You said the code worked fine without the order by clause. Did you really successfully run it exactly the way you posted it?

[Guest]

ok yes it works if the BB.TotalOuts is not '0'

yes your right it wont divide by 0, how can i reslove this problem

thanks

[Dan_Bracuk]

Start with the basics. Is TotalOuts a number or character? If it's a number, why do you have it quoted? If it's a character, why are you trying to do math with it?

As far as the case statement goes, write down what you want to do in plain and simple english. Then convert the english to sql. If you can't convert your plain and simple english statement to sql, post it here.

[Guest]

yes totalouts is a number

i need the sql for
bb.Runs diveded by bb.totalouts (both may be zero values)

not sure what to do if 0 devided by 0?

[Dan_Bracuk]

quote:

---

Originally posted by: richy2424
yes totalouts is a number
i need the sql for
bb.Runs diveded by bb.totalouts (both may be zero values)
not sure what to do if 0 devided by 0?

---

Having 0 runs is not a problem, you answer will be 0. Having 0 totalouts has to be dealt with. But before you can code it, you have to know what you want to do. What do you want your answer to be when there are 0 totalouts?

[paross1]

Wouldn't your CASE statement look more like this?

(CASE WHEN BB.TotalOuts = 0 THEN 0 ELSE BB.Runs / BB.TotalOuts) AS "AVER"
--or--
(CASE WHEN BB.TotalOuts <> 0 THEN BB.Runs / BB.TotalOuts ELSE 0) AS "AVER"

Phil