Work Around Divide By Zero Error in SQL SELECT

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

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