How to overcome Divide by Zero Error using ADO/SQL 7 
Author Message
 How to overcome Divide by Zero Error using ADO/SQL 7

I am attempting to return a recordset from a SQL box using a SQL string (not
a Stored Proc). One of the fields is a calculated field which for sake of
argument is Field1/Field2.
Obviously both are numeric, but Field2 is occasionally Zero, resulting in an
error from the SQL box, and failure to return any info at all.
Is there a way to get SQL to ignore the error (returning a value of zero for
the field).

Looking at some help files, am I right in saying that using
SET ANSI_WARNINGS ON (or OFF) could solve the problem.
I have tried this, and still get the error.

Regards, Darren



Tue, 11 Nov 2003 06:29:13 GMT  
 How to overcome Divide by Zero Error using ADO/SQL 7
SELECT result = (CASE field2 WHEN 0 then 0 ELSE field1/field2 END)
FROM myTable


Quote:
> I am attempting to return a recordset from a SQL box using a SQL string
(not
> a Stored Proc). One of the fields is a calculated field which for sake of
> argument is Field1/Field2.
> Obviously both are numeric, but Field2 is occasionally Zero, resulting in
an
> error from the SQL box, and failure to return any info at all.
> Is there a way to get SQL to ignore the error (returning a value of zero
for
> the field).

> Looking at some help files, am I right in saying that using
> SET ANSI_WARNINGS ON (or OFF) could solve the problem.
> I have tried this, and still get the error.

> Regards, Darren



Tue, 11 Nov 2003 06:51:42 GMT  
 How to overcome Divide by Zero Error using ADO/SQL 7
Thanks for replying, but unfortunately I cannot use this since the
calculated total is a user-defined calculation.
The situation is that I have a huge table of data, and the users can not
only select which data to view on the reports, but also make up their own
calculation.
For example, I have a gross & cost field, so they may wish to see a profit
field which will be (gross-cost)/gross.
There is also a package field, so they may wish to see average price per
pack (=gross/pack).
I could 'try' to forsee all situations, but inevitably there will be some
unforseen requests.
I therefore need a way of avoiding the error and still returning the
recordset.

Regards, Darren


Quote:
> SELECT result = (CASE field2 WHEN 0 then 0 ELSE field1/field2 END)
> FROM myTable



> > I am attempting to return a recordset from a SQL box using a SQL string
> (not
> > a Stored Proc). One of the fields is a calculated field which for sake
of
> > argument is Field1/Field2.
> > Obviously both are numeric, but Field2 is occasionally Zero, resulting
in
> an
> > error from the SQL box, and failure to return any info at all.
> > Is there a way to get SQL to ignore the error (returning a value of zero
> for
> > the field).

> > Looking at some help files, am I right in saying that using
> > SET ANSI_WARNINGS ON (or OFF) could solve the problem.
> > I have tried this, and still get the error.

> > Regards, Darren



Tue, 11 Nov 2003 16:15:03 GMT  
 How to overcome Divide by Zero Error using ADO/SQL 7
Then just set the ANSI_WARNINGS OFF and you will get a null.


Quote:
> Thanks for replying, but unfortunately I cannot use this since the
> calculated total is a user-defined calculation.
> The situation is that I have a huge table of data, and the users can not
> only select which data to view on the reports, but also make up their own
> calculation.
> For example, I have a gross & cost field, so they may wish to see a profit
> field which will be (gross-cost)/gross.
> There is also a package field, so they may wish to see average price per
> pack (=gross/pack).
> I could 'try' to forsee all situations, but inevitably there will be some
> unforseen requests.
> I therefore need a way of avoiding the error and still returning the
> recordset.

> Regards, Darren



> > SELECT result = (CASE field2 WHEN 0 then 0 ELSE field1/field2 END)
> > FROM myTable



> > > I am attempting to return a recordset from a SQL box using a SQL
string
> > (not
> > > a Stored Proc). One of the fields is a calculated field which for sake
> of
> > > argument is Field1/Field2.
> > > Obviously both are numeric, but Field2 is occasionally Zero, resulting
> in
> > an
> > > error from the SQL box, and failure to return any info at all.
> > > Is there a way to get SQL to ignore the error (returning a value of
zero
> > for
> > > the field).

> > > Looking at some help files, am I right in saying that using
> > > SET ANSI_WARNINGS ON (or OFF) could solve the problem.
> > > I have tried this, and still get the error.

> > > Regards, Darren



Tue, 11 Nov 2003 22:19:19 GMT  
 
 [ 4 post ] 

 Relevant Pages 

1. catching divide by zero errors in C Dll used by VB app

2. Dividing Zero by Zero

3. SQL Divide by Zero problem :(

4. Divide by zero - no error

5. Divide by zero error upon formating a report!

6. VB4-Enterprise: intermittent divide by zero error

7. Divide by zero when using Progress Bar control

8. Printer.Print Yeilds Divide By Zero Error

9. Printer.Print Yeilds Divide By Zero Error

10. bizar divide by zero problem

11. Divide By Zero Exception

12. Crescent QP Pro 4.0 divide by zero in Pause3

 

 
Powered by phpBB® Forum Software