Solved

how 2 catch divide by zero

Posted on 2000-03-30
11
505 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
Comment
Question by:stoneman
  • 4
  • 3
  • 2
  • +2
11 Comments
 

Expert Comment

by:Alex_nl
Comment Utility
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

by:csabay
Comment Utility
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

by:stoneman
Comment Utility
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

by:Alex_nl
Comment Utility
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

by:shabbirab
Comment Utility
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Expert Comment

by:shabbirab
Comment Utility
A little too fast on that ..
Give me some time.

0
 

Expert Comment

by:shabbirab
Comment Utility
A little too fast on that ..
Give me some time.

0
 
LVL 1

Author Comment

by:stoneman
Comment Utility
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

by:knel1234
Comment Utility
stoneman,

Are you still looking for an answer?

knel1234
0
 
LVL 1

Author Comment

by:stoneman
Comment Utility
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

by:
shabbirab earned 40 total points
Comment Utility
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
improving database skills 2 567
installing Sybase IQ express edition 15.4 16 848
Clean up a mailbox 5 146
surrogate key in database world 6 72
Never store passwords in plain text or just their hash: it seems a no-brainier, but there are still plenty of people doing that. I present the why and how on this subject, offering my own real life solution that you can implement right away, bringin…
Scam emails are a huge burden for many businesses. Spotting one is not always easy. Follow our tips to identify if an email you receive is a scam.
This video discusses moving either the default database or any database to a new volume.
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…

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

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

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now