01476. 00000 - "divisor is equal to zero" Oracle-CodePudding

I have the following code as part of a Script:

ROUND
(
  (
    (COUNT(DISTINCT CASE WHEN ONLINE_SALES > 0 THEN CONTACT_KEY ELSE NULL END)) 
    / 
    (COUNT(DISTINCT CASE WHEN ONLINE_SALES > 0 OR OFFLINE_SALES > 0 THEN CONTACT_KEY ELSE NULL END))
  ),3
) AS UNIQ_ONLINE_SHOP_RATE

when I run the script I get the 'Divizer is equal to zero' erro.

I ran the denominator and numerator separately which both equal zero so I understand the error.

I have tried NULLiF(,0) as so:

ROUND
(
  (
    (
      COUNT(DISTINCT CASE WHEN ONLINE_SALES > 0 THEN CONTACT_KEY ELSE NULL END) / 
      nullif((COUNT(DISTINCT CASE WHEN ONLINE_SALES > 0 OR OFFLINE_SALES > 0 THEN CONTACT_KEY ELSE NULL END)),0)
    ),3
  ) AS UNIQ_ONLINE_SHOP_RATE

but then get 'FROM keyword not found where expected error.

CodePudding user response：

Is this your real query? I assume ONLINE_SALES > 0 or OFFLINE_SALES > 0 is the default and ONLINE_SALES = 0 or OFFLINE_SALES = 0 is the exception.

Then in most cases your query would result in

COUNT(DISTINCT CONTACT_KEY) / COUNT(DISTINCT CONTACT_KEY)

which is not so "exciting", i.e. always 1

With some best-guess I would do:

NULLIF(
   ROUND( 
      COUNT(DISTINCT CONTACT_KEY) 
      / 
      NULLIF(COUNT(DISTINCT ONLINE_SALES), 0) 
   , 3)
, 0) AS UNIQ_ONLINE_SHOP_RATE

CodePudding user response：

Here are 3 options (CASE, NULLIF and DECODE), visually simple to understand - use a CTE to calculate both values, while the main query divides them, taking care about 0 as the denominator:

with temp as
  (select
     COUNT(DISTINCT CASE WHEN ONLINE_SALES > 0                      THEN CONTACT_KEY ELSE NULL END) val1,
     COUNT(DISTINCT CASE WHEN ONLINE_SALES > 0 OR OFFLINE_SALES > 0 THEN CONTACT_KEY ELSE NULL END) val2
   from your_table
  )
select 
  -- using CASE
  val1 / case when val2 = 0 then null else val2 end  as result_1,
  -- using NULLIF
  val1 / nullif(val2, 0)                             as result_2
  -- using DECODE
  val1 / decode(val2, 0, null, val2)                 as result_3  
from temp;

Shouldn't be difficult to round the result at the end.