Work Around Divide By Zero Error in SQL SELECT

Question

SELECT 
   *
FROM 
   RM_Sales_Union
WHERE
   DOCDATE >= 'September 1, 2011'
   AND DOCDATE < 'October 1, 2011'  
   AND CUSTNMBR = '2186020'

That query results in an error:

Msg 8134, Level 16, State 1, Line 1
Divide by zero error encountered.

if run as shown.

Excluding the last line AND CUSTNMBR = '2186020' allows the query to complete.

CUSTNMBR is a char(21) field. Divide by zero has me confused.

What is the "correct" way to work around this?

RM_Sales_Union is a union query view:

SELECT ACTNUMBR_1,
       ACTNUMBR_2,
       ACTNUMBR_3,
       ORSLSAMT,
       CUSTCLAS,
       CUSTNAME,
       CUSTNMBR,
       SLPRSNID,
       DOCABREV,
       CSPORNBR,
       CURNCYID,
       DOCDATE,
       DOCNUMBR,
       GLPOSTDT,
       SLSAMNT,
       VOIDSTTS,
       SLPRSNFN,
       SPRSNSLN,
       DocOrigin,
       ORFRTAMT,
       FRTAMNT,
       COMPRCNT,
       TRDISAMT,
       ORTDISAM,
       ORMISCAMT,
       ORTAXAMT,
       ORCTRXAM
FROM   dbo.RM_Sales_Hist
UNION
SELECT ACTNUMBR_1,
       ACTNUMBR_2,
       ACTNUMBR_3,
       ORSLSAMT,
       CUSTCLAS,
       CUSTNAME,
       CUSTNMBR,
       SLPRSNID,
       DOCABREV,
       CSPORNBR,
       CURNCYID,
       DOCDATE,
       DOCNUMBR,
       GLPOSTDT,
       SLSAMNT,
       VOIDSTTS,
       SLPRSNFN,
       SPRSNSLN,
       DocOrigin,
       ORFRTAMT,
       FRTAMNT,
       COMPRCNT,
       TRDISAMT,
       ORTDISAM,
       ORMISCAMT,
       ORTAXAMT,
       ORCTRXAM
FROM   dbo.RM_Sales_Open

RM_Sales_Hist and RM_Sales_Open are views defined as follows:

--RM_Sales_Hist
SELECT dbo.GL_Sales_Accounts.ACTNUMBR_1,
       dbo.GL_Sales_Accounts.ACTNUMBR_2,
       dbo.GL_Sales_Accounts.ACTNUMBR_3,
       ISNULL(dbo.MC020102.ORSLSAMT, dbo.RM30101.SLSAMNT) AS ORSLSAMT,
       dbo.RM00101.CUSTCLAS,
       dbo.RM00101.CUSTNAME,
       dbo.RM00101.CUSTNMBR,
       dbo.RM00101.SLPRSNID,
       dbo.RM40401.DOCABREV,
       dbo.RM30101.CSPORNBR,
       dbo.RM30101.CURNCYID,
       dbo.RM30101.DOCDATE,
       dbo.RM30101.DOCNUMBR,
       dbo.RM30101.GLPOSTDT,
       dbo.RM30101.SLSAMNT,
       dbo.RM30101.VOIDSTTS,
       dbo.RM00301.SLPRSNFN,
       dbo.RM00301.SPRSNSLN,
       'HIST'                                             AS DocOrigin,
       ISNULL(dbo.MC020102.ORFRTAMT, dbo.RM30101.FRTAMNT) AS ORFRTAMT,
       dbo.RM30101.FRTAMNT,
       dbo.RM00301.COMPRCNT,
       dbo.RM30101.TRDISAMT,
       ISNULL(dbo.MC020102.ORTDISAM, 0)                   AS ORTDISAM,
       ISNULL(dbo.MC020102.ORMISCAMT, 0)                  AS ORMISCAMT,
       ISNULL(dbo.MC020102.ORTAXAMT, 0)                   AS ORTAXAMT,
       ISNULL(dbo.MC020102.ORCTRXAM, 0)                   AS ORCTRXAM,
       dbo.RM00101.STATE
FROM   dbo.GL_Sales_Accounts
       INNER JOIN dbo.RM30301
         ON dbo.GL_Sales_Accounts.DSTINDX = dbo.RM30301.DSTINDX
       INNER JOIN dbo.RM30101
         ON dbo.RM30301.CUSTNMBR = dbo.RM30101.CUSTNMBR
            AND dbo.RM30301.DOCNUMBR = dbo.RM30101.DOCNUMBR
       INNER JOIN dbo.RM00101
         ON dbo.RM30101.CUSTNMBR = dbo.RM00101.CUSTNMBR
       INNER JOIN dbo.RM40401
         ON dbo.RM30101.RMDTYPAL = dbo.RM40401.RMDTYPAL
       INNER JOIN dbo.RM00301
         ON dbo.RM00101.SLPRSNID = dbo.RM00301.SLPRSNID
       LEFT OUTER JOIN dbo.MC020102
         ON dbo.RM30301.DOCNUMBR = dbo.MC020102.DOCNUMBR
WHERE  ( CAST(dbo.RM30301.DOCNUMBR AS VARCHAR(21)) NOT IN (SELECT
         CAST(
                                                                 DOCNUMBR AS
                                                                 VARCHAR(21)) AS
         Expr1
                                                           FROM
                  dbo.Invoices_With_Display_Discounts) )

--RM_Sales_Open
SELECT dbo.GL_Sales_Accounts.ACTNUMBR_1,
       dbo.GL_Sales_Accounts.ACTNUMBR_2,
       dbo.GL_Sales_Accounts.ACTNUMBR_3,
       ISNULL(dbo.MC020102.ORSLSAMT, 0)  AS ORSLSAMT,
       dbo.RM00101.CUSTCLAS,
       dbo.RM00101.CUSTNAME,
       dbo.RM00101.CUSTNMBR,
       dbo.RM00101.SLPRSNID,
       dbo.RM40401.DOCABREV,
       dbo.RM20101.CSPORNBR,
       dbo.RM20101.CURNCYID,
       dbo.RM20101.DOCDATE,
       dbo.RM20101.DOCNUMBR,
       dbo.RM20101.GLPOSTDT,
       dbo.RM20101.SLSAMNT,
       dbo.RM20101.VOIDSTTS,
       dbo.RM00301.SLPRSNFN,
       dbo.RM00301.SPRSNSLN,
       'OPEN'                            AS DocOrigin,
       ISNULL(dbo.MC020102.ORFRTAMT, 0)  AS ORFRTAMT,
       dbo.RM20101.FRTAMNT,
       dbo.RM00301.COMPRCNT,
       dbo.RM20101.TRDISAMT,
       ISNULL(dbo.MC020102.ORTDISAM, 0)  AS ORTDISAM,
       ISNULL(dbo.MC020102.ORMISCAMT, 0) AS ORMISCAMT,
       ISNULL(dbo.MC020102.ORTAXAMT, 0)  AS ORTAXAMT,
       ISNULL(dbo.MC020102.ORCTRXAM, 0)  AS ORCTRXAM,
       dbo.RM00101.STATE
FROM   dbo.GL_Sales_Accounts
       INNER JOIN dbo.RM10101
         ON dbo.GL_Sales_Accounts.DSTINDX = dbo.RM10101.DSTINDX
       INNER JOIN dbo.RM20101
         ON dbo.RM10101.CUSTNMBR = dbo.RM20101.CUSTNMBR
            AND dbo.RM10101.DOCNUMBR = dbo.RM20101.DOCNUMBR
       INNER JOIN dbo.RM00101
         ON dbo.RM20101.CUSTNMBR = dbo.RM00101.CUSTNMBR
       INNER JOIN dbo.RM40401
         ON dbo.RM20101.RMDTYPAL = dbo.RM40401.RMDTYPAL
       INNER JOIN dbo.RM00301
         ON dbo.RM00101.SLPRSNID = dbo.RM00301.SLPRSNID
       LEFT OUTER JOIN dbo.MC020102
         ON dbo.RM10101.DOCNUMBR = dbo.MC020102.DOCNUMBR
WHERE  ( CAST(dbo.RM20101.DOCNUMBR AS VARCHAR(21)) NOT IN (SELECT
         CAST(
                                                                 DOCNUMBR AS
                                                                 varchar(21)) AS
         Expr1
                                                           FROM
                  dbo.Invoices_With_Display_Discounts) )

Is RM_Sales_Union a view? If so, show us the definition please — gbn
– gbn, Commented Oct 12, 2011 at 16:46
I see nothing in your query that could cause this error. Does the RM_Sales_Union table have any computed fields that may be throwing this error? — David
– David, Commented Oct 12, 2011 at 16:48
Added further definitions of queries. No computed fields. Yes, the queries are ugly and horribly nested ... — Shane Brodie
– Shane Brodie, Commented Oct 12, 2011 at 19:22
It appears to be a problem with how MSSQL treats whitespace in comparisons. Wrapping CUSTNMBR in RTRIM(LTRIM()) resolves the issue. But, it does not give me a warm and fuzzy feeling. — Shane Brodie
– Shane Brodie, Commented Oct 12, 2011 at 19:47
That won't have caused the divide by zero error. It must have changed the plan in some way so it no longer occurs (e.g by moving a compute scalar to happen after a filter instead of before it). There must be some division going on in one of the referenced objects. Probably easiest way would be to do SET SHOWPLAN_TEXT ON and search for / or look at the compute scalar operators to find one containing a division. Might also be buried in a UDF though. — Martin Smith
– Martin Smith, Commented Oct 12, 2011 at 19:58

gbn · Accepted Answer · 2011-10-12 16:51:51Z

5

I guess that RM_Sales_Union is a view that contains a division. The filter simply changes something like a COUNT or SUM to give zero, and it is this that is used as divisor

Change it to use something/NULLIF(..., 0) to make the expressions give NULL. Or ISNULL(something/NULLIF(..., 0), 0) to get NULL instead

answered Oct 12, 2011 at 16:51

gbn

434k84 gold badges602 silver badges690 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Work Around Divide By Zero Error in SQL SELECT

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related