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