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 <[email protected]> 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 <[email protected]>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
> > <[email protected]>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 <[email protected]>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 [email protected]
To unsubscribe from this group, send email to
[email protected]
For more options, visit this group at
http://groups.google.com/group/Oracle-PLSQL?hl=en
-~----------~----~----~----~------~----~------~--~---
