Forum Stats

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

Discussions

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.
  • 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
  • dd_ram
    dd_ram Member Posts: 224
    Thanks Frank!!! I will look into it.
  • 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).
  • 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
     
  • 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??
  • dd_ram
    dd_ram 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.