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

# Puzzle of the Week #12

With a single SELECT statement produce a list of first 10 prime numbers above a given number of N.

Expected Result: (for N=15)

```     Prime
----------
17
19
23
29
31
37
41
43
47
53

10 rows selected.```

Expected Result: (for N=50)

```     Prime
----------
53
59
61
67
71
73
79
83
89
97

10 rows selected.```

### Solutions:

#1: Liming number of found prime numbers in CTE (Recursive WITH clsue)

```WITH y AS (
SELECT 500 fromN
FROM dual
), x (n, cnt, flag) AS (
SELECT fromN,
(SELECT CASE WHEN COUNT(*)=2 THEN 1 ELSE 0 END
FROM dual
WHERE MOD(fromN, LEVEL)=0
CONNECT BY LEVEL<=fromN),
(SELECT CASE WHEN COUNT(*)=2 THEN 1 ELSE 0 END
FROM dual
WHERE MOD(fromN, LEVEL)=0
CONNECT BY LEVEL<=fromN)
FROM y
UNION ALL
SELECT x.n+1, (SELECT x.cnt+CASE WHEN COUNT(*)=2 THEN 1 ELSE 0 END
FROM dual
WHERE MOD(x.n+1, LEVEL)=0
CONNECT BY LEVEL<=x.n+1),
(SELECT CASE WHEN COUNT(*)=2 THEN 1 ELSE 0 END
FROM dual
WHERE MOD(x.n+1, LEVEL)=0
CONNECT BY LEVEL<=x.n+1)
FROM x
WHERE x.cnt```

#2: Limiting number of found prime numbers outside of CTE (Recursive WITH clsue)

```WITH y AS (
SELECT 50 fromN
FROM dual
), x (n, flag) AS (
SELECT fromN,
(SELECT CASE WHEN COUNT(*)=2 THEN 1 ELSE 0 END
FROM dual
WHERE MOD(fromN, LEVEL)=0
CONNECT BY LEVEL<=fromN)
FROM y
UNION ALL
SELECT x.n+1, (SELECT CASE WHEN COUNT(*)=2 THEN 1 ELSE 0 END
FROM dual
WHERE MOD(x.n+1, LEVEL)=0
CONNECT BY LEVEL<=x.n+1) FROM x WHERE x.n>0
)
SELECT n AS prime
FROM x
WHERE flag=1
AND ROWNUM<=10;

PRIME
----------
53
59
61
67
71
73
79
83
89
97

10 rows selected.

Elapsed: 00:00:00.02```

#3: Using TABLE and MULTISET functions

```WITH y AS (
SELECT 16 fromN
FROM dual
), x (n, flag) AS (
SELECT fromN, column_value flag
FROM y, TABLE(CAST(MULTISET(SELECT CASE WHEN COUNT(*)=2 THEN 1 ELSE 0 END flag
FROM dual
WHERE MOD(fromN, LEVEL)=0
CONNECT BY LEVEL<=fromN) AS sys.odcinumberlist))
UNION ALL
SELECT x.n+1, column_value flag
FROM x, TABLE(CAST(MULTISET(SELECT CASE WHEN COUNT(*)=2 THEN 1 ELSE 0 END flag
FROM dual
WHERE MOD(x.n+1, LEVEL)=0
CONNECT BY LEVEL<=x.n+1) AS sys.odcinumberlist))  WHERE x.n>0
)
SELECT n AS prime
FROM x
WHERE flag=1
AND ROWNUM<=10;

PRIME
----------
17
19
23
29
31
37
41
43
47
53

10 rows selected.

Elapsed: 00:00:00.12
```

#4: Using LATERAL views

```WITH y AS (
SELECT 16 fromN
FROM dual
), x (n, flag) AS (
SELECT fromN, is_prime
FROM y, LATERAL (SELECT CASE WHEN COUNT(*)=2 THEN 1 ELSE 0 END is_prime
FROM dual
WHERE MOD(fromN, LEVEL)=0
CONNECT BY LEVEL<=fromN)
UNION ALL
SELECT x.n+1, is_prime
FROM x, LATERAL (SELECT CASE WHEN COUNT(*)=2 THEN 1 ELSE 0 END is_prime
FROM dual
WHERE MOD(x.n+1, LEVEL)=0
CONNECT BY LEVEL<=x.n+1) WHERE x.n>0
)
SELECT n AS prime
FROM x
WHERE flag=1
AND ROWNUM<=10;

PRIME
----------
17
19
23
29
31
37
41
43
47
53

10 rows selected.

Elapsed: 00:00:00.11
```

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