how to calc ranges in oracle

Question

Hi mates,

I have a table defining ranges, e.g.:

START | END | MAP
1     | 10  | A
11    | 15  | B
...

how do I query into that table so the result will be

ID | MAP
1  | A
2  | A
3  | A
4  | A
5  | A
6  | A
7  | A
8  | A
9  | A
10 | A
11 | B
12 | B
13 | B
14 | B
15 | B
...

I bet its a easy one... Thanks for the help

f.

Answer 1

WITH    r AS
        (
        SELECT   MAX(end - start) + 1 AS mr
        FROM     ranges
        ),
        series AS
        (
        SELECT   level - 1 AS l
        FROM     dual
        CONNECT BY
                 level <=
                 (
                 SELECT  mr
                 FROM    r
                 )
        )
SELECT  start + l, map
FROM    ranges
JOIN    series
ON      l <= end - start

In PostgreSQL, you could just do:

SELECT  map, generate_series(start, end)
FROM    ranges

Update:

Tested on your sample data:

WITH    ranges AS
        (
        SELECT  1 AS f_start, 10 AS f_end, 'A' AS map
        FROM    dual
        UNION ALL
        SELECT  11 AS f_start, 15 AS f_end, 'B' AS map
        FROM    dual
        ),
        r AS
        (
        SELECT   MAX(f_end - f_start) + 1 AS mr
        FROM     ranges
        ),
        series AS
        (
        SELECT   level - 1 AS l
        FROM     dual
        CONNECT BY
                 level <=
                 (
                 SELECT  mr
                 FROM    r
                 )
        )
SELECT  f_start + l, map
FROM    ranges
JOIN    series
ON      l <= f_end - f_start
ORDER BY
        2, 1

Answer 2

 select * from Table, (Select Level as Id from dual connect by Level <= (Select Max(End)      from Table)) t
 Where  t.Id between rr.Start and rr.End
 Order by Map, Start, Id

Answer 3

I can give you a dirty solution. But please don't laugh at me :(

1. Prepare a dummy table, say table DUMMY which contains only one field (DUMMY_ID) which its values are 1..n where n is big enough to your problem. Let's take n = 100 for example.

2. Join these two tables, your actual table and the DUMMY table. Just like this :

   SELECT DUMMY_ID, MAP FROM DUMMY, (SELECT START, END, MAP FROM ACTUAL) AS ACTUAL WHERE DUMMY_ID BETWEEN START AND END

Note that given query above is MySQL. I didn't use Oracle for long time but sure you got the point.

Answer 4

This solution at first glance it looks complicated, but generally resolves any range. Solve problem with VALUEs which may be interfered with any other range.

Firstly create sample table and insert data:

create table test_table (col_START NUMBER, col_END NUMBER, col_MAP CHAR(1));
insert into test_table(col_START, col_END, col_MAP) values(1,10,'A');
insert into test_table(col_START, col_END, col_MAP) values(11,15,'B');
insert into test_table(col_START, col_END, col_MAP) values(5,12,'C');

Now data look like this:

START | END | MAP
1     | 10  | A
11    | 15  | B
5     | 12  | C

Now create object type:

CREATE TYPE SampleType AS OBJECT
(
  id number,
  map_string varchar2(2000)
)
/

CREATE TYPE SampleTypeSet AS TABLE OF SampleType
/

And also create PIPELINED FUNCTION:

CREATE OR REPLACE FUNCTION GET_DATA RETURN SampleTypeSet
PIPELINED
IS
    l_one_row SampleType := SampleType(NULL, NULL);

BEGIN

    FOR cur_data IN (select col_START, col_END, col_MAP from test_table) LOOP
        FOR i IN cur_data.col_START..cur_data.col_END LOOP
            l_one_row.id := i;
            l_one_row.map_string := cur_data.col_MAP;
            PIPE ROW(l_one_row);
        END LOOP;
    END LOOP;

    RETURN;
END GET_DATA;
/

Finally you can use simple query:

SELECT * FROM TABLE(GET_DATA());

Or create and select it from view (if you want hide OBJECT implementation):

CREATE VIEW VIEW_ALL_DATA AS SELECT * FROM TABLE(GET_DATA());
SELECT * FROM VIEW_ALL_DATA;

Based on this my article:

http://martin-mares.cz/2010/08/oracle-db-pipelined-function/