Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5

    Unanswered: little help with recursion

    I am having a little difficulty writing what I want today. Here is the set up.

    Code:
    my_table
         id                int
         start_code    char(05)
         end_code     char(05)
    data
    Code:
    1   21750  21750
    2   56732  56784
    3   12847  12848
    I am wanting to get back the entire range of the start and end code.

    Code:
    1 21750
    2 56732
    2 56733
    2 56734
    .....
    3 12847
    3 12848
    A little help would be appreciated.

    Dave

  2. #2
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    Try this:
    Code:
    WITH TESTTAB (ID, STR, END)                                 
      AS (                                                      
          SELECT 1, 21750, 21750 FROM SYSIBM.SYSDUMMY1 UNION ALL
          SELECT 2, 56732, 56784 FROM SYSIBM.SYSDUMMY1 UNION ALL
          SELECT 3, 12847, 12848 FROM SYSIBM.SYSDUMMY1          
         )                                                      
       , RESTAB (ID, STR, END)                                  
      AS (                                                      
          SELECT ID, STR, END                                   
          FROM TESTTAB                                          
          UNION ALL                                             
          SELECT ID, STR + 1, END                               
          FROM RESTAB                                           
          WHERE STR + 1 <= END                                  
          )                                                     
    SELECT ID, STR                                              
    FROM RESTAB                                                 
    ORDER BY ID, STR                                            
     
    ID          STR
    --  -----------
    1        21750
    2        56732
    2        56733
    2        56734
    . . .
    2        56783
    2        56784
    3        12847
    3        12848

  3. #3
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    Thanks Stealth! Just couldn't get it figured out this am. Think I had already started my weekend. Had a bit more work to do on it, as some of the codes start with alpha characters, but it works like a charm. Thanks again.
    Dave

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •