Home » SQL & PL/SQL » SQL & PL/SQL » Create output that represents ranges based on broken consecutive list
Create output that represents ranges based on broken consecutive list [message #196662] Fri, 06 October 2006 07:37 Go to next message
kdnezz
Messages: 1
Registered: October 2006
Location: Houston
Junior Member
I use a MINUS operator to generate a list from two tables in an Oracle 7.3 databases:

===============================
SELECT DISTINCT PAGE_NUM
FROM A
WHERE PAY_END_DT = '01-oct-06'
AND PAYGROUP = upper('ATL')
MINUS
SELECT DISTINCT PAGE#
FROM B
WHERE WORK_WEEK = '01-oct-06'
AND PAYGROUP = upper('ATL')
===============================

I don't know how to create output that represents the consecutive numbers in my list as ranges. The gaps in the "CURRENT OUTPUT" list (ex. 4,5,6,7,8,9 or 4 - 9) represent processes that successfully completed. I want the output to be only the ranges of consecutive numbers (i.e. DESIRED OUTPUT) that need to be processed again because that is how the parameters are passed on an entry page.

===================================
CURRENT OUTPUT
----------------
1
2
3
10
11
12
20
21
22
===================================

DESIRED OUTPUT
--------------
1 - 3
10 - 12
20 - 22

====================================

Thanks in advance for any help.

[Updated on: Fri, 06 October 2006 07:39]

Report message to a moderator

Re: Create output that represents ranges based on broken consecutive list [message #196670 is a reply to message #196662] Fri, 06 October 2006 08:05 Go to previous messageGo to next message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
drop table seqs;

CREATE table seqs (seq_No number);

insert into seqs values (1);
insert into seqs values (2);
insert into seqs values (3);
insert into seqs values (4);
insert into seqs values (5);
insert into seqs values (8);
insert into seqs values (9);
insert into seqs values (10);
insert into seqs values (12);

select range from
(select 
seq_no||'-'||case when eflag = 'E' then seq_no else lead(seq_no) over (order by seq_no) end as range, sflag, eflag from
(select seq_no, 
case when seq_no - 1 <> lag(seq_no, 1,seq_no) over (order by seq_no) then 'S' end as Sflag,
case when seq_no + 1 <> lead(seq_no, 1,seq_no) over (order by seq_no) then 'E' end as Eflag
from seqs)
where sflag = 'S' or eflag = 'E')
where sflag = 'S'
Re: Create output that represents ranges based on broken consecutive list [message #196684 is a reply to message #196662] Fri, 06 October 2006 08:53 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
kdnezz wrote on Fri, 06 October 2006 08:37


WHERE PAY_END_DT = '01-oct-06'
.
.
.
WHERE WORK_WEEK = '01-oct-06'



Poor and sloppy coding to use strings for dates. When someone changes your NLS_DATE_FORMAT your query will fail miserably.
Re: Create output that represents ranges based on broken consecutive list [message #196685 is a reply to message #196662] Fri, 06 October 2006 08:53 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I'm afraid I lack anything as stone age as a 7.3.4 db to test this on, so it may not work.
(It's a doddle on 9i with analytics)

<pause for much coding and head scratching>

I'm afraid I can't think of a way to do this in a single query.

<longer pause to allow my stubborn refusal to admit defeat to kick in>

Right. Here we go. It's not pretty, but it works. Allow me to talk you through it.

Here's a table with the data in it.
create table temp_contig (col_1  number);

insert into temp_contig values (1);
insert into temp_contig values (2);
insert into temp_contig values (3);
insert into temp_contig values (10); 
insert into temp_contig values (11);
insert into temp_contig values (12);
insert into temp_contig values (13);
insert into temp_contig values (20);
insert into temp_contig values (21);
insert into temp_contig values (22);


This query builds several heirarchical tree from the temp_contig values, wherever there is a value in the table equal to the current value + 1
SQL> select col_1, level lvl
  2  from   temp_contig
  3  connect by prior col_1 = col_1+1;

     COL_1        LVL
---------- ----------
         1          1
         2          1
         1          2
         3          1
         2          2
         1          3
        10          1
        11          1
        10          2
        12          1
        11          2
        10          3
        13          1
        12          2
        11          3
        10          4
        20          1
        21          1
        20          2
        22          1
        21          2
        20          3

22 rows selected.


This query finds the highest position up a heirarchy for each of the values from the preious query. This is equivalent to the number of contiguous values earlier than this number.
If we add this position to the value itself, then we get a constant for each contiguous group.
select col_1,max(lvl) max_lvl,col_1 + max(lvl) grp
from (select col_1, level lvl
      from   temp_contig
      connect by prior col_1 = col_1+1)
group by col_1
/

COL_1    MAX_LVL        GRP
----- ---------- ----------
    1          3          4
    2          2          4
    3          1          4
   10          4         14
   11          3         14
   12          2         14
   13          1         14
   20          3         23
   21          2         23
   22          1         23

10 rows selected.


The tricky bit is done now.
All we have to do is to group by this constant value, and take the smallest and largest values we find, and we've got the range.
select min(col_1),max(col_1)
from  (select col_1,col_1 + max(lvl) grp
       from  (select col_1, level lvl
              from   temp_contig
              connect by prior col_1 = col_1+1)
       group by col_1)
group by grp;

MIN(COL_1) MAX(COL_1)
---------- ----------
         1          3
        10         13
        20         22


Phew.
Re: Create output that represents ranges based on broken consecutive list [message #196686 is a reply to message #196685] Fri, 06 October 2006 08:57 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Actually, I'm not certain this will work on 7.3.4.
Let us know.

(oh, and while you're at it, here's tuppence. Get yourself an upgrade!)
Re: Create output that represents ranges based on broken consecutive list [message #196988 is a reply to message #196686] Mon, 09 October 2006 08:17 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
JRow, I can confirm that this works in 7.3.4.
We have one of those dinosaurs around and tested your code.
Now, I wonder if it works in v6?
Re: Create output that represents ranges based on broken consecutive list [message #197001 is a reply to message #196988] Mon, 09 October 2006 08:44 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Shame the OP never replied. Sad
I may be being optimistic - he only said 7.3, so it might be 7.3.2 or 7.3.0 - they were both popular.
Previous Topic: tables
Next Topic: Diff between 2 sql's
Goto Forum:
  


Current Time: Mon Dec 05 08:52:17 CST 2016

Total time taken to generate the page: 0.05558 seconds