Error 2618 Invalid Calculation Division by Zero

Database
Enthusiast

Error 2618 Invalid Calculation Division by Zero

I have checked, and checked, included NULLIFZERO where applicable.

When I run the query with this last filter commented out

-- AND ABS(REC_VAL - ORIG_VAL)>3

the query returns records.

Remove the comment (--)

AND ABS(REC_VAL - ORIG_VAL)>3

the SQL Assistant returns the divide by zero error.

REC_VAL is a calculated recommended replacement value

for the ORIG_VAL.

Want to filter out any cases where the recommended value is

less than three units (+/-) from the original value.

Tags (2)
9 REPLIES
Enthusiast

Re: Error 2618 Invalid Calculation Division by Zero

Hi,

Can you please peovide the complete Where clause, Or the previous part of this AND condition?

It will help us to understand the problem.

Khurram
Enthusiast

Re: Error 2618 Invalid Calculation Division by Zero

WHERE F.STORE_NBR = S.STORE_NBR

 AND F.ITEM_NBR = S.ITEM_NBR

 AND F.ITEM_NBR = P.ITEM_NBR

 AND F.ITEM_NBR = I.ITEM_NBR

 AND F.ORIG_VAL>0

 AND F.SUB_VAL >0

 AND F.ORIG_VAL <> REC_TTL

-- AND ABS(REC_VAL - ORIG_VAL)>3

with the last line commented out, the query runs.

remove the comments, the divide by zero error is returned

Enthusiast

Re: Error 2618 Invalid Calculation Division by Zero

I think we need to look at complete SQL Statement and it's logic. Seems this filter is causing 'return zero' somehow.

Enthusiast

Re: Error 2618 Invalid Calculation Division by Zero

Here is the actual SQL:

SELECT  I.REPL_GROUP_NBR AS DMDUNIT

 ,'-' AS DMDGROUP

 , 'US_WMT_ST_'||SUBSTR(TRIM(CAST(100000+TRIM(F.STORE_NBR) AS INTEGER)),2,5) AS LOC

 , 'LEW_POS' AS MODEL

 , 'DMDWK' AS DMDCAL

 , 3 AS MODTYPE

 , 'PROJECT STORES' AS DESCR

 , (SELECT MAX(GREGORIAN_DATE)

  FROM US_WM_VM.CALENDAR_DAY

  WHERE GREGORIAN_DATE BETWEEN DATE - 7 AND DATE

  AND SAT_MULT = 1) AS STARTDATE

 , REC_MEAN AS ADJVAL

 , F.STORE_NBR

 , I.DEPT_NBR

 , I.OLD_NBR AS WMT_NBR

 , I.ITEM1_DESC AS ITEM_DESC

 , F.BASE_FCST

 , F.TTL_FCST

 , S.ON_HAND_1_QTY

 , S.MAX_SALE_FLOOR_QTY

 , ZEROIFNULL(P.CK_ST_POS*1.00/NULLIFZERO(S.OPEN_DAYS)) AS AVG_DLY_POS

 , P.CK_ST_POS AS L7D_POS

 , ZEROIFNULL((P.CK_ST_POS*1.00/NULLIFZERO(S.OPEN_DAYS))*7) AS AVG_WKLY_POS 

 , CASE WHEN P.ZONE_STORE = 0 THEN 0 ELSE(P.ZONE_ST_POS*1.00/P.ZONE_STORE)/7 END AS ZN_AVG_DLY_POS

 , CASE WHEN P.ZONE_STORE = 0 THEN 0 ELSE(P.ZONE_ST_POS*1.00/P.ZONE_STORE) END AS ZN_AVG_WKLY_POS 

 , CASE WHEN P.CORP_STORE = 0 THEN 0 ELSE (P.CORP_POS*1.00/P.CORP_STORE)/7 END AS CORP_AVG_DLY_POS

 , CASE WHEN P.CORP_STORE = 0 THEN 0 ELSE (P.CORP_POS*1.00/P.CORP_STORE) END AS CORP_AVG_WKLY_POS 

 , CASE  WHEN AVG_WKLY_POS > F.TTL_FCST THEN 1 ELSE 0 END AS SALES_DIR

 , CASE  WHEN AVG_WKLY_POS > F.TTL_FCST

   THEN CASE  WHEN ZN_AVG_WKLY_POS  > CORP_AVG_WKLY_POS

      THEN ZN_AVG_WKLY_POS 

      ELSE CORP_AVG_WKLY_POS END *1.20

   ELSE CASE  WHEN ZN_AVG_WKLY_POS = 0 THEN CORP_AVG_WKLY_POS

      WHEN ZN_AVG_WKLY_POS  < CORP_AVG_WKLY_POS

      THEN ZN_AVG_WKLY_POS 

      ELSE CORP_AVG_WKLY_POS END *0.80 END AS CZCUR

, CASE  WHEN AVG_WKLY_POS + F.TTL_FCST = 0 THEN CZCUR

  WHEN AVG_WKLY_POS = 0 THEN F.TTL_FCST

  WHEN AVG_WKLY_POS >= F.TTL_FCST THEN

   CASE WHEN CZCUR >= F.TTL_FCST THEN

    CASE WHEN CZCUR >= AVG_WKLY_POS THEN AVG_WKLY_POS

    ELSE CZCUR END

    ELSE AVG_WKLY_POS END

  WHEN AVG_WKLY_POS < F.TTL_FCST THEN

   CASE WHEN CZCUR <  F.TTL_FCST THEN

    CASE WHEN CZCUR < AVG_WKLY_POS THEN AVG_WKLY_POS

    ELSE CZCUR END

    ELSE AVG_WKLY_POS END END AS REC_TTL

, CASE WHEN F.BASE_FCST > F.TTL_FCST THEN ((F.BASE_FCST*REC_TTL*1.00)/F.TTL_FCST)

 ELSE ((F.BASE_FCST*1.00)/NULLIFZERO(F.TTL_FCST))*REC_TTL END AS REC_MEAN

 , CASE WHEN BASE_FCST = 0

  THEN 0

  ELSE ((REC_MEAN - BASE_FCST)/BASE_FCST )*100.00 END AS PCT_DIFF

FROM WM_AD_HOC.NS_CK_SKU_FCST F

 , WM_AD_HOC.NS_SKU S

 , WM_AD_HOC.NS_ITEM_POS P

 , WM_AD_HOC.NS_ITEM I

WHERE F.STORE_NBR = S.STORE_NBR

 AND F.ITEM_NBR = S.ITEM_NBR

 AND F.ITEM_NBR = P.ITEM_NBR

 AND F.ITEM_NBR = I.ITEM_NBR

 AND F.TTL_FCST>0

 AND F.BASE_FCST >0

 AND F.TTL_FCST <> REC_TTL

-- AND ABS(REC_MEAN - TTL_FCST)>3

Enthusiast

Re: Error 2618 Invalid Calculation Division by Zero

Let me look at this SQL, meanwhile can you try following sql and let me know if still getting same error?

SELECT  I.REPL_GROUP_NBR AS DMDUNIT
,'-' AS DMDGROUP
, 'US_WMT_ST_'||SUBSTR(TRIM(CAST(100000+TRIM(F.STORE_NBR) AS INTEGER)),2,5) AS LOC
, 'LEW_POS' AS MODEL
, 'DMDWK' AS DMDCAL
, 3 AS MODTYPE
, 'PROJECT STORES' AS DESCR
, (SELECT MAX(GREGORIAN_DATE)
FROM US_WM_VM.CALENDAR_DAY
WHERE GREGORIAN_DATE BETWEEN DATE - 7 AND DATE
AND SAT_MULT = 1) AS STARTDATE
, REC_MEAN AS ADJVAL
, F.STORE_NBR
, I.DEPT_NBR
, I.OLD_NBR AS WMT_NBR
, I.ITEM1_DESC AS ITEM_DESC
, F.BASE_FCST
, F.TTL_FCST
, S.ON_HAND_1_QTY
, S.MAX_SALE_FLOOR_QTY
, ZEROIFNULL(P.CK_ST_POS*1.00/NULLIFZERO(S.OPEN_DAYS)) AS AVG_DLY_POS
, P.CK_ST_POS AS L7D_POS
, ZEROIFNULL((P.CK_ST_POS*1.00/NULLIFZERO(S.OPEN_DAYS))*7) AS AVG_WKLY_POS
, CASE WHEN P.ZONE_STORE = 0 THEN 0 ELSE(P.ZONE_ST_POS*1.00/P.ZONE_STORE)/7 END AS ZN_AVG_DLY_POS
, CASE WHEN P.ZONE_STORE = 0 THEN 0 ELSE(P.ZONE_ST_POS*1.00/P.ZONE_STORE) END AS ZN_AVG_WKLY_POS
, CASE WHEN P.CORP_STORE = 0 THEN 0 ELSE (P.CORP_POS*1.00/P.CORP_STORE)/7 END AS CORP_AVG_DLY_POS
, CASE WHEN P.CORP_STORE = 0 THEN 0 ELSE (P.CORP_POS*1.00/P.CORP_STORE) END AS CORP_AVG_WKLY_POS
, CASE WHEN AVG_WKLY_POS > F.TTL_FCST THEN 1 ELSE 0 END AS SALES_DIR
, CASE WHEN AVG_WKLY_POS > F.TTL_FCST
THEN CASE WHEN ZN_AVG_WKLY_POS > CORP_AVG_WKLY_POS
THEN ZN_AVG_WKLY_POS
ELSE CORP_AVG_WKLY_POS END *1.20
ELSE CASE WHEN ZN_AVG_WKLY_POS = 0 THEN CORP_AVG_WKLY_POS
WHEN ZN_AVG_WKLY_POS < CORP_AVG_WKLY_POS
THEN ZN_AVG_WKLY_POS
ELSE CORP_AVG_WKLY_POS END *0.80 END AS CZCUR
, CASE WHEN AVG_WKLY_POS + F.TTL_FCST = 0 THEN CZCUR
WHEN AVG_WKLY_POS = 0 THEN F.TTL_FCST
WHEN AVG_WKLY_POS >= F.TTL_FCST THEN
CASE WHEN CZCUR >= F.TTL_FCST THEN
CASE WHEN CZCUR >= AVG_WKLY_POS THEN AVG_WKLY_POS
ELSE CZCUR END
ELSE AVG_WKLY_POS END
WHEN AVG_WKLY_POS < F.TTL_FCST THEN
CASE WHEN CZCUR < F.TTL_FCST THEN
CASE WHEN CZCUR < AVG_WKLY_POS THEN AVG_WKLY_POS
ELSE CZCUR END
ELSE AVG_WKLY_POS END END AS REC_TTL
, CASE WHEN F.BASE_FCST > F.TTL_FCST THEN ((F.BASE_FCST*REC_TTL*1.00)/NULLIFZERO(F.TTL_FCST))
ELSE ((F.BASE_FCST*1.00)/NULLIFZERO(F.TTL_FCST))*REC_TTL END AS REC_MEAN
, CASE WHEN BASE_FCST = 0
THEN 0
ELSE ((REC_MEAN - BASE_FCST)/BASE_FCST )*100.00 END AS PCT_DIFF
FROM WM_AD_HOC.NS_CK_SKU_FCST F
, WM_AD_HOC.NS_SKU S
, WM_AD_HOC.NS_ITEM_POS P
, WM_AD_HOC.NS_ITEM I

WHERE F.STORE_NBR = S.STORE_NBR
AND F.ITEM_NBR = S.ITEM_NBR
AND F.ITEM_NBR = P.ITEM_NBR
AND F.ITEM_NBR = I.ITEM_NBR
AND F.TTL_FCST>0
AND F.BASE_FCST >0
AND F.TTL_FCST <> REC_TTL
AND ABS(REC_MEAN - TTL_FCST)>3
Enthusiast

Re: Error 2618 Invalid Calculation Division by Zero

That did it. I thought I got all NULLIFZERO entered.

Thanks for your help and quick response.

Enthusiast

Re: Error 2618 Invalid Calculation Division by Zero

Did it work fine for you?

Enthusiast

Re: Error 2618 Invalid Calculation Division by Zero

yes it did. thanks again.

New Member

Re: Error 2618 Invalid Calculation Division by Zero

I got the same issue using the query below...

Select 

tbl_a.AMOUNT - tbl_b.AMOUNT as  "DIFFERENCE",

( "DIFFERENCE"   / tbl_b.AMOUNT  *100) as "PERCENT"

from tbl_a, tbl_b

Thanks to @Kawish_Siddiqui's answer, it was also resolved using nullifzero function.

Select 

tbl_a.AMOUNT - tbl_b.AMOUNT as  "DIFFERENCE",

(nullifzero("DIFFERENCE")   / tbl_b.AMOUNT  *100) as "PERCENT"

from tbl_a, tbl_b