Discussions
Categories
- 197.1K All Categories
- 2.5K Data
- 546 Big Data Appliance
- 1.9K Data Science
- 450.7K Databases
- 221.9K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 552 MySQL Community Space
- 479 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3.1K ORDS, SODA & JSON in the Database
- 555 SQLcl
- 4K SQL Developer Data Modeler
- 187.2K SQL & PL/SQL
- 21.3K SQL Developer
- 296.3K Development
- 17 Developer Projects
- 139 Programming Languages
- 293K Development Tools
- 110 DevOps
- 3.1K QA/Testing
- 646.1K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 158 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.2K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 19 Java Essentials
- 162 Java 8 Questions
- 86K Java Programming
- 81 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 204 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 466 LiveLabs
- 39 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 175 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 233 Portuguese
Multi-Rows from DUAL
Comments
-
This article shows both the MODEL and DUAL examples, but for speed, a pipelined function is pretty good too...
-
I personally use:
SQL> ed Wrote file afiedt.buf 1 SELECT rownum 2 FROM Dual 3* CONNECT BY rownum <= 10 SQL> / ROWNUM ---------- 1 2 3 4 5 6 7 8 9 10 10 rows selected. SQL>
I know people go on about it not being documented by Oracle etc. but I know that in 10.1.x versions of Oracle it had a bug where it returned 1 extra row (the above query would have given 11 rows), but in 10.2.x versions Oracle have fixed this so it now gives the correct result. I would assume that if they've gone to the trouble of fixing it, then it's something they intend to be there. -
I personally choose to believe the documentation when it says that connect by MUST be followed by PRIOR, although it's really a personal choice whether you choose to believe the documentation or the software as to what is the "correct" behaviour. There's many examples either way, I am sure.
I am more comfortable with the MODEL version since it's a documented, supported way to "create" rows which aren't present in the database.
cheers,
Anthony -
Hi,
Here one more method which can be used.SQL> ed
Regards
Wrote file afiedt.buf
1 SELECT rownum FROM (
2* SELECT 1 FROM DUAL GROUP BY CUBE(1,2))
SQL> /
ROWNUM
----------
1
2
3
4
SQL> ed
Wrote file afiedt.buf
1 SELECT rownum FROM (
2* SELECT 1 FROM DUAL GROUP BY CUBE(1,2,3))
SQL> /
ROWNUM
----------
1
2
3
4
5
6
7
8
8 rows selected.
SQL>
SQL> ed
Wrote file afiedt.buf
1 SELECT rownum FROM (
2* SELECT 1 FROM DUAL GROUP BY CUBE(1,2,3,4))
SQL> /
ROWNUM
----------
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
16 rows selected.
SQL> -
I personally choose to believe the documentation whennice link! thanks for sharing this ;-)
it says that
http://pages.citebite.com/e1k4e8r7q6wuaMUST be followed by PRIORwell, this is the way to define the hierarchy. If you do not define a hierarchy, than you have a loop. Even if one may pretend in some versions of Oracle you can you level or rownum to make the loop non-infinite, it is still a loop and it should generate an ORA-01436: CONNECT BY loop in user data -
using CUBE is the slowest method I know.
If you need 5000 records you have to write
SELECT rownum FROM (
SELECT 1 FROM DUAL GROUP BY CUBE(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1))
WHERE ROWNUM <= 5000
Execution time : 250 seconds
SELECT Level LVL
FROM Dual
CONNECT BY Level <= 5000
Execution time : 1 second -
Here you'll find a whole lot of ways for generating integer series along with perfomance measurements:
Integer Series Generator
Thanks for sharing SnippetyJoe -
-
Hmmm, looks like this thread has had quite a bit of activity over night. Lots to discuss. I'll post separate messages for each user to keep the conversations distinct.
Re. "little feature" versus "new language", o.k., you're right William. I guess calling it a little feature is a bit of an understatement. Even if it is a new language though, I still think it's one worth learning given its power.
Re. "four lines of cryptic code ... just to get the numbers from 1 to 10", I'd say it's no more cryptic than CONNECT BY LEVEL <= 10. If fact, once you get past learning a few new terms like "dimension" and "measure", my solution boils down to a simple loop. Let me take a stab at explaining it.
First, the "SELECT ... from DUAL where 1=2" is simply a little trick I use to start with an empty result set. We all know that "SELECT * FROM DUAL WHERE 1=2" returns no rows.
Next the MODEL clause kicks in. MODEL basically lets you treat the result set of the SELECT FROM DUAL as an array. To reference elements in the array you use syntax like "measure[dimension]". You can either use columns from the base table as measures and dimensions or you can roll your own columns. In my query I created one column called "key" and one called "integer_value". It's like creating columns based on expressions in a query, e.g. "select 0 as key, 0 as integer_value ...".
Now that we have an empty set and we've told the SQL engine which column will act as the dimension in our array and which will act as the measure we use a simple FOR loop to create new array elements. UPSERT tells Oracle that, if any of the cells we change with our rule does not exist, insert the cell. In my query, since the set starts out empty, all the cells referenced in the rule will be inserted. That's how we get 10 rows in the final result set. Finally the "cv(key)" expression is simply a call to a function called CV(), "Current Value", which returns the current value of KEY in each loop iteration.
O.k., I admit that's a lot of verbiage, but I think the underlying concepts are fairly simple once you know the basics of how MODEL works. -
Re. "the transitive closure of a graph", sorry Vadim, haven't got a clue what that is. I'm just a simple little code monkey. (BTW, your new SQL Design Patterns book looks like a real gem. I'm going to have to get me a copy some day. :-) )
This discussion has been closed.