6 Solutions to Puzzle of the Week #19

Puzzle of the Week #19:

Produce the department salary report (shown below) with the following  assumptions/requirements:

  • Use Single SELECT statement only
  • DECODE and CASE functions are not allowed
  • An employee’s salary is shown in the corresponding department column (10, 20 or 30), all other department columns should contain NULLs.
  • The query should work in Oracle 11g.

Expected Result:

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

Solutions:

#1: Using NULLIF, ABS, and SIGN functions

SELECT ename, NULLIF(sal * (1-ABS(SIGN(deptno-10))),0) "10",
              NULLIF(sal * (1-ABS(SIGN(deptno-20))),0) "20",
              NULLIF(sal * (1-ABS(SIGN(deptno-30))),0) "30"
FROM emp

#2: Using NULLIF and INSTR functions

SELECT ename, NULLIF(sal * INSTR(deptno, 10), 0) "10",
              NULLIF(sal * INSTR(deptno, 20), 0) "20",
              NULLIF(sal * INSTR(deptno, 30), 0) "30"
FROM emp

#3: Using NVL2 and NULLIF functions

SELECT ename, NVL2(NULLIF(deptno, 10), NULL, 1) * sal "10",
              NVL2(NULLIF(deptno, 20), NULL, 1) * sal "20",
              NVL2(NULLIF(deptno, 30), NULL, 1) * sal "30"
FROM emp

#4: Using PIVOT clause

SELECT *
FROM  (SELECT deptno, ename, sal
       FROM emp)
PIVOT (MAX(sal)
       FOR deptno IN (10, 20, 30)
      );

#5: Using Scalar SELECT statements in SELECT clause

SELECT ename,
       (SELECT sal FROM emp WHERE empno=e.empno AND deptno=10) "10",
       (SELECT sal FROM emp WHERE empno=e.empno AND deptno=20) "20",
       (SELECT sal FROM emp WHERE empno=e.empno AND deptno=30) "30"
FROM emp e;

#6: Using UNION (different sort order)

SELECT ename, sal "10", NULL "20", NULL "30"
FROM emp
WHERE deptno=10
UNION
SELECT ename, NULL, sal, NULL
FROM emp
WHERE deptno=20
UNION
SELECT ename, NULL, NULL, sal
FROM emp
WHERE deptno=30;

ENAME              10         20         30
---------- ---------- ---------- ----------
ADAMS                       1100
ALLEN                                  1600
BLAKE                                  2850
CLARK            2450
FORD                        3000
JAMES                                   950
JONES                       2975
KING             5000
MARTIN                                 1250
MILLER           1300
SCOTT                       3000
SMITH                        800
TURNER                                 1500
WARD                                   125

My Oracle Group on Facebook:

If you like this post, you may want to join my new Oracle group on Facebook: https://www.facebook.com/groups/sqlpatterns/

Would you like to read about many more tricks and puzzles?

For more tricks and cool techniques check my book “Oracle SQL Tricks and Workarounds” for instructions.

Advertisements

Prevent Division by Zero with NULLIF function

If you want to avoid division by 0 issue, use NULLIF function to substitute zero in  denominator with NULL.

For example, the following query fails:

SQL> SELECT 100/(SELECT COUNT(*) FROM emp WHERE deptno=40)
 2 FROM dual;
 SELECT 100/(SELECT COUNT(*) FROM emp WHERE deptno=40)
 *
 ERROR at line 1:
 ORA-01476: divisor is equal to zero

To fix it, use NULLIF and the result will be NULL instead of error:

SELECT 100/NULLIF((SELECT COUNT(*) 
                   FROM emp 
                   WHERE deptno=40),0) expr
FROM dual;

My Oracle Group on Facebook:

If you like this post, you may want to join my new Oracle group on Facebook: https://www.facebook.com/groups/sqlpatterns/

Would you like to read about many more tricks and puzzles?

For more tricks and cool techniques check my book “Oracle SQL Tricks and Workarounds” for instructions.

Combine the power of COALESCE, GREATEST, and NULLIF functions

CASE function is extremely powerful though bulky. It looks and feels like a PL/SQL element even though it is just another SQL function. In some cases, we have an opportunity to use a different, more concise expression avoiding CASE function.

Let’s consider a problem: return a list of all employee names with respective salary and commission columns. If commission is NULL or 0, replace it with 10% of the salary.

A typical solution (with CASE) would look like this:

SELECT ename, sal, CASE WHEN NVL(comm,0)=0 THEN 0.1*sal ELSE comm END AS comm
FROM emp
ORDER BY 1;

Result:

ENAME             SAL       COMM
---------- ---------- ----------
ADAMS            1100        110
ALLEN            1600        300
BLAKE            2850        285
CLARK            2450        245
FORD             3000        300
JAMES             950         95
JONES            2975      297.5
KING             5000        500
MARTIN           1250       1400
MILLER           1300        130
SCOTT            3000        300
SMITH             800         80
TURNER           1500        150
WARD             1250        500

Before presenting a workaround, let’s review the raw data:

SELECT ename, sal, comm
FROM emp
ORDER BY 1;

Result:

ENAME             SAL       COMM
---------- ---------- ----------
ADAMS            1100
ALLEN            1600        300
BLAKE            2850
CLARK            2450
FORD             3000
JAMES             950
JONES            2975
KING             5000
MARTIN           1250       1400
MILLER           1300
SCOTT            3000
SMITH             800
TURNER           1500          0
WARD             1250        500

Essentially, we want to substitute the comm value for all employees except ALLEN, MARTIN, and WARD.

If we did not have to deal with $0 commission (TURNER), we could have used NVL(comm, 0.1*sal) expression, or COALESCE(comm, 0.1*sal) which works identically to NVL function for 2 parameters.

So if we could turn 0 into NULL, we would be able to employ NVL/COALESCE instead of CASE function.

Here comes the turn of NULLIF function. It can do exactly what we need: substitute 0 (or any other value) with NULL. It can be done by the following expression:

NULLIF(comm,0) -- which means: when comm=0 then return NULL.

There is one issue that needs to be resolved before we can use the COALSCE function. We cannot make 2 different expression returing NULL is 2 cases, when the argument is 0 or NULL. However, we can employ GREATEST (or LEAST) function to wrap up multiple arguments that may evaluate to NULL and return just one value – it will be NULL if any of the arguments of GREATEST evaluate to NULL.

So, finally, our workaround will look as follows:

SELECT ename, sal, COALESCE(GREATEST(comm, NULLIF(comm,0)), 0.1*sal) AS comm
FROM emp
ORDER BY 1;

Result:

ENAME             SAL       COMM
---------- ---------- ----------
ADAMS            1100        110
ALLEN            1600        300
BLAKE            2850        285
CLARK            2450        245
FORD             3000        300
JAMES             950         95
JONES            2975      297.5
KING             5000        500
MARTIN           1250       1400
MILLER           1300        130
SCOTT            3000        300
SMITH             800         80
TURNER           1500        150   <-- 0 is replaced with 150 (10%)
WARD             1250        500

COALESCE function comes really handy (combined with NULLIF & GREATEST/LEAST) when we have multiple values of a column that we would like to treat as 0.
For example, if we wanted to treat $0, $300, and $500 as NULLs we could have used the following expression:

COALESCE(GREATEST(comm, NULLIF(comm,0), NULLIF(comm,300), NULLIF(comm,500)), 0.1*sal)

The trick is hidden in the fact that GREATEST returns NULL if one of the parameters is a NULL.

For more tricks and cool techniques check my book “Oracle SQL Tricks and Workarounds” for instructions.