Discussions
Categories
- 196.8K All Categories
- 2.2K Data
- 235 Big Data Appliance
- 1.9K Data Science
- 449.9K Databases
- 221.6K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 549 MySQL Community Space
- 478 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3K ORDS, SODA & JSON in the Database
- 532 SQLcl
- 4K SQL Developer Data Modeler
- 186.9K SQL & PL/SQL
- 21.3K SQL Developer
- 295.5K Development
- 17 Developer Projects
- 138 Programming Languages
- 292.1K Development Tools
- 104 DevOps
- 3.1K QA/Testing
- 645.9K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 154 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.1K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 17 Java Essentials
- 158 Java 8 Questions
- 85.9K Java Programming
- 79 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.2K Java SE
- 13.8K Java Security
- 203 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 401 LiveLabs
- 37 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.6K Other Languages
- 2.3K Chinese
- 171 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 230 Portuguese
Tricky Sql
Answers
-
dd_ram wrote:It must be 11.2 where Oracle introduced recursive subquery factoring.
We are using 11g..So what is that resursive Sql ?
SY. -
Hi,dd_ram wrote: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>?
Thanks Alot Frank...
We are using 11g..
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 -
Thanks Frank!!! I will look into it.
-
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). -
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
-
Thanks Alot Mike. It works great except some rows have @ in it. Is there a way to stop generating those rows??
-
Thanks Mike!!!! I figured out how to eliminate @...Your code works great..We really appreciate your help.
This discussion has been closed.