Why does this case stmt not catch? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

General SQL Server Forums

New to SQL Server Programming

Why does this case stmt not catch?

Author

Topic

SQLSoaker
Posting Yak Master

169 Posts

Posted - 2009-09-01 : 15:23:26

SELECT

CASE
isnull( (INVENTSUM.POSTEDQTY + (INVENTSUM.RECEIVED - INVENTSUM.DEDUCTED)), 0) when 0 then '0'
ELSE
(MAX((INVENTSUM.POSTEDVALUE + INVENTSUM.PHYSICALVALUE)
/ (INVENTSUM.POSTEDQTY + (INVENTSUM.RECEIVED - INVENTSUM.DEDUCTED))) )
END AS [UNIT COST],

CASE
isnull((INVENTSUM.POSTEDQTY + (INVENTSUM.RECEIVED - INVENTSUM.DEDUCTED)), 0) when 0 then '0'
ELSE
((INVENTSUM.PHYSICALINVENT * (INVENTSUM.POSTEDVALUE + INVENTSUM.PHYSICALVALUE))
/ (INVENTSUM.POSTEDQTY + (INVENTSUM.RECEIVED - INVENTSUM.DEDUCTED)) )
END AS COST

Getting a divide by zero error even with this case statement.

Anyone know why?

Many Thanks!

SQLSoaker
Posting Yak Master

169 Posts

Posted - 2009-09-01 : 16:04:34

Narrowed it down to the MAX function but how to compensate for that in the case statement?

Thanks.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2009-09-01 : 16:30:29

Something like this:

select
	max(
	case
	when (divisor expression) = 0
	then null
	else (expression) / (divisor expression)
	end)

CODO ERGO SUM

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-09-01 : 18:22:06

If you are just trying to prevent divid by zero errors you can use the old NULLIF/COALESCE "trick."

select max((expression) / NULLIF(COALESCE(divisor expression, 0), 0))

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources