1 2 3 4 Previous Next 49 Replies Latest reply on Nov 19, 2010 4:02 PM by BluShadow

    Multi-Rows from DUAL

    Gerd Volberg
      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
        • 1. Re: Multi-Rows from DUAL
          229023
          select * from 
          (select * from dual connect by level <= 1000)
          • 2. Re: Multi-Rows from DUAL
            21205
            select rownum
            from dual
            connect by level <= 10
            • 3. Re: Multi-Rows from DUAL
              494018
              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.
              • 4. Re: Multi-Rows from DUAL
                William Robertson
                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.
                • 5. Re: Multi-Rows from DUAL
                  494018
                  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. :-)
                  • 6. Re: Multi-Rows from DUAL
                    William Robertson
                    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.
                    • 7. Re: Multi-Rows from DUAL
                      Vadim Tropashko-Oracle
                      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?
                      • 8. Re: Multi-Rows from DUAL
                        William Robertson
                        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.
                        • 9. Re: Multi-Rows from DUAL
                          Gerd Volberg
                          that's pretty easy. Thx to you all
                          • 10. Re: Multi-Rows from DUAL
                            Gerd Volberg
                            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;
                            • 11. Re: Multi-Rows from DUAL
                              ABB
                              This article shows both the MODEL and DUAL examples, but for speed, a pipelined function is pretty good too...
                              • 12. Re: Multi-Rows from DUAL
                                BluShadow
                                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.

                                ;)
                                • 13. Re: Multi-Rows from DUAL
                                  290833
                                  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
                                  • 14. Re: Multi-Rows from DUAL
                                    Avinash Tripathi
                                    Hi,
                                    Here one more method which can be used.
                                    SQL> ed
                                    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>
                                    Regards
                                    1 2 3 4 Previous Next