Solved

# how 2 catch divide by zero

Posted on 2000-03-30
Medium Priority
533 Views
Last Modified: 2012-06-21
I am trying to create a view; one of the columns is meant to be a calculated profit percentage for the month to date sales.

e.g.

MONTH_PROF_PERC =
(i.SALES_CURMONTH - i.SALES_COST_CURMONTH) / i.SALES_CURMONTH

[i is an alias for inventory table]
However, sometimes a product hasn't sold anything for that month, so the view barfs with a divide by zero error, and hence nothing shows up.

in a real language (I hate SQL) I would simply say something like:

MONTH_PROF_PERC =
if( i.SALES_CURMONTH > 0)
then
(i.SALES_CURMONTH - i.SALES_COST_CURMONTH) / i.SALES_CURMONTH
else
0

There must be a way to code this using SQL, perhaps as a subquery? NOTE: I can't add a where clause that prevents records where  i.SALES_CURMONTH <= 0 from showing up, that would drop certain products that have been selling great all year, but maybe not yet this month.

Thanks!

p.s. I'm using Sybase Adaptive Server Enterprise 11.9.2
0
Question by:stoneman
• 4
• 3
• 2
• +2
11 Comments

Expert Comment

ID: 2672193
Could you use a case statement here?

Something like:

MONTH_PROF_PERC = CASE i.SALES_CURMONTH WHEN 0 THEN 0 ELSE (i.SALES_CURMONTH - i.SALES_COST_CURMONTH) / i.SALES_CURMONTH END

There may be a better syntax for this but I don't have the Online Books installed here...

HTH,
Alex
0

Expert Comment

ID: 2672851
Hi,

there is also an if-syntax to use in Sybase:

if i.sales_curmonth = 0
month_prof_perc = 0
else
month_prof_perc = ... &c

Charles
0

LVL 1

Author Comment

ID: 2676994
Sorry, neither of these suggestions helped. The whole context of what I am trying to do is here:

CREATE VIEW INV_ANALYSIS
AS
SELECT  i.VENDOR_6,
i.SKU,
MONTH_PROFIT = (i.SALES_CURMONTH - i.SALES_COST_CURMONTH),
MONTH_PROF_PERC = (i.SALES_CURMONTH - i.SALES_COST_CURMONTH) / i.SALES_CURMONTH
FROM INVENTORY i
WHERE i.SALES_CURYEAR > 0

When I put either of the above into suggestions into the line for MONTH_PROF_PERC I get a syntax error near keyword if.

0

Expert Comment

ID: 2679123
Hi stoneman,
I'm pretty sure this will work:

CREATE VIEW INV_ANALYSIS
AS
SELECT  i.VENDOR_6,
i.SKU,
MONTH_PROFIT = (i.SALES_CURMONTH - i.SALES_COST_CURMONTH),
MONTH_PROF_PERC = CASE i.SALES_CURMONTH
WHEN 0 THEN 0 ELSE
(i.SALES_CURMONTH - i.SALES_COST_CURMONTH) / i.SALES_CURMONTH
END
FROM INVENTORY i
WHERE i.SALES_CURYEAR > 0
0

Expert Comment

ID: 2770635
Just add a condition as under and this should work.

MONTH_PROF_PERC = (i.SALES_CURMONTH - i.SALES_COST_CURMONTH) / ISNULL(i.SALES_CURMONTH , 1)
AND MONTH_PROF_PERC != (i.SALES_CURMONTH - i.SALES_COST_CURMONTH)
0

Expert Comment

ID: 2770641
A little too fast on that ..
Give me some time.

0

Expert Comment

ID: 2770677
A little too fast on that ..
Give me some time.

0

LVL 1

Author Comment

ID: 2829408
None of these suggestions worked, sybase wouldn't
t let me save the create view. I've resorted to performing a check in my Java code instead. Thanks for the tries though!

0

LVL 3

Expert Comment

ID: 2872278
stoneman,

Are you still looking for an answer?

knel1234
0

LVL 1

Author Comment

ID: 2913088
Sure, but I'm convinced at this point it can't be done. My solution was to add a SALES_CURMONTH > 0 to the where clause. This stopped records that could produce the divide by zero, but my client wasn't too happy. It means items that are still top sellers, but for some reason haven't sold anything this month yet, would not show up in the report. If you can figure it out I'd double the points.

-stoneman
0

Accepted Solution

shabbirab earned 80 total points
ID: 2915433
If you do a Union of the Same Query with the condition SALES_CURMONTH <= 0, it should work. I mean ...

SELECT ....
MONTH_PROF_PERC =  (i.SALES_CURMONTH - i.SALES_COST_CURMONTH) / i.SALES_CURMONTH
FROM ...
WHERE ...
AND SALES_CURMONTH > 0
UNION
SELECT ....
MONTH_PROF_PERC =  0
FROM ...
WHERE ...
AND SALES_CURMONTH <= 0
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If anyone asked you to network diagram of the internet, it was drawn in the form of a fluffy cloud which further became known as cloud computing. Popularly cloud computing is defined as workloads that run over the internet in a commercial providerāsā¦
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as welā¦
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
Please read the paragraph below before following the instructions in the video ā there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, ā¦
###### Suggested Courses
Course of the Month17 days, 12 hours left to enroll

#### 829 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.