PL/SQL

May 30, 2007

Using Model for generating CSV by Partition

Filed under: CSV, example, MODEL, PL/SQL, SQL — Michael Moore @ 5:48 pm

This article has moved to

plsqlnotes.blogspot.com

Advertisements

2 Comments »

  1. You should warn people that your solution only works when THEDATE is a NOT NULL column. Otherwise you can get results like the following with this approach.

    select * from custtab order by thesortkey ;

    CUST THEDATE THESORTKEY
    ———- ———- ———-
    2 1 1
    2 2 2
    2 3
    2 4 4
    2 5 5

    5 rows selected.

    col csv format a30

    select cust,substr(csv,2) csv from custtab
    model
    return updated rows
    partition by (cust)
    dimension by (row_number() over (partition by cust order by thesortkey) as therow)
    measures ( thedate, cast (null as varchar2(400)) as csv)
    rules update
    iterate(9999) until thedate[ITERATION_NUMBER +2] is null
    (csv[1] = csv[1]||’,’||thedate[ITERATION_NUMBER +1])
    order by 1;

    CUST CSV
    ———- ——————————
    2 1,2

    1 row selected.

    In this case CSV should have been “1,2,,4,5”, not “1,2”.

    Consider adding “WHERE thedate is not null” to your query just to be safe. Using PRESENTV also avoids the problem with nulls (in fact, that’s why I used it in the SQL Snippets solution you referenced), but if you really don’t like calling PRESENTV then there’s always this tweak.

    select cust,substr(csv,2) csv from custtab
    model
    return updated rows
    partition by (cust)
    dimension by (row_number() over (partition by cust order by thesortkey) as therow)
    measures ( thedate, cast (null as varchar2(400)) as csv , 1 AS THEITERATIONFLAG)
    rules update
    iterate(9999) until THEITERATIONFLAG[iteration_number +2] is null
    (csv[1] = csv[1]||’,’||thedate[iteration_number +1])
    order by 1;

    CUST CSV
    ———- ——————————
    2 1,2,,4,5

    1 row selected.

    HTH.


    Joe Fuda
    http://www.sqlsnippets.com/

    Comment by SnippetyJoe — June 8, 2007 @ 6:43 pm

  2. All good points Joe. I agree that if your “ORDER BY” column is not the same as your CSV column, then the tweak you have shown would be necessary. Also, as you have illustrated, if you want to preserve NULL values in your CSV ( i.e. 4,,5,6 instead of 4,5,6 ) then my solution will not work.
    Just for fun, here is another solution that
    1. allows ORDER BY on a non CSV column
    2. preserves null columns
    3. provides FIXED formated output

    `
    col csv format a30
    select cust,substr(csv,2) csv from custtab
    model
      return updated rows
      partition by (cust)
      dimension by (row_number() over (partition by cust order by ind) as therow)
      measures ( nvl(cast(thedate as char(6)),’      ’) thedate,cast (null as varchar2(400)) as csv)
      rules update
            iterate(9999) until thedate[ITERATION_NUMBER +2] is null
                 (csv[1] = csv[1]||’,’||thedate[ITERATION_NUMBER  +1])
      order by 1;

          CUST CSV                          
    ———- ——————————
             5 100   ,234   ,      ,336      
             6 333   ,111   ,222            
             7       ,666   ,555   ,        
    `

    Comment by Michael Moore — June 10, 2007 @ 3:04 am


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: