• lsinoff - Tuesday, January 2, 2018 9:33 PM

    I need to create from 1 to n rows from a single row where the one column in the read table contains a string from 8 to 16 characters long.  The rows written contain a single column 8 bytes long containing all each row being a different combination of 8 consecutive characters from the column of the read table. 
    INPUT:  IN_NUM contains '3456738947'
    Output Row 1: NUM_7 contains '34567389'
    Output Row 2: NUM_7 contains '45673894'
    Output Row 3: NUM_7 contains '56738947'
    My ultimate input table will have over 1,000,000 rows and many more columns
    My ultimate output table will have about 4,000,000 rows and a subset of columns from the input table

    I'm a bit confused.  Ignoring the typo in Row2 of the output, what are you actually looking for the given value of '3456738947'.  Is it just 3 rows as you have listed or do you really want 8 rows using the full rotation of the "ring counter" that you've described?

    How about the example of '41724246874135'  that Luis used?  Do you want just the first 3 rows from the "ring" or do you want the full "ring" of 14 rows?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)