Oracle View problem with Select and divison zero

Question

Hi Folks,

following problem, i want to create an view in Oracle wich calculates an "utilization rate in percent".

AS SELECT
  sw.SWITCH_ID,
  sw.ASSET_ID,
  sw.SYSTEMNAME,
  sw.MAX_INSTALLABLE_PORTS,
  sw.INSTALLED_PORTS,
  sw.USED_PORTS,
  (sw.INSTALLED_PORTS/sw.MAX_INSTALLABLE_PORTS)*100 AS UTIL_INSTALLED_PORTS,
  sw.RES_INFRASTRUCTURE_PORTS,
  sw.USED_INFRASTRUCTURE_PORTS,
  sw.FREE_INFRASTRUCTURE_PORTS,
  (sw.INSTALLED_PORTS/sw.MAX_INSTALLABLE_PORTS)*100 AS UTIL_INFRASTRUCTURE_PORTS,
  sw.RESERVED_DEVICE_PORTS,
  sw.USED_DEVICE_PORTS,
  sw.FREE_DEVICE_PORTS,
  (sw.FREE_DEVICE_PORTS/sw.RESERVED_DEVICE_PORTS)*100 AS UTIL_DEVICE_PORTS,
  sw.RUN_DATE

Problem: sometimes sw.INSTALLED_PORTS or sw.MAX_INSTALLABLE_PORTS can be NULL (same for other UTIL Rows)..

Is there any nice way to do something like:

if (sw.INSTALLED_PORTS or sw.MAX_INSTALLABLE_PORTS == null) 
      UTIL_INSTALLABLE_PORTS = null 
else (sw.INSTALLED_PORTS/sw.MAX_INSTALLABLE_PORTS)*100 AS UTIL_INSTALLABLE_PORTS,

Answer 1

CASE
   WHEN sw.INSTALLED_PORTS IS NULL OR sw.MAX_INSTALLABLE_PORTS IS NULL THEN NULL
   ELSE (sw.INSTALLED_PORTS/sw.MAX_INSTALLABLE_PORTS)*100
END UTIL_INFRASTRUCTURE_PORTS

Answer 2

You could use COALESCE() which returns the first non-null from its arguments, like :

(COALESCE(sw.INSTALLED_PORTS, 0)/COALESCE(sw.MAX_INSTALLABLE_PORTS,1))*100  
 AS UTIL_DEVICE_PORTS

Note that I used 1 as the coalesce default in the denominator, you don't want a DIVIDE/0 instead.

Answer 3

Divizion by NULL is not the same as divizion by zero (as you reference the problem in the title).

select 1/null from dual = null
select null/null from dual = null

So you'll automatically get what you want by (sw.INSTALLED_PORTS/sw.MAX_INSTALLABLE_PORTS)*100.

I think, the problem is when sw.MAX_INSTALLABLE_PORTS is zero. In this case you can use the following:

case
  when sw.MAX_INSTALLABLE_PORTS = 0 then null
  else (sw.INSTALLED_PORTS/sw.MAX_INSTALLABLE_PORTS)*100
end

Answer 4

or a little shorter:

sw.INSTALLED_PORTS/NULLIF(sw.MAX_INSTALLABLE_PORTS,0)

Regards, Rob.