Saturday, September 22, 2007

Using Model for generating CSV by Partition

This example assumes that  "thedate"can not be NULL.
CUSTTAB
      CUST    THEDATE
---------- ----------
         5        234
         5        333
         5        336
         6        111
         6        222
         6        333
         5        100

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 thedate) 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
---------- ------------------------------
         5 100,234,333,336
         6 111,222,333                  

2 rows selected.

Other model to CSV solution can be found at
http://forums.oracle.com/forums/message.jspa?messageID=1815489#1815489
and
http://www.sqlsnippets.com/en/topic-12092.html

In contrast to the example at the above URL, note the use of
UPDATE instead of UPSERT and the lack of a need for the "presentv" function.
The reference to a non-existent cell in "until thedate[ITERATION_NUMBER +2] is null"
returns NULL;

Intuition tells me that UPDATE should run faster than UPSERT,
but I've not actually tested it.


No comments:

Labels