How can I handle a division by zero within this case statement

Question

Within this case statement I have a "divide by". How can I modify this to handle div by zero errors?

CASE WHEN INVOICE_V.INVC_TYPE = 0 then 
    (((INVC_ITEM_V.PRICE - INVC_ITEM_V.TAX_AMT)*INVC_ITEM_V.QTY) -  
     (INVC_ITEM_V.COST * INVC_ITEM_V.QTY)) 
        / ((INVC_ITEM_V.PRICE - INVC_ITEM_V.TAX_AMT)*INVC_ITEM_V.QTY)*100 
    else 0 END as EXT_M_PERCENT,

@500-InternalServerError - Um, what? Normally it throws some sort of [divide-by-zero error](https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=486a26402969f36ee4699b13c69eeae2), like just about every language out there. Unless you're thinking of some specific db? — Clockwork-Muse, Jan 22 '19 at 21:30

score 1 · Answer 1 · answered Jan 22 '19 at 21:27

1

If by "handle" you mean return 0, then you can do this:

CASE WHEN INVOICE_V.INVC_TYPE = 0 
      AND (((INVC_ITEM_V.PRICE - INVC_ITEM_V.TAX_AMT)*INVC_ITEM_V.QTY) <> 0 THEN

answered Jan 22 '19 at 21:27

Hogan

69,564
10
76
117

Yes by handle I mean return zero to prevent a division by zero error. So basically what I wish to say is "evaluate the "divide by" and return 0 if it's 0 else continue" – Fraser Kelly Jan 23 '19 at 08:22
@FraserKelly -- then you should accept my answer as solving your problem. – Hogan Jan 23 '19 at 15:10

How can I handle a division by zero within this case statement

1 Answers1