Jointure à une plage d'entiers, qui est créée séparément pour chaque ligne.

Question

De la table :

|   name   |   range   |
------------------------
| 'Range1' | '456-458' |
| 'Range2' |   '11-13' |

J'essaie juste d'obtenir ce résultat :

|   name   |   range   | value |
--------------------------------
| 'Range1' | '456-458' |  456  |
| 'Range1' | '456-458' |  457  |
| 'Range1' | '456-458' |  458  |
| 'Range2' |   '11-13' |   11  |
| 'Range2' |   '11-13' |   12  |
| 'Range2' |   '11-13' |   13  |

J'ai fait la requête qui fonctionne bien si la table source n'a qu'une seule plage :

WITH data AS (
    SELECT 'Range1' name, '456-458' range FROM dual
)
SELECT ROWNUM, name, range, LEVEL value
FROM data, dual
WHERE LEVEL >= to_number(SUBSTR(range, 1, INSTR(range,'-')-1))
CONNECT BY LEVEL <= to_number(SUBSTR(range, INSTR(range,'-')+1));

mais renvoie des dizaines de milliers de lignes, si elle prend deux plages :

WITH data AS (
    SELECT 'Range1' name, '456-458' range FROM dual
    UNION
    SELECT 'Range2' name, '11-13' range FROM dual
)
SELECT ROWNUM, name, range, LEVEL value FROM data, dual
WHERE LEVEL >= to_number(SUBSTR(range, 1, INSTR(range,'-')-1))
CONNECT BY LEVEL <= to_number(SUBSTR(range, INSTR(range,'-')+1));

Est-il possible d'améliorer cette requête pour obtenir le résultat souhaité, ou bien mon approche initiale est erronée ?

Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production

Answer 1

Dans 11g vous pouvez utiliser une sous-requête récursive factorisée pour ce type de chose.

SQL> with data as (select name, range,
  2                       to_number(substr(range, 1, instr(range,'-')-1)) from_val,
  3                       to_number(substr(range, instr(range,'-')+1)) to_val
  4                  from your_table),
  5  ranges (name, range, curr_val, from_val, to_val)
  6  as (select name, range, from_val, from_val, to_val
  7        from data
  8      union all
  9      select name, range, curr_val+1, from_val, to_val
 10        from ranges
 11       where curr_val < to_val)
 12  select name, range, curr_val
 13    from ranges
 14   order by name, curr_val;

NAME   RANGE     CURR_VAL
------ ------- ----------
Range1 456-458        456
Range1 456-458        457
Range1 456-458        458
Range2 11-13           11
Range2 11-13           12
Range2 11-13           13

Answer 2

La récursion (réponse de DazzaL) est une meilleure solution mais si vous voulez voir comment le faire en utilisant une requête hiérarchique.

WITH data AS (
SELECT 'Range1' name
     , '456-460' range 
  FROM dual
UNION ALL
SELECT 'Range2' name
     , '11-13' range 
  FROM dual)

, ranges AS (
SELECT name
     , range
     , to_number(SUBSTR(range, 1, INSTR(range,'-')-1)) from_range
     , to_number(SUBSTR(range, INSTR(range,'-')+1)) to_range
  FROM data

), max_seq AS (
SELECT MAX(to_range - from_range) + 1 max_seq
  FROM ranges

), seq AS (
SELECT ROWNUM seq
  FROM max_seq
CONNECT BY LEVEL <= max_seq
) 

SELECT ROWNUM
     , name
     , range
     , from_range + seq - 1 value
  FROM ranges
 INNER JOIN seq
    ON seq <=  (to_range - from_range) + 1