Division by Zero and Select Case Statement 1

[dunkyn]

Any advice on how to avoid division by zero with a select case statement?

Here is what I have:


whileprintingrecords;

If {sp_Classified_Position_Report;1.VALVAL_CMB_AMT}>0 then

SELECT {sp_Classified_Position_Report;1.ASSET_CLASS_MNEM}

Case "FIXED INC": {sp_Classified_Position_Report;1.VALVAL_CMB_AMT}/Sum ({sp_Classified_Position_Report;1.VALVAL_CMB_AMT},{sp_Classified_Position_Report;1.ASSET_CLASS_MNEM})*100
Case "EQUITY": ({sp_Classified_Position_Report;1.VALVAL_CMB_AMT}/sum({sp_Classified_Position_Report;1.VALVAL_CMB_AMT},{@MySort})*100)
Case "CASH": ({sp_Classified_Position_Report;1.VALVAL_CMB_AMT}/sum({sp_Classified_Position_Report;1.VALVAL_CMB_AMT},{@MySort})*100)
Case "CURR EQUIV"{sp_Classified_Position_Report;1.VALVAL_CMB_AMT}/sum({sp_Classified_Position_Report;1.VALVAL_CMB_AMT},{@MySort})*100)
Case "REC/PAY"{sp_Classified_Position_Report;1.VALVAL_CMB_AMT}/sum({sp_Classified_Position_Report;1.VALVAL_CMB_AMT},{@MySort})*100)
Case "OTHER": ({sp_Classified_Position_Report;1.VALVAL_CMB_AMT}/sum({sp_Classified_Position_Report;1.VALVAL_CMB_AMT},{@MySort})*100)

Else 0;

I get the error message: "The remaining text does not appear to be part of the formula."

TIA

 

[synapsevampire]

you should be checking:

not(isnull(sum({sp_Classified_Position_Report;1.VALVAL_CMB_AMT}))

-k

 

[dunkyn]

Perfect! Thx!

 

[dunkyn]

I am unable to star this for your help? Is there a problem with the site?

 

[synapsevampire]

Not sure, generally you just select mark this post and it will pop up a windows to confirm it.

-k

 

[lbass]

I would have said the problem was with the final "else 0".

I don't think you need an "else" for the initial "if-then." But the select case statement should end with:

default : 0

...instead of "else 0"

I tested a select case with an initial if-then and ending in "else 0" and got the same error message you did. Replacing it with "default : 0" returned no errors.

-LB

 

[dunkyn]

Thank you lbass. That is helpful too.

Unfortunately, when I click on the "Mark this post as a helpful/expert post!" nothing happens.

I think there may be a hiccup. It worked for me before, but now nothing....