• Home
  • SQL & PL/SQL Guidelines
  • Publications / Talks
  • Videos
  • Monday Morning Tech Talk
  • About
  • Contact

Just another ORACLE Development blog

Just another ORACLE Development blog

Category Archives: Underestimated Features

NULLIF – A tiny, powerfull and underrated SQL Function

19 Monday Jun 2017

Posted by troroblog in Tipps and Tricks, Underestimated Features

≈ Leave a comment

Tags

ORACLE, SQL

NULLIF has been introducde quite a while ago (Oracle 9) but still this function is not well known to the community.

SYNTAX : NULLIF(expr1,expr2)

Nullif compares expr1 and expr2. If they are equal, then NULL is returned otherwise expr1. We cannot specify the literal NULL for expr1.

So where can this function be used?

Eliminiate Division by Zero:

WITH sales (prod, thisyear, lastyear)
        AS (SELECT 'Monitor' ,  50, 25 FROM dual UNION ALL
            SELECT 'Keyboard', 125,  0 FROM dual UNION ALL
            SELECT 'Mouse'   ,  35, 40 FROM dual UNION ALL 
            SELECT 'Desktop' ,   0, 25 FROM dual UNION ALL
            SELECT 'Laptop'  ,  10,  3 FROM dual)
SELECT prod, thisYear, lastYear
     , ROUND(thisYear * 100 / NULLIF(lastYear,0),2) AS SalesPerc
  FROM sales
/

PROD       THISYEAR   LASTYEAR  SALESPERC
-------- ---------- ---------- ----------
Monitor          50         25        200
Keyboard        125          0
Mouse            35         40       87.5
Desktop           0         25          0
Laptop           10          3     333.33

 

Remove not needed concatenation seperators:

Add first letter of the middle name followed by a dot if a middle name exists.

WITH pers (firstname, middle, lastname)
       AS (SELECT 'Kurt'  ,'Heinrich','Meier' FROM dual UNION ALL
           SELECT 'Hubert',NULL      ,'Huber' FROM dual)
SELECT firstname || NULLIF(' '||SUBSTR(middle,1,1) || '.',' .') 
    || ' ' || lastname AS fullname
  FROM pers
/    

FULLNAME
---------------
Kurt H. Meier
Hubert Huber

 


Suppress values above (or below) a given margin:

Do not show salaries greater/equal 3000.

SELECT ename, NULLIF(sal,GREATEST(sal,3000)) AS sal
  FROM emp
/    

ENAME             SAL
---------- ----------
SMITH             800
ALLEN            1600
WARD             1250
JONES            2975
MARTIN           1250
BLAKE            2850
CLARK            2450
SCOTT
KING
TURNER           1500
ADAMS            1100
JAMES             950
FORD
MILLER           1300

 

As you can see there are multiple opportunities to make use of this smart little function.

Disclaimer

The opinions I express on my blog are my own and not necessarily those of the company I am working for.

Archives

Tags

Analytic Functions CAST Compiler Error Compiler Warning CONNECT BY Data Conversion DATE DEFAULT FIRST_VALUE Identity Column LAST_VALUE LISTAGG Model Clause New Feature ORACLE ORACLE 11g Release 2 ORACLE 12c Release 1 ORACLE 12c Release 2 OUTER JOIN PL/SQL PRAGMA RECURSION Regular Expression ROUNDING Row Pattern Matching Sequence SQL TABIBITOSAN Top-N TO_CHAR TO_DATE TO_NUMBER Truncate Table VALIDATE_CONVERSION VALIDITY WM_CONCAT XMLAGG

Recent Posts

  • Antipatterns SQL & PL/SQL – Redefine oracle defined exceptions
  • ORA12R2 – IMPdp may change segment_column_id
  • Antipatterns SQL & PL/SQL – “SELECT *” even if you only need a few columns
  • Antipatterns SQL & PL/SQL – Substitute NULL with empty string
  • Antipatterns SQL & PL/SQL – Reusing table aliases in the same SQL statement

Blog at WordPress.com.

 

Loading Comments...