How to generate a list of first N binary numbers in Oracle SQL?

In my recent post I showed how to convert a decimal number (i.e. an integer) into a binary string. We can build upon that technique to answer the question:

WITH x AS (
SELECT LEVEL n
FROM dual
CONNECT BY LEVEL<=50
)
SELECT x.N, y.bin
FROM x, LATERAL (SELECT LISTAGG(SIGN(BITAND(x.N, POWER(2,LEVEL-1))),'') 
                        WITHIN GROUP(ORDER BY LEVEL DESC) bin
                 FROM dual
                 CONNECT BY POWER(2, LEVEL-1)<=x.N) y

Note the LATERAL keyword (Oracle 12c new feature) that enables us to reference “x” in the inline view “y”. In pre-12c world, we would have to use TABLE/CAST/MULTISET function composition to achieve the same result:

WITH x AS (
SELECT LEVEL n
FROM dual
CONNECT BY LEVEL<=50
)
SELECT x.N, y.column_value bin
FROM x, TABLE(CAST(MULTISET(
          SELECT LISTAGG(SIGN(BITAND(x.N, POWER(2,LEVEL-1))),'') 
                 WITHIN GROUP(ORDER BY LEVEL DESC) bin
          FROM dual
          CONNECT BY POWER(2, LEVEL-1)<=x.N) AS sys.odcivarchar2list)) y

The idea used in the following query is based on a totally different approach. It builds a string of “0”s and “1”s in a loop until its length reaches a desired value:

WITH x(v, n) AS (
SELECT column_value, 1
FROM TABLE(sys.odcivarchar2list('0','1'))
UNION ALL
SELECT x.v || t.column_value, x.n+1
FROM TABLE(sys.odcivarchar2list('0','1')) t JOIN x on LENGTH(x.v)=n
WHERE n<=CEIL(LOG(2,50))
), y AS (
SELECT NVL(LTRIM(x.v,'0'),'0') bin, ROWNUM-1 dec
FROM x
WHERE n=(SELECT MAX(n) FROM x)
)
SELECT *
FROM y
WHERE dec<=50

To better understand the above query, try the following one:

SELECT *                            
FROM TABLE(sys.odcivarchar2list('0','1')), 
     TABLE(sys.odcivarchar2list('0','1')),
     TABLE(sys.odcivarchar2list('0','1')),
     TABLE(sys.odcivarchar2list('0','1'))

If we put enough tables in the Cartesian product and concatenate all column_value columns in a single character string expression, we will achieve our goal. The challenge with this approach is to dynamically change the number of the tables in the FROM clause. This can be simulated in the recursive WITH clause by repeatedly adding more and more collections of bits (0 and 1).

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”.

 

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 (WARD), 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.

Get the root record when its parent id matches the root’s id, by Zahar Hilkevich

Very often developers decide to set parent id of a hierarchy root record to the record’s id value, meaning that the parent of that record is the record itself. This works fine as long as you don’t need to show a branch (or the entire) hierarchy from a record up to its very top root.

Let’s look at a query against the famous scott schema that comes with default Oracle’s database:

SELECT empno AS ID, ename AS name, mgr AS parent_id, LEVEL
 FROM emp
 CONNECT BY empno=PRIOR mgr
 START WITH empno=7788

Result:

   ID NAME        PARENT_ID      LEVEL
----- ---------- ---------- ----------
 7788 SCOTT            7566          1
 7566 JONES            7839          2
 7839 KING                           3

so far so good – note that King’s manager employee number (parent_id) is NULL.

Let’s update it to 7839 (King’s empno) and re-run the above SQL:

UPDATE emp
SET mgr=empno
WHERE empno=7839

Now, the result is quite different:

SQL> SELECT empno AS ID, ename AS name, mgr AS parent_id, LEVEL
 2  FROM emp
 3  CONNECT BY empno=PRIOR mgr
 4  START WITH empno=7788
 5  /
 ERROR:
 ORA-01436: CONNECT BY loop in user data

Of course, you can use NOCYCLE clause:

SELECT empno AS ID, ename AS name, mgr AS parent_id, LEVEL
FROM emp
CONNECT BY NOCYCLE empno=PRIOR mgr
START WITH empno=7788

Result:

  ID NAME        PARENT_ID      LEVEL
---- ---------- ---------- ----------
7788 SCOTT            7566          1
7566 JONES            7839          2

The KING has gone! Now the challenge is quite apparent – we need to see the entire branch that includes the KING!

The trick is to mimic the original design when the King’s parent id was NULL!

A very first idea is to use WITH clause:

WITH x AS (
 SELECT empno AS ID, ename AS name, DECODE(mgr,empno,NULL,mgr) AS parent_id
 FROM emp
 )
 SELECT ID, name, parent_id, LEVEL
 FROM x
 CONNECT BY id=PRIOR parent_id
 START WITH id=7788

Result:

  ID NAME       PARENT_ID       LEVEL
---- ---------- --------------- ----------
7788 SCOTT      7566            1
7566 JONES      7839            2
7839 KING                       3

Note, that parent_id for KING is shown as NULL even though we updated it to 7839 – this can be fixed by applying NVL on the parent_id if needed.

A closer look allowed us to find a nice workaround that does not rely on the WITH clause:

SELECT empno AS ID, ename AS name, mgr AS parent_id, LEVEL
FROM emp
CONNECT BY empno=PRIOR DECODE(mgr,empno,NULL,mgr)
START WITH empno=7788

Result:

  ID NAME        PARENT_ID      LEVEL
---- ---------- ---------- ----------
7788 SCOTT            7566          1
7566 JONES            7839          2
7839 KING             7839          3

Here we go!

Don’t forget to rollback the changes to set mgr=NULL for the KING’s record

P.S. If you like this trick, you can find many more in my book “Oracle SQL Tricks and Workarounds”