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
-~----------~----~----~----~------~----~------~--~---
