Discussions
Categories
 195.9K All Categories
 2.1K Data
 208 Big Data Appliance
 1.9K Data Science
 447.2K Databases
 220.8K General Database Discussions
 3.7K Java and JavaScript in the Database
 23 Multilingual Engine
 516 MySQL Community Space
 463 NoSQL Database
 7.7K Oracle Database Express Edition (XE)
 2.9K ORDS, SODA & JSON in the Database
 461 SQLcl
 3.9K SQL Developer Data Modeler
 185.8K SQL & PL/SQL
 20.9K SQL Developer
 292.4K Development
 7 Developer Projects
 122 Programming Languages
 289.2K Development Tools
 94 DevOps
 3K QA/Testing
 645.4K Java
 22 Java Learning Subscription
 36.9K Database Connectivity
 150 Java Community Process
 104 Java 25
 22.1K Java APIs
 137.8K Java Development Tools
 165.3K Java EE (Java Enterprise Edition)
 14 Java Essentials
 142 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
 197 Java User Groups
 24 JavaScript  Nashorn
 Programs
 223 LiveLabs
 34 Workshops
 10.2K Software
 6.7K Berkeley DB Family
 3.5K JHeadstart
 5.8K Other Languages
 2.3K Chinese
 166 Deutsche Oracle Community
 1.2K Español
 1.9K Japanese
 225 Portuguese
MultiRows from DUAL
Comments

Yes, there is. The difference would be that OracleWell, If I fully understand what you mean  I can't agree.
would now have 1 row to put into the model initially
and it would have to administer which rows where
original and which ones are new. The query as is
starts blank, generates 10 cells, and converts them
back as rows. A little less work than using the
RETURN UPDATED ROWS clause.
Cause when you use e.g. WHERE 1=2 in the model clause it would return not only inserted values, but also updated  so the process would be quite the same  it also have to recognize what values were updated and inserted, and what values  were not touched.SQL> with t as (select 1 num from dual union all 2 select 2 from dual union all 3 select 3 from dual union all 4 select 4 from dual) 5  6 select * from t 7  where 1=2 8 model 9 return updated rows 10 dimension by (num rn) 11 measures(num) 12 rules(num[1]=0, 13 num[3]=0, 14 num[5]=1, 15 num[6]=1) 16 / RN NUM   1 0 3 0 6 1 5 1 SQL> SQL> with t as (select 1 num from dual union all 2 select 2 from dual union all 3 select 3 from dual union all 4 select 4 from dual) 5  6 select * from t 7 where 1=2 8 model 9  return updated rows 10 dimension by (num rn) 11 measures(num) 12 rules(num[1]=0, 13 num[3]=0, 14 num[5]=1, 15 num[6]=1) 16 / RN NUM   6 1 5 1 3 0 1 0 SQL>

SQL> edThat's because you are using an earlier version of Oracle.
Wrote file afiedt.buf
1 SELECT rownum
2 FROM Dual
3* CONNECT BY rownum <= 10
L> /
ROWNUM

1
showing only 1 not 1 to 10
Try:
SELECT rn FROM (SELECT rownum rn FROM DUAL CONNECT BY ROWNUM <= 10);

Ooops, realized that I'm wrong!
They are all four rows inserted  you are right, Rob.
If we use symbolic reference  we can easily see the difference:SQL> with t as (select 1 num from dual union all 2 select 2 from dual union all 3 select 3 from dual union all 4 select 4 from dual) 5  6 select * from t 7  where 1=2 8 model 9 return updated rows 10 dimension by (num rn) 11 measures(num) 12 rules(num[rn=1]=0, 13 num[rn=3]=0, 14 num[5]=1, 15 num[6]=1) 16 / RN NUM   1 0 3 0 6 1 5 1 SQL> SQL> with t as (select 1 num from dual union all 2 select 2 from dual union all 3 select 3 from dual union all 4 select 4 from dual) 5  6 select * from t 7 where 1=2 8 model 9  return updated rows 10 dimension by (num rn) 11 measures(num) 12 rules(num[rn=1]=0, 13 num[rn=3]=0, 14 num[5]=1, 15 num[6]=1) 16 / RN NUM   6 1 5 1 SQL>

The reason I used that condition is explained in the section labelled "WHERE 1=2" at SQL Snippets: Integer Series Generators  MODEL Method.
Volder's right about RETURN UPDATED ROWS though. It would produce the same results as using WHERE 1=2.

Joe Fuda
SQL Snippets 
The best solution is CONNECT BY as other said,
But if you forget it , one simple trick is just select rownum from dba_tables where rownum<10000.
Regards
Helio Dias
http://heliodias.com 
Volder's right about RETURN UPDATED ROWS though. ItNo, Joe, I wasn't right. And I gave an example in my post previous to your post
would produce the same results as using WHERE 1=2. 
Sorry Volder, I don't follow. I was saying that this
select integer_value
gives the same results as this
from dual
 where 1=2
model
RETURN UPDATED ROWS
dimension by ( 0 as key )
measures ( 0 as integer_value )
rules upsert ( integer_value[ for key from 1 TO 3 increment 1 ] = cv(key) )
;
INTEGER_VALUE

1
2
3select integer_value
As an aside, for many cases we don't need either clause. The following version works fine for queries with hardcoded limits that always return one or more rows.
from dual
where 1=2
model
 RETURN UPDATED ROWS
dimension by ( 0 as key )
measures ( 0 as integer_value )
rules upsert ( integer_value[ for key from 1 TO 3 increment 1 ] = cv(key) )
;
INTEGER_VALUE

1
2
3select integer_value
Personally I prefer the WHERE 1=2 approach because it seems cleaner and safer to start with an empty set right before the MODEL rules are applied. That's just me though.
from dual
model
dimension by ( 1 as key )  use "1" here instead of "0"
measures ( 1 as integer_value )  use "1" here instead of "0"
rules upsert ( integer_value[ for key from 1 TO 3 increment 1 ] = cv(key) )
;
INTEGER_VALUE

1
2
3

Joe Fuda
SQL Snippets 
SELECT Row_Number() over(order by 1) FROM DUAL CONNECT BY ROWNUM <11;

user12919849 wrote:Ok, so you've dragged up an old old thread for what reason?
SELECT Row_Number() over(order by 1) FROM DUAL CONNECT BY ROWNUM <11;
There's absolutely no benefit in using the analytical function row_number() to get row numbers out of that query when rownum itself will do the job. Using that function will only serve to decrease performance.
This discussion has been closed.