Nullif - 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

General SQL Server Forums

New to SQL Server Programming

Nullif

Author

Topic

ertweety
Starting Member

15 Posts

Posted - 2012-06-12 : 14:54:44

Hi There,
See code highlighted in red below. I have a 0 in my table for both YTD_ETA_HIT and YTD_ETA_OPPS. I'm trying to divide one by the other but keep getting a divide by 0 error. Help!

SELECT Q.MTH, 
       Q.VENDOR#, 
       Q.VENDOR_NAME,
       Q.CATEGORY,
       Q.ETA_YES,
       Q.TOTAL_ETA_OPS,
       (Q.ETA_yes/Q.TOTAL_ETA_OPS) AS ETA_PCT,
       R.YTD_ETA_HIT,
       R.YTD_ETA_OPPS,
       nsv(R.YTD_ETA_HIT/(nsv(R.YTD_ETA_OPPS,0)))AS YTD_ETA_PCT,
       S.NAT_MTH_ETA_HIT,
       S.NAT_MTH_ETA_OPPS,
       S.NAT_MTH_ETA_PCT,
       T.NAT_YTD_ETA_HIT,
       T.NAT_YTD_ETA_OPPS,
       T.NAT_YTD_ETA_PCT
FROM SC_10_ETA_ALL_CATEG Q,
     SC_11A_ETA_YTD R,
     SC_11B_ETA_MTH_NAT S,
     SC_11C_ETA_YTD_NAT T
WHERE Q.VENDOR# = R.VENDOR#
AND Q.MTH = S.MTH
AND Q.CATEGORY = R.CATEGORY
AND R.CATEGORY = S.CATEGORY
AND R.CATEGORY = T.CATEGORY
GROUP BY Q.MTH, 
       Q.VENDOR#, 
       Q.VENDOR_NAME,
       Q.CATEGORY,
       Q.ETA_YES,
       Q.TOTAL_ETA_OPS,
       R.YTD_ETA_HIT,
       R.YTD_ETA_OPPS,
       S.NAT_MTH_ETA_HIT,
       S.NAT_MTH_ETA_OPPS,
       S.NAT_MTH_ETA_PCT,
       T.NAT_YTD_ETA_HIT,
       T.NAT_YTD_ETA_OPPS,
       T.NAT_YTD_ETA_PCT
ORDER BY Q.VENDOR#, Q.MTH;

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-12 : 14:57:10

use

NULLIF(R.YTD_ETA_OPPS,0)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ertweety
Starting Member

15 Posts

Posted - 2012-06-12 : 14:59:25

I wrote it like this but I still get a divide by 0 error.

(R.YTD_ETA_HIT/ NULLIF(R.YTD_ETA_OPPS,0))AS YTD_ETA_PCT,

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-12 : 15:02:29

it wont. the error may be from other divide statement

(Q.ETA_yes/NULLIF(Q.TOTAL_ETA_OPS,0))

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ertweety
Starting Member

15 Posts

Posted - 2012-06-12 : 15:04:29

Duh! Sorry, dumb question.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-12 : 15:08:45

you're welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources