Mar 072014
 
with recursive_query_subfactoring (
    you_can_generate_rows, yes
) as (
  select 1 you_can_generate_rows,
         'from nothing!' yes
  from   dual
  union  all
  select you_can_generate_rows + 1,
         rpad(yes, 
              13 + you_can_generate_rows, 
              '!') yes
  from   recursive_query_subfactoring
  where  you_can_generate_rows < 100
)
select you_can_generate_rows, yes
from   recursive_query_subfactoring
where  rownum <= 10;

YOU_CAN_GENERATE_ROWS YES               
--------------------- --------------------
                    1 from nothing!       
                    2 from nothing!!      
                    3 from nothing!!!     
                    4 from nothing!!!!    
                    5 from nothing!!!!!   
                    6 from nothing!!!!!!  
                    7 from nothing!!!!!!! 
                    8 from nothing!!!!!!!!
                    9 from nothing!!!!!!!!! 
                   10 from nothing!!!!!!!!!! 

If you want more details, Rob van Wijk has a nice detailed post about CTEs and Jonathan Lewis discusses some possible issues with hinting these.

Personally I’ll be sticking with connect by for now as it’s quicker to type! ;)

  4 Responses to “The alternative to connect by for magicking rows from nothing”

  1. I like CONNECT BY better as well – even if it is not ANSI standard – as it is better to understand in my opinion. Also, who cares for for standards (in this special case)? Why migrate away from the best database there is :)

    Is there a use case you can’t solve with CONNECT BY that you can solve with CTEs?
    Having said that I remember a bug I found in a CONNECT BY with joins, have to look it up in OTN.

    Best regards,
    Salek

    • I’m not aware of anything you can do with CTEs that aren’t possible with CONNECT BY (that doesn’t mean there isn’t though!). CTEs allow you to specify DEPTH/BREADTH FIRST for searching – in some cases this may make it clearer what you’re trying to do. In general CTEs are more verbose though.

  2. select from dual is not nothing ? but
    select from dual where null is not null is.
    So

    select you_can_generate_rows, yes
    from dual
    where null is not null
    model
    dimension by ( 1 no )
    measures ( 0 you_can_generate_rows, cast(null as varchar2(30)) yes)
    rules upsert all iterate(10) (
    you_can_generate_rows[ iteration_number ] = cv(no) + 1,
    yes [ iteration_number ] = rpad( 'from nothing', 12 + you_can_generate_rows[cv()], '!')
    )
    /
    YOU_CAN_GENERATE_ROWS YES
    --------------------- ------------------------------
    1 from nothing!
    2 from nothing!!
    3 from nothing!!!
    4 from nothing!!!!
    5 from nothing!!!!!
    6 from nothing!!!!!!
    7 from nothing!!!!!!!
    8 from nothing!!!!!!!!
    9 from nothing!!!!!!!!!
    10 from nothing!!!!!!!!!!

    • True, was using a bit of artistic license there :)

      Nice example getting something from nothing there.

 Leave a Reply

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

(required)

(required)