11 Replies Latest reply on Jun 4, 2006 9:45 AM by paross1

# ORDER BY

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
• ###### 1. Re: ORDER BY
Try ordering by your case expression.
• ###### 2. Re: ORDER BY
yes thats what i thought i was doing?

is there another way i can do it?
• ###### 3. Re: ORDER BY
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.
• ###### 4. ORDER BY
1
• ###### 5. Re: ORDER BY
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?
• ###### 6. Re: ORDER BY
You said the code worked fine without the order by clause. Did you really successfully run it exactly the way you posted it?
• ###### 7. Re: ORDER BY
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
• ###### 8. Re: ORDER BY
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.
• ###### 9. Re: ORDER BY
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?
• ###### 10. Re: ORDER BY
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?
• ###### 11. Re: ORDER BY
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