Discussion :

[kolodz]

Bonjour,

Dans le cadre d'un de mes développements, je dois valider la validité d'un élément en base de donnée en vérifiant si celui-ci existe pour une plage de valeur donnée.

Par exemple :

Dans une table ayant les colonnes suivantes (code, debut, fin), je vais avoir le contenu suivant :

Code x :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
('A', 10, 20)
('A', 21, 30)
('A', 31, 40)
('B', 10, 20)
('B', 31, 40)
('C', 10, 40)

Et je dois valider si A/B/C est disponible pour un plage (15, 35) par exemple. J'ai une requête un peu idiote qui réalise un min et un max. Ce qui me permet de dégrossir la majorité des cas. Mais, il est possible que je tombe dans le cas représenté par le cas B dans mon exemple. A savoir une plage de debut/fin discontinue et valide pour ma requête.

Ma requête en l'état :

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

select * from maTable where code = 'B' group by code having min(debut) < 15 and max(fin) > 35;

Il doit y avoir moyen de faire quelque chose de propre avec des jointures de tables non ?

Cordialement,
Patrick Kolodziejczyk.

Note : Je pourrai le faire dans le code de mon application, mais le langage utilisé est plus que rebutant. (Au point où je préfère le faire en SQL !)

Edit : Je suppose qu'il faudrait utiliser un connect by, mais ma base de donnée tourne en boucle sur mon test =/

[Waldar]

Je n'ai pas bien compris le résultat que vous cherchez à obtenir avec par exemple (B, 15, 35) par rapport à vos données en entrées.

[kolodz]

Ce que j'aimerai obtenir de manière théorique, c'est :

Code x :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
('A', 10, 40)
('B', 10, 20)
('B', 31, 40)
('C', 10, 40)

Et après pouvoir le filtré par rapport à me borne. Ce qui dans le cas de (B, 15, 35) retourne un résultat vide. Car, il n'y a pas de plage continue qui contient 15 et 35.
L'idée est de pouvoir fusionné les plages adjacentes (ou chevauché), afin d'avoir qu'un qu'une seul plage a testé. Ce qui fait le min/max quand il n'y a pas de trou entre les plages pour un code.

Cordialement,
Patrick Kolodziejczyk.

[Waldar]

D'accord, en fait vous avez deux problèmes, je n'avais pas bien compris.

Pour le regroupement des valeurs on peut en effet utiliser une solution récursive :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
with matable (code, debut, fin) as
(
select 'A', 10, 20 from dual union all
select 'A', 21, 30 from dual union all
select 'A', 31, 40 from dual union all
select 'B', 10, 20 from dual union all
select 'B', 31, 40 from dual union all
select 'C', 10, 40 from dual
)
    select code, connect_by_root debut as debut, fin
      from matable t1
     where connect_by_isleaf = 1
start with not exists (select null
                         from matable t2
                        where t2.code = t1.code
                          and t2.fin  = t1.debut - 1)
connect by prior code = code
       and prior fin  = debut - 1;
 
CODE      DEBUT        FIN
---- ---------- ----------
A            10         40 
B            10         20 
B            31         40 
C            10         40

Il n'y a plus alors qu'à filtrer les bornes :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
    select code, connect_by_root debut as debut, fin
      from matable t1
     where connect_by_isleaf = 1
       and 15 between connect_by_root debut and fin
       and 35 between connect_by_root debut and fin
start with not exists (select null
                         from matable t2
                        where t2.code = t1.code
                          and t2.fin  = t1.debut - 1)
connect by prior code = code
       and prior fin  = debut - 1;
 
CODE      DEBUT        FIN
---- ---------- ----------
A            10         40 
C            10         40

[kolodz]

You are my King !

Sachant que mon problème se trouve en plus sur une jointure de table, j'ai tenté de la faire en même temps. Mais, c'est contre productif.
Au final, j'utilise ton code en remplacement matable par le select from table a ,table b where qui va bien et tout marche !!!

Grand merci à toi ! Je n'ai pas encore tout compris de la subtilité du connect_by_root, mais avec cette exemple fonctionnel, je devrais y arrivé.

Cordialement,
Patrick Kolodziejcyzk.

[Waldar]

connect_by_root <col> va conserver la valeur de <col> de la première étape de la récursion.

Le plus simple pour comprendre c'est de tout afficher :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
with matable (code, debut, fin) as
(
select 'A', 10, 20 from dual union all
select 'A', 21, 30 from dual union all
select 'A', 31, 40 from dual
)
    select level
         , code
         , connect_by_root debut as debut_root
         , connect_by_root fin   as fin_root
         , debut                 as debut_cur
         , fin                   as fin_cur
         , connect_by_isleaf
      from matable t1
start with fin = 20
connect by prior code = code
       and prior fin  = debut - 1;
 
     LEVEL CODE DEBUT_ROOT   FIN_ROOT  DEBUT_CUR    FIN_CUR CONNECT_BY_ISLEAF
---------- ---- ---------- ---------- ---------- ---------- -----------------
         1 A            10         20         10         20                 0 
         2 A            10         20         21         30                 0 
         3 A            10         20         31         40                 1

[kolodz]

Question de curiosité (mon cas métier exclu le cas) :

Si on a ce cas là :

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
with matable (code, debut, fin) as
(
select 'A', 10, 20 from dual union all
select 'A', 21, 30 from dual union all
select 'A', 25, 40 from dual union all
)

Le test t2.fin = t1.debut - 1 ne fonctionne plus. J'ai tenté de le remplacé naïvement avec t2.fin >= t1.debut - 1 and t2.debut < t1.debut. Cependant, cela ne donne pas le résultat espère. ('A', 10, 40).