## Forum Stats

• 3,817,244 Users
• 2,259,294 Discussions

Discussions

# Tricky Sql

2»

• Member Posts: 19,335 Red Diamond
dd_ram wrote:
We are using 11g..So what is that resursive Sql ?
It must be 11.2 where Oracle introduced recursive subquery factoring.

SY.
• Member, Moderator Posts: 41,930 Red Diamond
edited Aug 2, 2011 4:23PM
Hi,
dd_ram wrote:
Thanks Alot Frank...
We are using 11g..
There is no Oracle 11<b>f</b> or 11<b>h</b>, so what's the point of saying you're using 11<b>g</b>?
There is, however, a version called 11<b>.1</b>, which is different from 11<b>.2</b>. In this case, that difference is very important. Recursive WITH clauses don't work in Oracle 11<b>.1</b>, only in 11<b>.2</b>.
So what is that resursive Sql ?
Recursion is a 2-part definition of a set or a fucntion, consisting of
(a) an End Condition , and
(b) a Recursive Condition that references the set or function being defined.
For example, the definition of an Erdos number is:
(a) if you're Paul Erdos, your Erdos number is 0 (end condition)
(b) otherwise, your Erdos number is 1 plus the lowest Erdos number of anyone with whom you've ever co-authored a paper. (recursive condition; it includes "Erdos number" itself in the definition)
It was said that, during Erdos's lifetime, no living mathematician in the world had an Erdos number greater than 7.

Oracle has supported recursive fucntions (fucntions that call themselves) as long as it had user-defined functions, and CONNECT BY queries (available since Oracle version 2) are a recursive feature, so recursion is not new in Oracle SQL. I was talking about a new feature in Oracle 11.2, where you can define a result set as the UNION of two queries:
(a) a normal query, based on any existing table or view (end-condition) and
(b) a query that is based on the result set being defined (recursive condition)

For specifics, look up "Recursive subquery factoring" in the Oracle 11.2 SQL Language manual:
• Member Posts: 224
Thanks Frank!!! I will look into it.
• Member Posts: 8,424 Bronze Crown
what is that resursive Sql ?
As a start you may try sth like
```with t as (
select chr(63 + level) l from dual connect by level <= 27
),
t2 (a) as (
select cast(l as varchar2(4)) from t union all
select l || a from t, t2 where length (l || a) <= 4
)
select 'STL12345' || decode(rownum,1,null,a) a from t2 order by rownum
/
A
-----------------
STL12345
STL12345A
STL12345B
STL12345C
STL12345D
STL12345E
STL12345F
STL12345G
STL12345H
STL12345I
STL12345J
....```
It'll work only until STL12345ZZZZ but of course it is easily adaptable to go as large as you want (just change the number 4).
• Member Posts: 8,466 Silver Trophy
edited Aug 2, 2011 5:59PM
In addition, an XQuery solution :
```SQL> var target number
SQL> exec :target := 1500;

PL/SQL procedure successfully completed

SQL>
SQL> select *
2  from xmltable(
3  'declare function local:base26(\$n as xs:integer, \$a as xs:string) as xs:string
4   {
5    let \$q := (\$n - 1) idiv 26
6    let \$r := (\$n - 1) mod 26
7    let \$c := concat(codepoints-to-string(\$r + 65), \$a)
8    return if (\$q = 0) then \$c else local:base26(\$q, \$c)
9   }; (::)
10   for \$i in 1 to xs:integer(\$maxnum)
11   return local:base26(\$i, "")'
12    passing :target as "maxnum"
13    columns val varchar2(30) path '.'
14  )
15  ;

VAL
------------------------------
A
B
C
D
E
F
G
H
I
J
K
L
M
N
O
P
Q
R
S
T

VAL
------------------------------
U
V
W
X
Y
Z
AA
AB
AC

...

BDX
BDY
BDZ
BEA
BEB
BEC
BED
BEE
BEF
BEG
BEH

VAL
------------------------------
BEI
BEJ
BEK
BEL
BEM
BEN
BEO
BEP
BEQ
BER

1500 rows selected
```
• Member Posts: 224
Thanks Alot Mike. It works great except some rows have @ in it. Is there a way to stop generating those rows??
• Member Posts: 224
Thanks Mike!!!! I figured out how to eliminate @...Your code works great..We really appreciate your help.
This discussion has been closed.