Need a unique gift idea?
A Pastebin account makes a great Christmas gift
SHARE
TWEET

Fibonacci series with 12c recursive subquery factoring

ExaGridDba Apr 25th, 2017 (edited) 36 Never
Upgrade to PRO!
ENDING IN00days00hours00mins00secs
 
  1. SQL*Plus: Release 12.1.0.2.0 Production on Wed Apr 26 00:04:38 2017
  2.  
  3. Copyright (c) 1982, 2014, Oracle.  All rights reserved.
  4.  
  5.  
  6. Connected to:
  7. Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
  8. With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
  9. and Real Application Testing options
  10.  
  11. SQL> with fib(e,f) as (select 1, 1 from dual union all select e+f,e from fib where e <= 55) select f from fib;
  12.  
  13.          F
  14. ----------
  15.          1
  16.          1
  17.          2
  18.          3
  19.          5
  20.          8
  21.         13
  22.         21
  23.         34
  24.         55
  25.  
  26. 10 rows selected.
  27.  
  28. Here are a few interesting error messages relating to 12c recursive subquery factoring:
  29.  
  30. SQL> with tmp as (select 1 id from dual union all select id + 1 from tmp where tmp.id + 1 <= 10) select id from tmp;
  31. with tmp as (select 1 id from dual union all select id + 1 from tmp where tmp.id + 1 <= 10) select id from tmp
  32.                                                                 *
  33. ERROR at line 1:
  34. ORA-32039: recursive WITH clause must have column alias list
  35.  
  36. SQL> with tmp(id) as (select 1 id, 1 l from dual union all select id + 1, id from tmp where tmp.id < 10) select id from tmp;
  37. with tmp(id) as (select 1 id, 1 l from dual union all select id + 1, id from tmp where tmp.id < 10) select id from tmp
  38.                                                                              *
  39. ERROR at line 1:
  40. ORA-32038: number of WITH clause column names does not match number of elements in select list
  41.  
  42.  
  43. SQL> with fib(f,g) as (select 1 f, 1 g from dual union select f+g,f from fib where f < 55) select f,g from fib;
  44. with fib(f,g) as (select 1 f, 1 g from dual union select f+g,f from fib where f < 55) select f,g from fib
  45.                                                                     *
  46. ERROR at line 1:
  47. ORA-32040: recursive WITH clause must use a UNION ALL operation
  48.  
  49.  
  50.  
  51. SQL> with fib(f,g) as (select 1 f, 0 g from dual union all select f+g,f from fib where f < 55) select f,g from fib;
  52. ERROR:
  53. ORA-32044: cycle detected while executing recursive WITH query
  54.  
  55.  
  56.  
  57. no rows selected
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top