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