Scott on Writing

Feedback

# re: Computed Columns and Divide by Zero Errors 10/19/2004 10:55 AM Jay Nathan

Here's something else that'll work, but not sure if there are any advantages:

COALESCE(SomeColumnA/(SomeColumnB, 0.0), SomeColumnA/SomeColumnB)

The immediate disadvantage is that it's more verbose.

# re: Computed Columns and Divide by Zero Errors 10/19/2004 10:56 AM Jay Nathan

Doh!! That won't work afterall.

# re: Computed Columns and Divide by Zero Errors 10/19/2004 11:03 AM David Neal

If you are using stored procedures, then you can do the following:

SET ANSI_WARNINGS OFF
SET ARITHABORT OFF

SELECT Result1 = ColumnA/ColumnB,
Result2 = COALESCE(ColumnA/ColumnB, 0)

Result1 will be NULL and Result2 will be 0 if the value in ColumnB is 0.

# re: Computed Columns and Divide by Zero Errors 10/19/2004 11:10 AM Scott Mitchell

David, won't the Result1 = ColumnA/ColumnB result in a Divide by zero error when ColumnB = 0? I don't believe illegal division results in a NULL, but rather raises an error. (At least on my database box, maybe it's a SQL server setting somewhere...)

# re: Computed Columns and Divide by Zero Errors 10/19/2004 11:16 AM Mike Singer

> Problem: With SQL server you have a computed column

Perhaps the problem could be expressed wider, kind of: there are tables on SQL server and you should show to a user some corresponding grids with the computed columns? Then you could insert the column not only on Data Storage Level, but also on higher levels, right up to the level of client-side scripts.

# re: Computed Columns and Divide by Zero Errors 10/19/2004 11:32 AM David Neal

Scott, you won't get a divide by zero error if you have both ANSI_WARNINGS and ARITHABORT set to OFF. Again, this is only convenient when you're doing this within a stored procedure or batch statement.

BTW, ISNULL() and COALESCE() do the same thing. I'm just used to writing COALESCE.

# re: Computed Columns and Divide by Zero Errors 10/19/2004 11:34 AM David Neal

Here's an example you can run in Query Analyzer.

SET ANSI_WARNINGS OFF
SET ARITHABORT OFF

DECLARE @tblTable TABLE ( c1 int, c2 int )

INSERT INTO @tblTable
SELECT 235, 0
UNION SELECT 244, 0
UNION SELECT 50, 10

SELECT Result1 = c1/c2, Result2 = ISNULL(c1/c2, 0)
FROM @tblTable

# re: Computed Columns and Divide by Zero Errors 10/19/2004 12:50 PM graz

I'm a little confused. Why can't you use a CASE statement in a computer column. Am I missing something simple here.

use tempdb
GO
if exists (select *
from dbo.sysobjects
where id = object_id(N'[dbo].[T1]')
and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[T1]
GO

CREATE TABLE [dbo].[T1] (
[Col1] [decimal](10, 5) NULL ,
[Col2] [decimal](10, 5) NULL ,
[CompColumn] AS (CASE
WHEN Col2 = 0 THEN 0
WHEN Col2 IS NULL THEN 0
ELSE Col1 / Col2
END)
) ON [PRIMARY]
GO

INSERT T1 (Col1, Col2)
VALUES (1, 2)

INSERT T1 (Col1, Col2)
VALUES (10, 5)

INSERT T1 (Col1, Col2)
VALUES (10, 0)


INSERT T1 (Col1, Col2)
VALUES (10, NULL)

GO
SELECT *
FROM T1

# re: Computed Columns and Divide by Zero Errors 10/19/2004 1:04 PM Scott Mitchell

Didn't realize you could have CASE statements in a computed column, that sure is helpful to know. (I had needed this in an earlier project, tried IFs in the computed column formula region, but those aren't allowed obviously, so ended up using a UDF... will have to "fix" that.) Thanks for the info! :-)

# re: Computed Columns and Divide by Zero Errors 10/20/2004 2:44 PM Stuart Radcliffe

If you have more complex requirements, you can also use a UDF in a computed column

# re: Computed Columns and Divide by Zero Errors 10/26/2004 8:14 AM Scott Mitchell

I've whipped up an article extolling the benefits of CASE, one of the benefits including graz's comment, about using CASE statements in computed columns:

The Power of SQL CASE Statements
http://www.4guysfromrolla.com/webtech/102704-1.shtml

# re: Computed Columns and Divide by Zero Errors 12/2/2004 11:19 AM Jim

I've always used something like this...

SELECT
CASE WHEN ColumnB = 0 THEN 0 ELSE ColumnA/ColumnB END as 'Value'


Good article, Scott.

# re: Computed Columns and Divide by Zero Errors 12/6/2004 9:39 AM jon s

Dont forget, you cant use CASE in a view.

# re: Computed Columns and Divide by Zero Errors 2/7/2005 3:00 PM Johnny

What if I need do SUM(ColumnA/ColumnB)?

# re: Computed Columns and Divide by Zero Errors 2/7/2005 3:16 PM Johnny

Nevermind, the following seems to work:

ISNULL(SUM(CASE WHEN ColumnB = 0 Then 0 Else ColumnA/ColumnB),0)

Any logical issues with the above statement?

# re: Computed Columns and Divide by Zero Errors 3/11/2005 7:58 AM Don

Do not use

SET ANSI_WARNINGS OFF
SET ARITHABORT OFF

in a stored procedure that you expect to call frequently or under load. This will cause the sproc to recompile each time it is executed, crippling your server.

# re: Computed Columns and Divide by Zero Errors 8/26/2005 6:24 PM Billy

Thanks very much, this helped me heaps!

# re: Computed Columns and Divide by Zero Errors 8/31/2005 9:56 PM Khadar khan

Thak You very much Mr.Scott and Mr.Jim
I got nice solution from both of u.

# re: Computed Columns and Divide by Zero Errors 9/25/2005 4:14 PM movedata

Thank you for the suggestion, fixed my issue.

# The Power of SQL CASE Statements 4/10/2007 10:58 PM baikangwang

By Scott Mitchell

Title:
Name:
Url:
Enter the code you see:
Comments
Remember Me?