Saturday, March 11, 2006

Simplest row generator meets maximum inefficiency

Generating a set of values that are not stored in a table is a reasonably common problem. I recently came across this solution for a set of irregular interval values.

 OPEN cur_rows FOR
   'SELECT 3  FROM dual UNION '||
   'SELECT 6  FROM dual UNION '||
   'SELECT 9  FROM dual UNION '||
   'SELECT 12  FROM dual UNION '||
   'SELECT 24  FROM dual UNION '||
   'SELECT 36  FROM dual';
    FETCH cur_rows INTO v_num_value;
    EXIT WHEN cur_rows%NOTFOUND;
I didn't know which to admire the most. The clever use of dynamic SQL or the sort distinct to get rid of any duplicate literals that may occur from accidentally typing the same line twice. I think the latter wins for being also relationally pure apparently.

No comments: