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

 

Advertisements

Integer to Binary Conversion in Oracle SQL

Interestingly enough, Oracle does not have a built-in function to convert Decimal numbers (i.e. integers) into Binary. This post offers an elegant way of doing so.

The following script is intended to be executed in SQL*Plus, so it uses some SQL*Plus commands:

column bin format a40
undefine N
SELECT LISTAGG(SIGN(BITAND(&&N, POWER(2,LEVEL-1))),'') 
       WITHIN GROUP(ORDER BY LEVEL DESC) bin
FROM dual
CONNECT BY POWER(2, LEVEL-1)<=&&N;

Result (for N=400):

BIN
-------------
110010000

Result (for N=1401):

BIN
------------
10101111001

Explanation:

How many digits may the resulting binary string have? The answer comes from Math: not more than LOG(2, N) + 1. Let’s first generate a numeric range from 1 to LOG(2,N)+1:

SELECT LEVEL
FROM dual
CONNECT BY LEVEL<=LOG(2,&N)+1

Result (for N=1401):

 LEVEL
------
     1
     2
     3
     4
     5
     6
     7
     8
     9
    10
    11

Alternatively, we can use mathematically equivalent condition in the CONNECT BY clause using POWER instead of LOG function:

SELECT LEVEL
FROM dual
CONNECT BY POWER(2,LEVEL)<=&N*2

or

SELECT LEVEL
FROM dual
CONNECT BY POWER(2,LEVEL-1)<=&N

Now, we will check every bit of the desired result (i.e. binary representation of N) by using BITAND function:

SELECT LEVEL, BITAND(&&N, POWER(2,LEVEL-1)) bit
FROM dual
CONNECT BY POWER(2,LEVEL-1)<=&&N

Result (for N=12):

LEVEL        BIT
----- ----------
    1          0
    2          0
    3          4
    4          8

Positive values in the bit column refer to a bit 1 in the corresponding position (in reverse order) of the binary value. It’s easy to turn those values to 1 by using SIGN function:

SELECT LEVEL, SIGN(BITAND(&&N, POWER(2,LEVEL-1))) bit
FROM dual
CONNECT BY POWER(2,LEVEL-1)<=&&N

Result (for N=12):

LEVEL        BIT
----- ----------
    1          0
    2          0
    3          1
    4          1

Here, we can see that we need to concatenate the values in the bit column in reverse order. This is very easy to do using LISTAGG function:

SELECT LISTAGG(SIGN(BITAND(&&N, POWER(2,LEVEL-1))),'') 
       WITHIN GROUP(ORDER BY LEVEL DESC) bin
FROM dual
CONNECT BY POWER(2,LEVEL-1)<=&&N

Result (for N=12):

BIN
----------
1100

Note that we sorted all the rows in descending order of the LEVEL to obtain the correct order of bits.

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.

How to Simulate SIGN Function

Puzzle of the day:

How to simulate the SIGN function in Oracle SQL by only using CEIL, FLOOR, and ABS Oracle SQL functions along with arithmetic operators? No PL/SQL.

Solution:

SIGN(x)=CEIL(x/(1+ABS(x)))+FLOOR(x/(1+ABS(x)))

In SQL, we can demonstrate it as follows:

WITH r AS (
SELECT dbms_random.VALUE(-999,999) rnd
FROM dual
CONNECT BY LEVEL<=10
UNION ALL
SELECT 0
FROM dual
)
SELECT rnd, SIGN(rnd), CEIL(rnd/(1+ABS(rnd)))+FLOOR(rnd/(1+ABS(rnd))) "MySign"
FROM r

Result:

       RND  SIGN(RND)     MySign
---------- ---------- ----------
  -519.606         -1         -1
-657.62692         -1         -1
414.625079          1          1
736.175183          1          1
268.689074          1          1
-647.12649         -1         -1
338.192233          1          1
784.780876          1          1
-529.69184         -1         -1
-596.56803         -1         -1
         0          0          0

As you can see, “MySign” column perfectly matches SIGN column.

Comment:

WITH clause is needed to generate 10 random values in the range of -999 .. +999. “0” value is added to demonstrate a special case as it is unlikely that zero will be randomly generated.

 

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.

9 Solutions to Puzzle of the Week #21

Puzzle of the Week #21:

Produce a report that shows employee name, his/her immediate manager name, and the next level manager name. The following conditions should be met:

  • Use Single SELECT statement only
  • Use mgr column to identify employee’s immediate manager
  • The query should work in Oracle 11g.
  • A preferred solution should use only a single instance of emp table.

Expected Result:

NAME1      NAME2      NAME3
---------- ---------- ------
SMITH      FORD       JONES
ALLEN      BLAKE      KING
WARD       BLAKE      KING
JONES      KING
MARTIN     BLAKE      KING
BLAKE      KING
CLARK      KING
SCOTT      JONES      KING
KING
TURNER     BLAKE      KING
ADAMS      SCOTT      JONES
JAMES      BLAKE      KING
FORD       JONES      KING
MILLER     CLARK      KING

Solutions:

#1. Using connect_by_root, sys_connect_by_path, and regexp_substr functions

col name1 for a10
col name2 for a10
col name3 for a10
WITH x AS(
SELECT CONNECT_BY_ROOT(ename) name,
       SYS_CONNECT_BY_PATH(ename, ',') path,
       CONNECT_BY_ROOT(empno) empno
FROM emp
WHERE LEVEL<=3
CONNECT BY empno=PRIOR mgr
)
SELECT name, REGEXP_SUBSTR(MAX(path), '[^,]+', 1, 2) name2,
             REGEXP_SUBSTR(MAX(path), '[^,]+', 1, 3) name3
FROM x
GROUP BY name, empno
ORDER BY empno;

#2. Using CONNECT BY twice

WITH x AS (
SELECT ename, PRIOR ename mname, empno, mgr
FROM emp
WHERE LEVEL=2 OR mgr IS NULL
CONNECT BY PRIOR empno=mgr
)
SELECT ename name1, mname name2, MAX(PRIOR mname) name3
FROM x
WHERE LEVEL<=2
CONNECT BY PRIOR empno=mgr
GROUP BY ename, mname, empno
ORDER BY empno

#3. Using CONNECT BY and Self Outer Join

WITH x AS (
SELECT ename, PRIOR ename mname, PRIOR mgr AS mgr, empno
FROM emp
WHERE LEVEL=2 OR mgr IS NULL
CONNECT BY PRIOR empno=mgr
)
SELECT x.ename name1, x.mname name2, e.ename name3
FROM x LEFT JOIN emp e ON x.mgr=e.empno
ORDER BY x.empno

#4. Using 2 Self Outer Joins

SELECT a.ename name1, b.ename name2, c.ename name3
FROM emp a LEFT JOIN emp b ON a.mgr=b.empno
           LEFT JOIN emp c ON b.mgr=c.empno
ORDER BY a.empno

#5. Using CONNECT BY and PIVOT

SELECT name1, name2, name3
FROM (
SELECT ename, LEVEL lvl, CONNECT_BY_ROOT(empno) empno
FROM emp
WHERE LEVEL<=3
CONNECT BY empno=PRIOR mgr
)
PIVOT(
MAX(ename)
FOR lvl IN (1 AS name1, 2 AS name2, 3 AS name3)
)
ORDER BY empno;

#6. PIVOT Simulation

WITH x AS (
SELECT ename, LEVEL lvl, CONNECT_BY_ROOT(empno) empno
FROM emp
WHERE LEVEL<=3
CONNECT BY empno=PRIOR mgr
)
SELECT MAX(DECODE(lvl, 1, ename)) name1,
       MAX(DECODE(lvl, 2, ename)) name2,
       MAX(DECODE(lvl, 3, ename)) name3
FROM x
GROUP BY empno
ORDER BY empno;

#7. Using CONNECT BY and no WITH/Subqueries (Credit to Krishna Jamal)

SELECT ename Name1, PRIOR ename Name2,
DECODE(LEVEL, 
    3, CONNECT_BY_ROOT(ename), 
    4, TRIM(BOTH ' ' FROM 
        REPLACE(
            REPLACE(SYS_CONNECT_BY_PATH(PRIOR ename, ' '), PRIOR ename), 
        CONNECT_BY_ROOT(ename)))
        ) Name3
FROM emp
START WITH mgr IS NULL
CONNECT BY PRIOR empno = mgr
ORDER BY empno;

#8. A composition of Methods 1 and 7:

SELECT ename Name1, PRIOR ename Name2,
       CASE WHEN LEVEL IN (3,4) 
          THEN REGEXP_SUBSTR(SYS_CONNECT_BY_PATH(ename, ','),'[^,]+',1,LEVEL-2) 
       END AS Name3
FROM emp
START WITH mgr IS NULL
CONNECT BY PRIOR empno = mgr
ORDER BY empno;

#9. Using NTH_VALUE Analytic function (Oracle 11.2 and up):

WITH x AS (
SELECT CONNECT_BY_ROOT(ename) n1, CONNECT_BY_ROOT(empno) empno,
 NTH_VALUE(ename, 2) OVER(PARTITION BY CONNECT_BY_ROOT(ename) ORDER BY LEVEL) n2,
 NTH_VALUE(ename, 3) OVER(PARTITION BY CONNECT_BY_ROOT(ename) ORDER BY LEVEL) n3
FROM emp
WHERE LEVEL<=3
CONNECT BY empno=PRIOR mgr
)
SELECT n1 name1, MAX(n2) name2, MAX(n3) name3
FROM x
GROUP BY n1, empno
ORDER BY empno

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.

Interview Question: Count number of every week day in a year

Interview Question: With a single SELECT statement get the number of each week day in the current year.

Level: Intermediate

Expected Result:

Day                                  Days in Year
------------------------------------ ------------
SUNDAY                                         52
MONDAY                                         52
TUESDAY                                        52
WEDNESDAY                                      52
THURSDAY                                       52
FRIDAY                                         53
SATURDAY                                       53

Solution #1:

WITH x AS (
SELECT LEVEL-1+TRUNC(SYSDATE, 'YYYY') AS dd
FROM dual
CONNECT BY TRUNC(LEVEL-1+TRUNC(SYSDATE, 'YYYY'),'YYYY')=TRUNC(SYSDATE, 'YYYY')
)
SELECT TO_CHAR(dd, 'DAY') "Day", COUNT(*) "Days in Year"
FROM x
GROUP BY TO_CHAR(dd, 'DAY'), TO_CHAR(dd, 'D')
ORDER BY TO_CHAR(dd, 'D');

Explanation:

The WITH clause returns all days in the current year, this is a common trick used in majority of sql puzzle related to a calendar. The connect by query used in the WITH generated a date range which starts on TRUNC(SYSDATE, ‘YYYY’) – i.e. the 1st day of the year – and continues as long as the next day falls into the same year (see condition in the CONNECT BY clause). The main query groups by day name – TO_CHAR(dd, ‘DAY’) – and sorts by day number (in a week) – TO_CHAR(dd, ‘D’).

Solution #2:

WITH x AS (
SELECT TO_CHAR(ADD_MONTHS(TRUNC(SYSDATE,'YYYY'), 12)-1, 'DDD') days_in_year
FROM dual
)
SELECT TO_CHAR(LEVEL-1+TRUNC(SYSDATE, 'YYYY'),'DAY') "Day",
       CASE WHEN MOD(days_in_year,52)>=LEVEL THEN 53
            ELSE 52
       END "Days in Year"
FROM x
CONNECT BY LEVEL<=7
ORDER BY TO_CHAR(LEVEL-1+TRUNC(SYSDATE, 'YYYY'),'D');

Explanation:

The idea behind this solution is totally different than in the 1st one. A year has 52 weeks and 1 or 2 days depending on whether it is a  leap year or not. So each day of the week happens 52 times a year + first one or two days of the year make corresponding week days have 53 days in that same year. If we know the number of days in a year (365 or 366) we can find out which days of the week will happen 53 times. For that matter we can take MOD(days_in_year, 52) expression that will return either 1 or 2. If the day order number within a year is 1 (or 2 for the leap year) we know that the corresponding week day will occur 53 times, otherwise – 52.

The WITH clause returns number of days in the current year. We get that by taking the 1st day of the current year: TRUNC(SYSDATE,’YYYY’), adding 12 months to it and subtract 1 day to get the last day of the current year. Taking TO_CHAR(…, ‘DDD’) – gives us the order number of that day in the year which is exactly the number of days in the current year.

The main query generates the date range from Jan-1 to Jan-7 in the current year, and assigns 52 or 53 to the 2nd column based on the logic described above.

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.

 

 

Three Solutions to Puzzle of the Week #18

Puzzle of the Week #18:

There are gaps in values of empno column in emp table. The challenge is to find all the gaps within the range of employee numbers already in use. All numbers should be grouped in ranges (see expected result section below). A single SELECT statement against emp table is expected.

Expected Result:

Avail. Emp Numbers
------------------
7370 - 7498
7500 - 7520
7522 - 7565
7567 - 7653
7655 - 7697
7699 - 7781
7783 - 7787
7789 - 7838
7840 - 7843
7845 - 7875
7877 - 7899
7901 - 7901
7903 - 7933

Solutions

#1: Using GROUP BY Over ROWNUM expression

WITH x AS (
SELECT MIN(empno) min_no, MAX(empno) max_no
FROM emp
), y AS (
SELECT min_no+LEVEL-1 empno
FROM x
CONNECT BY min_no+LEVEL-1<=max_no
MINUS
SELECT empno
FROM emp
)
SELECT MIN(empno) || ' - ' || MAX(empno) "Avail. Emp Numbers"
FROM y
GROUP BY empno-ROWNUM
ORDER BY empno-ROWNUM;

Avail. Emp Numbers
--------------------
7370 - 7498
7500 - 7520
7522 - 7565
7567 - 7653
7655 - 7697
7699 - 7781
7783 - 7787
7789 - 7838
7840 - 7843
7845 - 7875
7877 - 7899
7901 - 7901
7903 - 7933

#2: Using MATCH_RECOGNIZE (Oracle 12c and up;  credit to Zohar Elkayam)

WITH x AS (
SELECT MIN(empno) min_no, MAX(empno) max_no
FROM emp
), y AS (
SELECT min_no+LEVEL-1 empno
FROM x
CONNECT BY min_no+LEVEL-1<=max_no
MINUS
SELECT empno
FROM emp
)
SELECT firstemp || ' - ' || lastemp "Avail. Emp Numbers"
FROM y
MATCH_RECOGNIZE (
  ORDER BY empno
  MEASURES
   A.empno firstemp,
   LAST(empno) lastemp
  ONE ROW PER MATCH
  AFTER MATCH SKIP PAST LAST ROW
  PATTERN (A B*)
  DEFINE B AS empno = PREV(empno)+1
);

#3: Using LEAD Analytic function (credit to Krishna Jamal)

WITH x AS
(
SELECT empno, LEAD(empno,1) OVER(ORDER BY empno) lead_empno
FROM emp
)
SELECT (empno+1) || ' - ' || (lead_empno-1) "Avail. Emp Numbers"
FROM x
WHERE empno+1!=lead_empno;

Also, see a very similar Puzzle of the Week #15 for more workarounds.

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.

Three Solutions to Puzzle of the Week #16

Puzzle of the Week #16:

With a single SELECT statement find the biggest prime factor of a given integer value (N).

Expected Result:

--For N=100:

Biggest Prime Factor
--------------------
                  5

--For N=52:

Biggest Prime Factor
--------------------
                 13

--For N=21:

Biggest Prime Factor
--------------------
                   7

Solutions

#1: Using CTE (recursive WITH)

WITH input AS (
SELECT &N n
FROM dual
), x(num, flag) AS (
SELECT 2, CASE WHEN MOD(n, 2)=0 THEN 1 ELSE 0 END AS flag
FROM input
UNION ALL
SELECT x.num+1, CASE WHEN MOD(i.n, x.num+1)=0 THEN 1 ELSE 0 END
FROM input i, x
WHERE x.num+1<=i.n
), y AS (
SELECT num, (SELECT CASE WHEN COUNT(*)=2 THEN 1 ELSE 0 END
             FROM dual
             WHERE MOD(x.num,LEVEL)=0
             CONNECT BY LEVEL<=x.num) is_prime
FROM x
WHERE flag=1
)
SELECT MAX(num) "Biggest Prime Factor"
FROM y
WHERE is_prime=1;

Enter value for n: 100
old   2: SELECT &N n
new   2: SELECT 100 n

Biggest Prime Factor
--------------------
                   5

SQL> /
Enter value for n: 52
old   2: SELECT &N n
new   2: SELECT 52 n

Biggest Prime Factor
--------------------
                  13

SQL> /
Enter value for n: 21
old   2: SELECT &N n
new   2: SELECT 21 n

Biggest Prime Factor
--------------------
                   7


#2: Using CONNECT BY clause , version 1

WITH input AS (
SELECT &N n
FROM dual
), x AS (
SELECT LEVEL num
FROM input i
WHERE MOD(i.N, LEVEL)=0
CONNECT BY LEVEL<=i.N
), y AS (
SELECT num, (SELECT CASE WHEN COUNT(*)=2 THEN 1 ELSE 0 END
             FROM dual
             WHERE MOD(x.num,LEVEL)=0
             CONNECT BY LEVEL<=x.num) is_prime
FROM x
WHERE flag=1
)
SELECT MAX(num) "Biggest Prime Factor"
FROM y
WHERE is_prime=1;

#3: Using CONNECT BY clause, version 2

WITH input AS (
SELECT &N n
FROM dual
), range AS (
SELECT LEVEL num
FROM input i
CONNECT BY LEVEL <= i.N
), x AS(
SELECT r1.num
FROM range r1, range r2, input i
WHERE MOD(i.N, r1.num)=0
GROUP BY r1.num
HAVING COUNT(CASE WHEN MOD(r1.num, r2.num)=0 THEN 1 END)=2
)
SELECT MAX(num) "Biggest Prime Factor"
FROM x;

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.

Three Solutions to Puzzle of the Week #15

Puzzle of the Week #15:

Find all the year based intervals from 1975 up to now when the company did not hire employees. Use a single SELECT statement against emp table.

Expected Result:

years
------------
1975 - 1979
1983 - 1986
1988 - 2016

Solutions

#1: Grouping by an expression on ROWNUM (no Analytic functions!)

SQL> col years for a15

SQL> WITH x AS (
  2  SELECT 1975+LEVEL-1 yr
  3  FROM dual
  4  CONNECT BY 1975+LEVEL-1<=EXTRACT(YEAR FROM SYSDATE)
  5  MINUS
  6  SELECT EXTRACT(YEAR FROM hiredate)
  7  FROM emp
  8  )
  9  SELECT MIN(yr) || ' - ' || MAX(yr) "years"
 10  FROM x
 11  GROUP BY yr-ROWNUM
 12  ORDER BY yr-ROWNUM;

years
---------------
1975 - 1979
1983 - 1986
1988 - 2016

#2: Calculating steps with Analytic function and grouping by a sum of step.

WITH x AS (
SELECT 1975+LEVEL-1 yr
FROM dual
CONNECT BY 1975+LEVEL-1<=EXTRACT(YEAR FROM SYSDATE)
MINUS
SELECT EXTRACT(YEAR FROM hiredate)
FROM emp
), y AS (
SELECT DECODE(yr, LAG(yr,1)OVER(ORDER BY yr)+1, 0, 1) AS step, yr
FROM x
), z AS (
SELECT yr, SUM(step)OVER(ORDER BY yr) grp
FROM y
)
SELECT MIN(yr) || ' - ' || MAX(yr) "years"
FROM z
GROUP BY grp
ORDER BY grp;

years
---------------
1975 - 1979
1983 - 1986
1988 - 2016

#3: Using Self Outer Join to calculate steps

WITH x AS (
SELECT 1975+LEVEL-1 yr
FROM dual
CONNECT BY 1975+LEVEL-1<=EXTRACT(YEAR FROM SYSDATE)
MINUS
SELECT EXTRACT(YEAR FROM hiredate)
FROM emp
), y AS (
SELECT x1.yr, NVL2(x2.yr, 0, 1) step
FROM x x1 LEFT JOIN x x2 ON x1.yr=x2.yr+1
), z AS (
SELECT yr, SUM(step)OVER(ORDER BY yr) grp
FROM y
)
SELECT MIN(yr) || ' - ' || MAX(yr) "years"
FROM z
GROUP BY grp
ORDER BY grp;

years
---------------
1975 - 1979
1983 - 1986
1988 - 2016

 

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.

4 Solutions to Puzzle of the Week #12

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.

3 Solutions to Puzzle of the Week #10: Fibonacci

Puzzle of the Week #10: Fibonacci

With a single SELECT statement calculate first 20 Fibonacci numbers without using Binet’s formula.

Expected Result:

   N     Fib(n)
---- ----------
   1          1
   2          1
   3          2
   4          3
   5          5
   6          8
   7         13
   8         21
   9         34
  10         55
  11         89
  12        144
  13        233
  14        377
  15        610
  16        987
  17       1597
  18       2584
  19       4181
  20       6765

Solutions:

#1. Oracle 10g solution (using MODEL clause):

SELECT n, f AS "Fib(n)"
FROM dual
MODEL
  DIMENSION BY (0 d)
  MEASURES (0 n, 0 f)
  RULES ITERATE(20) (
     f[iteration_number]=DECODE(iteration_number, 0,1, 1,1,
                                f[iteration_number-2]+f[iteration_number-1]),
     n[iteration_number]=iteration_number+1
  );

  N     Fib(n)
--- ----------
  1          1
  2          1
  3          2
  4          3
  5          5
  6          8
  7         13
  8         21
  9         34
 10         55
 11         89
 12        144
 13        233
 14        377
 15        610
 16        987
 17       1597
 18       2584
 19       4181
 20       6765

#2. Oracle 11.2g solution (using Recursive WITH clause):

WITH x(n, f1, f2) AS (
SELECT 1, 1, 1
FROM dual
UNION ALL
SELECT n+1, f2, f1+f2
FROM x
WHERE n<20
)
SELECT n, f1 AS “Fib(n)”
FROM x

#3. Oracle 12c solution (using WITH for PL/SQL function):

WITH
  FUNCTION fib(n INTEGER) RETURN NUMBER DETERMINISTIC
  AS
  BEGIN
     RETURN CASE WHEN n IN (1,2) THEN 1
                 ELSE fib(n-2)+fib(n-1)
            END;
  END;
SELECT LEVEL n, fib(LEVEL) AS "Fib(n)"
FROM dual
CONNECT BY LEVEL<=20

Suggested further reading:

For more tricks and cool techniques check my book “Oracle SQL Tricks and Workarounds” for instructions. The book is also available on Amazon and in all major book stores.

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/