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

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/

## Puzzle of the Week #7

For every employee find the sum of ASCII codes of all the characters in their names. Write a single SELECT statement only.

Expected Result:

EMPNO ENAME       SUM_ASCII
----- ---------- ----------
7788 SCOTT             397
7566 JONES             383
7499 ALLEN             364
7521 WARD              302
7934 MILLER            453
7902 FORD              299
7369 SMITH             389
7844 TURNER            480
7698 BLAKE             351
7782 CLARK             365
7654 MARTIN            459
7839 KING              297
7900 JAMES             368

## Solutions:

Solution/Workaround #1: Oracle 12c and up Only (submitted by Zohar Elkayam)

WITH
FUNCTION sumascii(p_str in varchar2)  RETURN NUMBER
IS
x NUMBER:= 0;
BEGIN
FOR i IN 1..LENGTH(p_str) LOOP
x := x + ASCII(SUBSTR(p_str, i, 1)) ;
END LOOP;
RETURN x;
END;
SELECT empno, ename, sumascii(ename) AS sum_ascii
FROM emp
/

Variation of Solution #1 (Recursive function):

WITH
FUNCTION sumascii(p_str in varchar2)  RETURN NUMBER
IS
BEGIN
IF p_str IS NULL THEN
RETURN 0;
END IF;
RETURN ASCII(p_str) + sumascii(SUBSTR(p_str,2));
END;
SELECT empno, ename, sumascii(ename) AS sum_ascii
FROM emp
/

Solution/Workaround #2: Cartesian Product with Generated Numeric Range (by Zohar Elkayam)

SELECT empno, ename, SUM(ASCII(ename_char)) sum_ascii
FROM (SELECT empno, ename, SUBSTR(ename, i, 1) ename_char
FROM emp, (SELECT LEVEL i
FROM dual
CONNECT BY LEVEL<=(SELECT MAX(LENGTH(ename))                                      FROM emp)                  )       WHERE LENGTH(ename)>=i
)
GROUP BY empno, ename
/

Simplified variation of Workaround #2:

SELECT empno, ename,
SUM(ASCII(SUBSTR(ename, i, 1))) sum_ascii
FROM emp, (SELECT LEVEL i
FROM dual
CONNECT BY LEVEL<=(SELECT MAX(LENGTH(ename))                                FROM emp)            )  WHERE LENGTH(ename)>=i
GROUP BY empno, ename
/

Solution/Workaround #3: In-Line Scalar Subquery

SELECT empno, ename,
(SELECT SUM(ASCII(SUBSTR(a.ename, LEVEL, 1)))
FROM dual
CONNECT BY LEVEL<=LENGTH(a.ename)) AS sum_ascii
FROM emp a
/

Solution #4/Workaround : Recursive WITH clause

WITH x(n, empno, ename, letter) AS (
SELECT 1 AS n, empno, ename, SUBSTR(ename, 1, 1)
FROM emp
UNION ALL
SELECT x.n+1, empno, ename, SUBSTR(ename, n+1, 1)
FROM x
WHERE LENGTH(ename)>=n+1
)
SELECT empno, ename, SUM(ASCII(letter)) sum_ascii
FROM x
GROUP BY empno, ename
/

Solution/Workaround #5: Use DUMP function and Regular Expressions (submitted by Sunitha)

SELECT empno, ename, SUM(REGEXP_SUBSTR(nm, '\d+', 1, occ)) AS sum_ascii
FROM (SELECT empno, ename, REGEXP_REPLACE(DUMP(ename), '.*: (\d.*)\$', '\1') nm
FROM emp),
(SELECT LEVEL occ FROM dual CONNECT BY LEVEL <=ANY(SELECT LENGTH(ename) FROM emp))
GROUP BY empno, ename
/

Solution/Workaround #6: Use LATERAL View (Oracle 12c and up)

SELECT empno, ename, sum_ascii
FROM emp e, LATERAL (SELECT SUM(ASCII(SUBSTR(e.ename,LEVEL,1)) ) sum_ascii
FROM dual
CONNECT BY LEVEL<=LENGTH(e.ename) ) x

Solution/Workaround #7: Use TABLE/CAST/MULTISET function composition

SELECT empno, ename, x.column_value AS sum_ascii
FROM emp e,
TABLE(CAST(MULTISET(SELECT SUM(ASCII(SUBSTR(e.ename,LEVEL,1)) ) sum_ascii
FROM dual
CONNECT BY LEVEL<=LENGTH(e.ename)
) AS sys.odcinumberlist
)
) 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”.