| Author |
Topic |
|
sillylady
Starting Member
13 Posts |
 Posted - 2007-01-08 : 08:05:10
|
| Hi All,I have a query which does lots of calculation. I have used CASE statement too where I knew the value of the field can be zero. I have more than 50 fields and I think it is not possible to check each field for zero. Can any one suggest me any idea to over come this problem?Thanks |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-01-08 : 08:33:49
|
| Do you have a query? Some sample data? Your expected output based on the sample data?Peter LarssonHelsingborg, Sweden |
 |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2007-01-08 : 08:41:32
|
| I doubt you are dividing things by 50 different values at once:select @a / @b / @c / @d / @e ... etc ..if you are writing an expression like that, I'd love to hear why! You only need to check things for zero that ultimately end up as a denominator. If for some reason you have an expression written as above, simplify it:select @a / (@b * @c * @d * @e .. etc ... )will return the same value. And in that case, you only need to ensure that the entire denominator (@b * @c * @d ... etc) is not zero, which is just a single check. Surely that is not too many cases to write, I hope.Basic algebra should really be a requirement for programmers .... It's kind of useful! And, please, remember that @x divided by 0 is NOT zero, which people love to assume ... in fact it is not even close ....- Jeff |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2007-01-08 : 09:18:44
|
quote:
Originally posted by sillylady
Hi All,I have a query which does lots of calculation. I have used CASE statement too where I knew the value of the field can be zero. I have more than 50 fields and I think it is not possible to check each field for zero. Can any one suggest me any idea to over come this problem?Thanks
Why is it not possible to check them all for zero? There is no limitation that I know of on the number of CASE statements.CODO ERGO SUM |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-01-08 : 09:26:08
|
| I think OP is doing 50 separate divisions...And Copy & Paste is an unknown work habit.Peter LarssonHelsingborg, Sweden |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2007-01-08 : 09:48:44
|
quote:
Originally posted by Peso
I think OP is doing 50 separate divisions...And Copy & Paste is an unknown work habit.Peter LarssonHelsingborg, Sweden
Probably ... most likely, we have a classic unnormalized database here .....sillylady -- why do you have more than 50 fields in your table? Is your data stored with 1 field per month or something that like? If so, your design is contributing to making your calculations much more complicated than they need to be.- Jeff |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2007-01-08 : 09:50:47
|
| Sometimes you just have to do boring, tedious work to get the job done. That's why they pay you for it.CODO ERGO SUM |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-01-08 : 12:35:55
|
| "sillylady"Great nickname! |
|
|
|
sillylady
Starting Member
13 Posts |
Posted - 2007-01-09 : 02:06:25
|
| To Michael, Jsmith.......I have a table which has Balancesheet, Incomestatements items as fields. The most you can do to suggest me to break up the balancesheet and incomestatement in 2 different tables, even if i do that then i will have more complication during ratio generation. To Michael on checking each fields for zero. I think that i have to do though i hate repeating the same thing again n again. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-01-09 : 04:12:19
|
| "again n again"Why is it again and again?That suggests that your code is not optimal - posting an example of what you are doing will help folk here make suggestions.Kristen |
|
|
|
sillylady
Starting Member
13 Posts |
Posted - 2007-01-11 : 04:55:41
|
| Thanks for your valuable advice, I managed to solve it. |
|
|
|
|
Division by zero error