Thursday, September 22, 2005

Fun with String

It seems that my predecessor needed to convert a row of five columns into a column with five rows. Simple enough, you might think, once you realise that you can use a handy collection type in a TABLE() expression. But hey, why stop there when there is a much, much harder way involving a string-to-table function and a self-join on three columns? ...that aren't quite unique... (Table and column names changed to protect the guilty)
FROM   huge_table t
     , TABLE ( SELECT utils.string_to_table
                      ( t2.col1 || ',' || t2.col2 || ',' ||
                        t2.col3 || ',' || t2.col4 || ',' || t2.col5)
               FROM   huge_table t2
               WHERE  t2.switch_id = t.switch_id
               AND    t2.switch_ctn = t.switch_ctn
               AND    t2.phone_prefix = t.phone_prefix
               AND    ROWNUM < 2 )
Now if that isn't half ample in the WHERE, I don't know what is.

No comments: