I think that the two pure sql answers(while imaginative) made Mike's point. I would hate to have to maintain either of those and i would hate to have to explain to someone how they worked.
There really are times where it is best to do it in pl/sql. Conversely, I am currently working on converting two particularly hideous reports that were written as procedures into something a little more effective than a bunch of nested cursors that generate TSV files at the end. What the original authors were thinking completely escapes me. On Sep 7, 4:35 am, benoit bordeaux <benoit.depla...@gmail.com> wrote: > create table t (val number not null); > insert into t values (1 ); > insert into t values (2 ); > insert into t values (3 ); > insert into t values (4 ); > insert into t values (8 ); > insert into t values (9 ); > insert into t values (10); > insert into t values (11); > insert into t values (12); > insert into t values (13); > insert into t values (20); > insert into t values (21); > insert into t values (22); > insert into t values (23); > insert into t values (24); > commit; > > with lData as ( > select val, > lag(val, 1) over (order by val) as lag_val, > lead(val, 1) over (order by val) as lead_val > from t), > lDataBound as ( > select val, > lag_val, > case when (lag_val is null) or (val != lag_val + 1) then 'Y' > else 'N' end as lower_bound, > case when (lead_val is null) or (val != lead_val - 1) then 'Y' > else 'N' end as upper_bound > from lData), > lData_upper_bound as ( > select val, > row_number() over (order by val) as rn > from lDataBound > where upper_bound = 'Y'), > lData_lower_bound as ( > select val, > row_number() over (order by val) as rn > from lDataBound > where lower_bound = 'Y') > select a.val as lower_bound, > b.val as upper_bound > from lData_lower_bound a > join lData_upper_bound b > on b.rn = a.rn > order by a.rn; > > LOWER_BOUND UPPER_BOUND > ----------- ----------- > 1 4 > 8 13 > 20 24 > > Regards > > Benoit --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Oracle PL/SQL" group. To post to this group, send email to Oracle-PLSQL@googlegroups.com To unsubscribe from this group, send email to oracle-plsql-unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/Oracle-PLSQL?hl=en -~----------~----~----~----~------~----~------~--~---
