when table have no data ORA-01476 divisor is equal to zero tips in table sql

   SQL>  select nvl(ROUND(((NVL(SUM(45),0)/NULLIF(SUM(0),0))*100),2),0) divissor_error_remove from dual;

  DQTY_PER
----------
         0
//

or,

function RQTY_IQTY_PERFormula return Number is
begin
        if :RQTY<=0 then
        return(0);
    else
    return ((:IQTY/:RQTY)*100);
    end if;
end;



1. IS [NOT] NULL

Syntax: expr1 IS [NOT] NULL
SELECT * FROM emp t WHERE t.comm IS NULL;

2. NVL
Syntax: NVL(expr1, expr2)

if expr1 contains a NULL value, then replace it with the value of expr2
--The NVL function lets you substitute a value when a null value is encountered.

Examples:
NVL(‘A’,’B’) results in A
NVL(NULL,’B’) results in B
NVL(1,2) results in 1
NVL(NULL,2) results in 2
NVL(‘A’,2) results in A
NVL(1, ‘B’) results in an error


3.NVL2

Syntax: NVL2(expr1, expr2, expr3)

If expr1 contains a NULL value, then return expr3. If the value of expr1 contains a non-NULL value, then return expr2.

Examples:
NVL2(‘A’,’B’,’C’) results in B
NVL2(NULL,’B’,’C’) results in C
NVL2(1,2,3) results in 2
NVL2(NULL,2,3) results in 3


4. NULLIF

Syntax: NULLIF(expr1, expr2)


NULLIF returns NULL if expr1 is equal to expr2. If they are not equal expr1 is returned.
--Expressions must be of the same data type, There is no implicit conversion performed.

Examples:
NULLIF(‘A’,’B’) results in A
NULLIF(‘A’,’A’) results in NULL
NULLIF(2,3) results in 2
NULLIF(2,2) results in NULL
NULLIF(‘2’,2) results in an error
NULLIF(2,’2’) results in an error


5. COALESCE

Syntax: COALESCE(expr [, expr ]...)

The coalesce function returns the first non-NULL value of the expressions in the list. The list
must consist of at least 2 values. If all expressions evaluate to NULL then NULL is returned.

Examples:
COALESCE(‘A’,’B’,’C’) results in A
COALESCE(NULL,’B’,’C’) results in B
COALESCE(NULL,NULL,’C’) results in C
COALESCE(NULL,’B’,NULL) results in B
COALESCE(‘A’) results in an error


6. LNNVL

Syntax: LNNVL(condition)


The LNNVL function is used in the WHERE clause of an SQL statement when one of the
operands may contain a NULL value. The function returns TRUE is the result of the
condition is FALSE and FALSE is the result of the condition is TRUE or UNKNOWN. LNNVL
can be used in a condition when you would otherwise need to combine a condition with an
IS [NOT] NULL or an NVL predicate.

The following queries have the same result:

SELECT *
      FROM emp e
     WHERE LNNVL(e.comm >= 100);
    SELECT *
      FROM emp e
     WHERE 1 = 1
       AND ((e.comm))
      
      
7. DECODE

Syntax: DECODE(expr, search, result [, search, result ]... [, default ])

SELECT decode(color, 1, 'Red', 2, 'White', 3, 'Blue', 4, 'Yellow')
FROM table;

8. CASE

Syntax:

         CASE [ expression ]

   WHEN condition_1 THEN result_1
   WHEN condition_2 THEN result_2
   ...
   WHEN condition_n THEN result_n

   ELSE result

END;

The case statement has two flavors. The simple case and the searched case. In the simple case the expression is
written only once and depending on the result of this expression one of the possible cases is being used. This can be
helpful when you need to choose from a set of distinct values. In the searched case every case has its own expression.
This can for instance be helpful when using ranges.
The same query as above can be written using a simple case expression.

SELECT CASE color
         WHEN 1 THEN 'Red'
         WHEN 2 THEN 'White'
         WHEN 3 THEN 'Blue'
         WHEN 4 THEN 'Yellow'
         ELSE 'Unknown'
      END color
  FROM table;

The query can also be written using a searched case expression:

SELECT CASE
         WHEN color=1 THEN 'Red'
         WHEN color=2 THEN 'White'
         WHEN color=3 THEN 'Blue'
         WHEN color=4 THEN 'Yellow'
      END color
FROM table;