All Forums Database
tclear 12 posts Joined 06/12
03 Dec 2013
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.
 

M.Saeed Khurram 544 posts Joined 09/12
03 Dec 2013

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

tclear 12 posts Joined 06/12
04 Dec 2013

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

Kawish_Siddiqui 37 posts Joined 03/07
04 Dec 2013

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

Kawish Siddiqui -

tclear 12 posts Joined 06/12
04 Dec 2013

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
 

Kawish_Siddiqui 37 posts Joined 03/07
04 Dec 2013

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

 

Kawish Siddiqui -

tclear 12 posts Joined 06/12
04 Dec 2013

That did it. I thought I got all NULLIFZERO entered.
Thanks for your help and quick response.

Kawish_Siddiqui 37 posts Joined 03/07
04 Dec 2013

Did it work fine for you?

Kawish Siddiqui -

tclear 12 posts Joined 06/12
04 Dec 2013

yes it did. thanks again.

gaynour 1 post Joined 08/15
08 Sep 2015

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

 

You must sign in to leave a comment.