Discussions
Categories
 385K All Categories
 2.5K Data
 580 Big Data Appliance
 2K Data Science
 452K Databases
 222.3K General Database Discussions
 3.8K Java and JavaScript in the Database
 32 Multilingual Engine
 575 MySQL Community Space
 479 NoSQL Database
 7.9K Oracle Database Express Edition (XE)
 3.1K ORDS, SODA & JSON in the Database
 575 SQLcl
 4K SQL Developer Data Modeler
 187.7K SQL & PL/SQL
 21.5K SQL Developer
 11 Data Integration
 11 GoldenGate
 297.6K Development
 3 Application Development
 18 Developer Projects
 140 Programming Languages
 294.3K Development Tools
 118 DevOps
 3.1K QA/Testing
 646.4K Java
 30 Java Learning Subscription
 37K Database Connectivity
 185 Java Community Process
 107 Java 25
 22.1K Java APIs
 138.3K Java Development Tools
 165.3K Java EE (Java Enterprise Edition)
 21 Java Essentials
 172 Java 8 Questions
 86K Java Programming
 82 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
 206 Java User Groups
 24 JavaScript  Nashorn
 Programs
 580 LiveLabs
 41 Workshops
 10.2K Software
 6.7K Berkeley DB Family
 3.5K JHeadstart
 5.7K Other Languages
 2.3K Chinese
 191 Deutsche Oracle Community
 1.1K Español
 1.9K Japanese
 240 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.