Forum Stats

  • 3,760,392 Users
  • 2,251,699 Discussions
  • 7,871,092 Comments

Discussions

Multi-Rows from DUAL

Gerd Volberg
Gerd Volberg Member Posts: 4,558 Blue Ribbon
edited Nov 19, 2010 11:02AM in SQL & PL/SQL
I need an easy select from DUAL, which gives me more than one row.

e.g. 1000 rows or 4500 rows....


I know that one of the best solutions was something with an CONNECT BY PRIOR and a WHERE ROWNUM <= 1000 e.g.


But what was the complete statement?

thx 4 solutions
Gerd
«1345

Comments

  • 229023
    229023 Member Posts: 2,305
    select * from 
    (select * from dual connect by level <= 1000)
  • 21205
    21205 Member Posts: 6,168 Gold Trophy
    select rownum
    from dual
    connect by level <= 10
  • 494018
    494018 Member Posts: 227
    Be careful when using this approach. There are a few things that can trip you up.

    1. If you use a bind variable in place of the value 1000 and your bind variable value can ever be 0 the query will not work as expected.

    2. People have reported bugs with these types of queries in various Oracle versions.

    3. There is some debate about whether the syntax is legal and whether it will continue to work in future versions.

    Given these risks you may want to consider using this alternative query instead (assuming you are running on 10g).
    select integer_value
    from   dual
    where  1=2
    model
      dimension by ( 0 as key )
      measures     ( 0 as integer_value )
      rules upsert ( integer_value[ for key from 1 to 10 increment 1 ] = cv(key) )
    ;
    
    INTEGER_VALUE
    -------------
                1
                2
                3
                4
                5
                6
                7
                8
                9
               10
    As an added bonus, the MODEL query runs faster than the CONNECT BY LEVEL query.

    You can find more details (including a fix for issue #1) at

    http://www.sqlsnippets.com/en/topic-11821.html (SQL Snippets: Integer Series Generators - CONNECT BY LEVEL Method)

    and

    http://www.sqlsnippets.com/en/topic-11979.html (SQL Snippets: Integer Series Generators - MODEL Method).

    Hope this helps.
  • William Robertson
    William Robertson Member Posts: 9,564 Bronze Crown
    Neat.

    Is it just me or is MODEL() evil though? I notice it gives the same results regardless of the values you put in the DIMENSION BY and MEASURES clauses, whatever they do. It just seems like a short step from MODEL to MDX or, heaven help us, XQuery.
  • 494018
    494018 Member Posts: 227
    MODEL Evil???? Oh no, no, no. Au contraire mon ami. MODEL is the best thing since sliced bread. It's my new favourite feature. It slices, it dices, it loops, it straggs ... it does all sorts of neat things. It's like having PL/SQL in your queries without the context switches.

    I think people may not appreciate MODEL's usefulness because of the learning curve associated with it. MODEL packs a lot of functionality into one little clause so it's hard to get your head around it unless you learn it the right way. That's one of the reasons I wrote a really easy tutorial series on MODEL that goes through each component one step at a time. Check it out at http://www.sqlsnippets.com/en/topic-11663.html and then tell me if you still think it's evil or simply a misunderstood little feature. :-)
  • William Robertson
    William Robertson Member Posts: 9,564 Bronze Crown
    It's not so much a "little feature" as a new language. It seems to have nothing to do with SQL. I did once hope SQL could have some sort of inline function facility, which just goes to show you should be careful what you wish for.

    I guess I'll have to buy a 500 page book on MODEL() one day. I'll see if I can work through your examples (thanks btw) over the next five years or so.
  • Model clause is like a mini rule based system, right? Here is an exercise, write a transitive closure of a graph. Those are just 2 rules:

    AllEdges(x,y) :- Edges(x,y)
    AllEdges(x,y) :- Edges(x,y) AllEdges(x,y)

    Can you do it with Model clause?
  • William Robertson
    William Robertson Member Posts: 9,564 Bronze Crown
    It takes four lines of deeply cryptic code involving "measures", "dimensions" and an "upsert" just to get the numbers from 1 to 10 out of it, so I imagine the transitive closure of a graph isn't going to be pretty.

    I'm guessing "transitive closure", "graph" and ":-" mean something to mathematicians.
  • Gerd Volberg
    Gerd Volberg Member Posts: 4,558 Blue Ribbon
    that's pretty easy. Thx to you all
  • Gerd Volberg
    Gerd Volberg Member Posts: 4,558 Blue Ribbon
    I've tested this version and the value 0 runs without error, except, that 1 row is returned

    SELECT Level LVL
    FROM Dual
    CONNECT BY Level <= maxValue;
This discussion has been closed.