TABLE functions in Oracle 12.2

Oracle 12.2 introduced a nice little feature – when selecting from a table-function you no longer have to use the TABLE function:

Before 12.2, here is what you had to do:

SELECT *
FROM TABLE([your-collection-or-table-function])

Starting with 12.2 the code becomes shorter:

SELECT *
FROM [your-collection-or-table-function]

Example (Oracle 12.2):

SELECT *
FROM sys.odcinumberlist(5,2,6,3,78)

Result:

COLUMN_VALUE
------------
           5
           2
           6
           3
          78

Note that if you try the last query in Oracle 12.1 or any prior release, you will get the following error:

FROM sys.odcinumberlist(5,2,6,3,78)
                       *
ERROR at line 2:
ORA-00933: SQL command not properly ended

This is an indication that you may need to check your Oracle version:

SELECT *
FROM v$version
BANNER
-----------------------------------------------------------------------------
Oracle Database 11g Express Edition Release 11.2.0.2.0 - Production
PL/SQL Release 11.2.0.2.0 - Production
CORE    11.2.0.2.0      Production
TNS for 32-bit Windows: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production

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

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

 

Solutions to Puzzle of the Week #13

Puzzle of the Week #13:

Table Setup and Puzzle description can be located here

Expected Result:

  ID FULL_NAME                             GROUP_ID
---- ----------------------------------- ----------
   8 Oscar Pedro Fernando Rodriguez               1
   9 Rodriguez, Oscar Pedro Fernando              1
  10 Oscar Fernando Rodriguez Pedro               1
   1 John Smith                                   2
   2 John L. Smith                                2
   4 Smith, John                                  2
   5 Tom Khan                                     3
  11 KHAN, TOM S.                                 3

Solutions:

#1. Using CTE (Recursive WITH) and LISTAGG

WITH x AS (
SELECT name_id, UPPER(REGEXP_REPLACE(full_name,'[[:punct:]]')) full_name
FROM name_list
), y(id, token, lvl) AS (
SELECT name_id, REGEXP_SUBSTR(full_name, '[^ ]+', 1, 1), 1 
FROM x
UNION ALL
SELECT x.name_id, REGEXP_SUBSTR(full_name, '[^ ]+', 1, y.lvl+1), y.lvl+1
FROM x JOIN y ON x.name_id=y.id AND REGEXP_SUBSTR(full_name, '[^ ]+', 1, y.lvl+1) IS NOT NULL
), z AS (
SELECT id, LISTAGG(token, ' ') WITHIN GROUP(ORDER BY token) ordered_name, 
       COUNT(*)OVER(PARTITION BY LISTAGG(token, ' ') WITHIN GROUP(ORDER BY token)) cnt,
       DENSE_RANK()OVER(ORDER BY LISTAGG(token, ' ') WITHIN GROUP(ORDER BY token)) group_id
FROM y
WHERE LENGTH(token)>1
GROUP BY id
)
SELECT z.id, n.full_name, DENSE_RANK()OVER(ORDER BY group_id) group_id
FROM z JOIN name_list n ON z.id=n.name_id
WHERE z.cnt>1
ORDER BY 3, 1;

  ID FULL_NAME                                  GROUP_ID
--- ---------------------------------------- ----------
  8 Oscar Pedro Fernando Rodrigues                    1
  9 Rodrigues, Oscar Pedro Fernando                   1
 10 Oscar Fernando Rodrigues Pedro                    1
  1 John Smith                                        2
  2 John L. Smith                                     2
  4 Smith, John                                       2
  5 Tom Khan                                          3
 11 KHAN, TOM S.                                      3

Explanation:

The key idea is to split each name into multiple name tokens, then sort and merge them back into a single line. Matching (duplicate) names will have the same merged line so we could use it to identify duplicates. DENSE_RANK analytic function is used to generate sequential group id values.

The same idea is used in the solution below. The only difference is the way to split the names into tokens.

#2: Using CONNECT BY and TABLE/CAST/MULTISET functions

 WITH x AS (
SELECT name_id, UPPER(REGEXP_REPLACE(full_name,'[[:punct:]]')) full_name
FROM name_list
), y AS (
SELECT name_id AS id, y.column_value AS token
FROM x,
     TABLE(CAST(MULTISET(SELECT REGEXP_SUBSTR(x.full_name, '[^ ]+', 1, LEVEL) token
                    FROM dual
                    CONNECT BY LEVEL <= LENGTH(full_name)-LENGTH(REPLACE(full_name,' '))+1
                        )
                AS sys.odcivarchar2list)
          ) y
WHERE LENGTH(y.column_value)>1
), z AS (
SELECT id, LISTAGG(token, ' ') WITHIN GROUP(ORDER BY token) ordered_name,
       COUNT(*)OVER(PARTITION BY LISTAGG(token, ' ') WITHIN GROUP(ORDER BY token)) cnt,
       DENSE_RANK()OVER(ORDER BY LISTAGG(token, ' ') WITHIN GROUP(ORDER BY token)) group_id
FROM y
WHERE LENGTH(token)>1
GROUP BY id
)
SELECT z.id, n.full_name, DENSE_RANK()OVER(ORDER BY group_id) group_id
FROM z JOIN name_list n ON z.id=n.name_id
WHERE z.cnt>1
ORDER BY 3, 1;

  ID FULL_NAME                                  GROUP_ID
---- ---------------------------------------- ----------
   8 Oscar Pedro Fernando Rodrigues                    1
   9 Rodrigues, Oscar Pedro Fernando                   1
  10 Oscar Fernando Rodrigues Pedro                    1
   1 John Smith                                        2
   2 John L. Smith                                     2
   4 Smith, John                                       2
   5 Tom Khan                                          3
  11 KHAN, TOM S.                                      3

 

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.

Solutions to Puzzle of the Week #7

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
 7876 ADAMS             358
 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”.