Forum Stats

  • 3,817,244 Users
  • 2,259,294 Discussions
  • 7,893,711 Comments

Discussions

Categories

Tricky Sql

2»

Answers

  • Solomon Yakobson
    Solomon Yakobson 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.
    · Share on TwitterShare on Facebook
  • Frank Kulash
    Frank Kulash 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:
    http://download.oracle.com/docs/cd/E11882_01/server.112/e17118/statements_10002.htm#sthref6760
    · Share on TwitterShare on Facebook
  • dd_ram
    dd_ram Member Posts: 224
    Thanks Frank!!! I will look into it.
    · Share on TwitterShare on Facebook
  • MichaelS
    MichaelS 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).
    · Share on TwitterShare on Facebook
  • odie_63
    odie_63 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
    · Share on TwitterShare on Facebook
  • dd_ram
    dd_ram Member Posts: 224
    Thanks Alot Mike. It works great except some rows have @ in it. Is there a way to stop generating those rows??
    · Share on TwitterShare on Facebook
  • dd_ram
    dd_ram Member Posts: 224
    Thanks Mike!!!! I figured out how to eliminate @...Your code works great..We really appreciate your help.
    · Share on TwitterShare on Facebook
This discussion has been closed.