IsZero? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

IsZero?

Author

Topic

yonabout
Posting Yak Master

112 Posts

Posted - 2008-05-08 : 07:01:21

is there a similar function to the isnull() function, but that will replace zeros instead of nulls?

If not, would it be more efficient to write a user function to do something similar, or to handle the replacement of zeros with another value in the main query?

I'm going down the user function route unless I hear otherwise.

Cheers,

Yonabout

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2008-05-08 : 07:09:01

What would you want to replace 0 with?

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2008-05-08 : 07:09:09

You can handle it in the main query using something like one of these...

case when a = 0 then b else a end
isnull(nullif(a, 0), b)

Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-05-08 : 07:09:23

[code]IsNull(Nullif(col, 0), <value>)[/code]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

yonabout
Posting Yak Master

112 Posts

Posted - 2008-05-08 : 07:48:16

Hi,

RickD -
I'd be replacing it in this instance with 1.

RyanRandall -
I was thinking of using a case statement, but its part of a calculation, and I need to make sure the divisor isn't zero, so I don't get divide by zero errors. I was thinking a case statement would get too messy.

harsh_athalyte -
So I Null the value if its zero using the NullIF, then wrap that in an isnull to change the null to what I want? - Sounds like a winner.

Thanks All for your help

Cheers,

Yonabout

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2008-05-08 : 08:17:55

Well done, harsh - you are the winner!

Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.

yonabout
Posting Yak Master

112 Posts

Posted - 2008-05-08 : 08:30:14

sorry - didn't mean it to come out like that!

(harsh did win though!)

Cheers,

Yonabout

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-05-08 : 08:38:02

Thanks Yonabout!

But I think I share that title with Ryan since he also posted similar solution...although not formatted quite well to be distinguished as two different solutions.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-05-09 : 10:15:45

Small point but...

If you use :


IsNull(Nullif(col, 0), <value>)

Won't this replace any value that is 0 or NULL with the value you specify rather than only replacing 0 with your value?

Doesn't matter if you can treat all your NULL's as 0 but it's something to be aware of if not.

Charlie.

-------------
Charlie

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-09 : 10:50:25

quote:
Originally posted by Transact Charlie

Small point but...

If you use :

IsNull(Nullif(col, 0), <value>)

Won't this replace any value that is 0 or NULL with the value you specify rather than only replacing 0 with your value?

Doesn't matter if you can treat all your NULL's as 0 but it's something to be aware of if not.

Charlie.

-------------
Charlie

Yup. thats true. If col has any other NULL, that will also be replaced by the <value>

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2008-05-09 : 10:53:09

You're quite right Charlie. I considered mentioning this myself but I got side-tracked with my other point

.

Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-05-09 : 11:29:33

Case is fine for inline calculations and it's not *too* ugly.

There is something stylish about the IsNull(Nullif(col, 0), <value> though....

Example...


SELECT
	10 / CASE yt.[col] WHEN 0 THEN 1 ELSE yt.[col] END
FROM
	yourTable yt

If you have NULL(s) the output of the calculation above will be NULL as well for them but 0 values would be 10. If you used something like...


SELECT
	10 / IsNull(Nullif(yt.[col], 0), 1)
FROM
	yourTable yt

Then any NULL values would resolve to 10 (same as zero values)

-------------
Charlie

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-05-09 : 13:23:11

Kinda depends what you want you output to look like:

DECLARE @Yak TABLE (Num INT, Den INT)

INSERT @Yak
SELECT 1, 1
UNION ALL SELECT 10, 1
UNION ALL SELECT 1, 0
UNION ALL SELECT 5, 2
UNION ALL SELECT 20, 0
UNION ALL SELECT 20, NULL

-- Errors
--SELECT Num / Den
--FROM @Yak

SELECT Num / NULLIF(Den, 0)
FROM @Yak

SELECT Num / COALESCE(NULLIF(Den, 0), 1)
FROM @Yak

SELECT COALESCE(Num / NULLIF(Den, 0), 0)
FROM @Yak

pom_iti
Starting Member

1 Post

Posted - 2009-08-11 : 06:12:51

CREATE FUNCTION dbo.iszero(@Value numeric,@Replace int)
RETURNS int
AS
BEGIN
DECLARE @Result int
IF @Value = 0
SET @Result = @Replace
ELSE
SET @Result = @Value
RETURN @Result
END

Test

SELECT
Id AS ID,
[IND Volume],
[Volume Grade],
[Volume Grade] / dbo.iszero([IND Volume],null) AS DIV_Result
FROM dbo.LampCustomerAll
WHERE (ASM_Code = 'LM5000') AND ([IND Volume] = 0) OR ([IND Volume] = 2)
ORDER BY Id DESC

copy from here -> http://ms-sql-function.blogspot.com/2007/09/iszero.html

:)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources