how 2 catch divide by zero

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
LVL 1
stonemanAsked:
Who is Participating?
 
shabbirabConnect With a Mentor Commented:
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
 
Alex_nlCommented:
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
 
csabayCommented:
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
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

 
stonemanAuthor Commented:
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
 
Alex_nlCommented:
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
 
shabbirabCommented:
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
 
shabbirabCommented:
A little too fast on that ..
Give me some time.

0
 
shabbirabCommented:
A little too fast on that ..
Give me some time.

0
 
stonemanAuthor Commented:
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
 
knel1234Commented:
stoneman,

Are you still looking for an answer?

knel1234
0
 
stonemanAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.