1 2 3 4 Previous Next 49 Replies Latest reply on Nov 19, 2010 4:02 PM by BluShadow Go to original post
      • 45. Re: Multi-Rows from DUAL
        428027
        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
        • 46. Re: Multi-Rows from DUAL
          572471
          Volder's right about RETURN UPDATED ROWS though. It
          would produce the same results as using WHERE 1=2.
          No, Joe, I wasn't right. And I gave an example in my post previous to your post :)
          • 47. Re: Multi-Rows from DUAL
            494018
            Sorry Volder, I don't follow. I was saying that this
            select integer_value
            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
                        3
            gives the same results as this
            select integer_value
            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
                        3
            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.
            select integer_value
            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
            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.

            --
            Joe Fuda
            SQL Snippets
            • 48. Re: Multi-Rows from DUAL
              816641
              SELECT Row_Number() over(order by 1) FROM DUAL CONNECT BY ROWNUM <11;
              • 49. Re: Multi-Rows from DUAL
                BluShadow
                user12919849 wrote:
                SELECT Row_Number() over(order by 1) FROM DUAL CONNECT BY ROWNUM <11;
                Ok, so you've dragged up an old old thread for what reason?

                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.
                1 2 3 4 Previous Next