Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Fixing Divide by Zero Error Expand / Collapse
Author
Message
Posted Monday, April 15, 2013 10:52 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, September 13, 2013 8:55 PM
Points: 18, Visits: 68
Hello,

I have in select statement select t1.A/t1.B but get divide by zero error

when I used with isnull isnull(t1.A/t1.B,'-')


I get error Error converting data type varchar to float.

how to fix?

Thanks
Post #1442575
Posted Monday, April 15, 2013 11:06 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 4:59 PM
Points: 23,000, Visits: 31,482
VegasL (4/15/2013)
Hello,

I have in select statement select t1.A/t1.B but get divide by zero error

when I used with isnull isnull(t1.A/t1.B,'-')


I get error Error converting data type varchar to float.

how to fix?

Thanks


select t1.A / nullif(t1.B,0)



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1442576
Posted Monday, April 15, 2013 11:40 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, July 10, 2014 11:20 AM
Points: 1,888, Visits: 2,323
VegasL (4/15/2013)
Hello,

I have in select statement select t1.A/t1.B but get divide by zero error

when I used with isnull isnull(t1.A/t1.B,'-')


I get error Error converting data type varchar to float.

how to fix?

Thanks

You can handle this using CASE also:
SELECT CASE when t1.b =0 THEN 0 ELSE t1.A/t1.B



_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #1442584
Posted Monday, April 15, 2013 11:51 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 12:40 AM
Points: 7,001, Visits: 8,439
why are you storing numeric values in a varchar column. As experienced, this will get you in troubles.
( in most cases because of formatting issues or just because someone put non-numeric data in it)

There must be a zero ( 0 ) in your t1.B column.
add a case statement to check it so it doesn't produce the error.

select t1.a / case t1.b when 0 then NULL /* to the result will be null */
else t1.B
end as TheResult



Johan


Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't get you anywhere

- How to post Performance Problems
- How to post data/code to get the best help


- How to prevent a sore throat after hours of presenting ppt ?


"press F1 for solution", "press shift+F1 for urgent solution"


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me
Post #1442593
Posted Sunday, April 21, 2013 7:55 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, September 13, 2013 8:55 PM
Points: 18, Visits: 68
Thanks Lynn, you'res was the easiest solution for me. I'll have to look into the others more. Thanks All!
Post #1444795
Posted Monday, April 22, 2013 10:09 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, July 14, 2014 9:18 AM
Points: 23, Visits: 225
I would agree with Lynn's post. You can not divide by zero, but if you divide by NULL, you'll get NULL.

DECLARE @i FLOAT = 10, @x FLOAT = 0
SELECT @i / NULLIF(@x,0)

Post #1445052
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse