SELECT ... 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.
Thursday, September 22, 2005
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)
Posted by William Robertson at 7:13 pm