That was stupid of me. Guess It's what I get for looking a work on a holiday. Mike
On Tue, Sep 8, 2009 at 6:48 AM, ddf <orat...@msn.com> wrote: > > > > On Sep 7, 10:26 am, Michael Moore <michaeljmo...@gmail.com> wrote: > > Sonty, I get an error with this. I replace your table name with t so I > could > > use Benoit's data. > > Mike > > > > SELECT xph2.l low, xph.l high > > FROM t xph, t xph2 > > WHERE NOT EXISTS (SELECT l > > FROM t > > WHERE l = xph.l + 1) > > AND NOT EXISTS (SELECT l > > FROM t > > WHERE l = xph2.l - 1) > > AND (xph.l - xph2.l) + 1 = (SELECT COUNT (1) > > FROM t > > WHERE l <= xph.l AND l >= xph2.l) > > ORDER BY 1, 2 > > * > > Error at line 9 > > ORA-00904: "XPH2"."L": invalid identifier > > > > Script Terminated on line 1. > > > > > --------------------------------------------------------------------------------------------------- > > > > > > > > On Mon, Sep 7, 2009 at 1:18 AM, sonty <saurabh.zen...@gmail.com> wrote: > > > > > This is working for me: > > > > > SELECT xph2.l low,xph.l high > > > FROM test_temp xph, test_temp xph2 > > > WHERE NOT EXISTS (SELECT l > > > FROM test_temp > > > WHERE l = xph.l + 1) > > > AND NOT EXISTS (SELECT l > > > FROM test_temp > > > WHERE l = xph2.l - 1) > > > AND (xph.l - xph2.l)+1 = > > > (SELECT COUNT (1) > > > FROM test_temp > > > WHERE l <= xph.l > > > AND l >= xph2.l) > > > order BY 1,2 > > > > > where table test_temp has column l. > > > > > Regards, > > > Sonty > > > > > On Sep 6, 10:25 pm, Michael Moore <michaeljmo...@gmail.com> wrote: > > > > In oracle you could create a "table function" which you use like > this. > > > > > > select * from TABLE(mytab); > > > > > > where mytab is the name of a pl/sql function. That function must be > in a > > > > pl/sql package. It can not be 'stand alone'. > > > > > > A google search on ORACLE,PLSQL,TABLE,FUNCTION should get you > everything > > > you > > > > need to know. If not, write back. > > > > > > On Sun, Sep 6, 2009 at 5:43 AM, David Hart < > david.john.h...@gmail.com > > > >wrote: > > > > > > > I would expect this:LBound UBound > > > > > 1 2 > > > > > 5 5 > > > > > 7 8 > > > > > 12 12 > > > > > > > I'm more from a SQL Server background... could I create a > temporary > > > table, > > > > > fill it with the correct values and then return the result from a > SPROC > > > or > > > > > function? > > > > > > > D. > > > > > > > On Sat, Sep 5, 2009 at 10:47 AM, Michael Moore < > > > michaeljmo...@gmail.com>wrote: > > > > > > >> This would probably best be solved procedurally. If you can come > up > > > with a > > > > >> query that does it, it will probably be very ugly. Also, what > result > > > would > > > > >> you expect in this case: > > > > > > >> 1 > > > > >> 2 > > > > >> 5 > > > > >> 7 > > > > >> 8 > > > > >> 12 > > > > > > >> ?? > > > > > > >> Mike > > > > > > >> On Sat, Sep 5, 2009 at 6:49 AM, Dave Hart < > david.john.h...@gmail.com > > > >wrote: > > > > > > >>> Hi, > > > > >>> I have a table which includes data like this: > > > > > > >>> SEQ > > > > >>> 1 > > > > >>> 2 > > > > >>> 3 > > > > >>> 4 > > > > >>> 8 > > > > >>> 9 > > > > >>> 10 > > > > >>> 11 > > > > >>> 12 > > > > >>> 13 > > > > >>> 20 > > > > >>> 21 > > > > >>> 22 > > > > >>> 23 > > > > >>> 24 > > > > > > >>> I want to be able to write a query that displays the upper and > lower > > > > >>> bounds of each range: > > > > >>> LBound UBound > > > > >>> 1 4 > > > > >>> 8 13 > > > > >>> 20 24 > > > > > > >>> I've found plenty of queries that find the start and end of the > gaps > > > > >>> but none that display the start and end of the ranges. > > > > > > >>> Any help is appreciated. > > > > > > >>> Thanks, > > > > >>> Dave- Hide quoted text - > > > > > > - Show quoted text -- Hide quoted text - > > > > - Show quoted text - > > You cannot do that without creating table 't' with a 'proper' > definition (one that matches sonty's example table). This works: > > SQL> create table t(l number not null); > > Table created. > > SQL> > SQL> insert into t values (1 ); > > 1 row created. > > SQL> insert into t values (2 ); > > 1 row created. > > SQL> insert into t values (3 ); > > 1 row created. > > SQL> insert into t values (4 ); > > 1 row created. > > SQL> insert into t values (8 ); > > 1 row created. > > SQL> insert into t values (9 ); > > 1 row created. > > SQL> insert into t values (10); > > 1 row created. > > SQL> insert into t values (11); > > 1 row created. > > SQL> insert into t values (12); > > 1 row created. > > SQL> insert into t values (13); > > 1 row created. > > SQL> insert into t values (20); > > 1 row created. > > SQL> insert into t values (21); > > 1 row created. > > SQL> insert into t values (22); > > 1 row created. > > SQL> insert into t values (23); > > 1 row created. > > SQL> insert into t values (24); > > 1 row created. > > SQL> commit; > > Commit complete. > > SQL> > SQL> SELECT xph2.l low, xph.l high > 2 FROM t xph, t xph2 > 3 WHERE NOT EXISTS (SELECT l > 4 FROM t > 5 WHERE l = xph.l + 1) > 6 AND NOT EXISTS (SELECT l > 7 FROM t > 8 WHERE l = xph2.l - 1) > 9 AND (xph.l - xph2.l) + 1 = (SELECT COUNT (1) > 10 FROM t > 11 WHERE l <= xph.l AND l >= xph2.l) > 12 ORDER BY 1, 2; > > LOW HIGH > ---------- ---------- > 1 4 > 8 13 > 20 24 > > SQL> > > > David Fitzjarrell > > > --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
