[Oracle 10g] Requête hiérachique et fils masqués [Résolu]

[Doc.Fusion]

Bonjour,

Je cherche à savoir s'il est possible de retourner via Oracle le résultat d'un arbre en masquant un fils tout en gardant la relation.
Exemple :
Arbre initial :

Code :

1
2
3
4
1
|- 2
   |- 3
      |- 4

Contenu en base :

Code :

1
2
3
4
5
Père  Fils
----  ----
 1     2
 2     3
 3     4

Je décide de ne pas afficher 3 :

Code :

1
2
3
1
|- 2
   |- 4

Contenu souhaité retourné par requête :

Code :

1
2
3
4
Père  Fils
----  ----
 1     2
 2     4

Est-ce faisable ?

Si oui quelle est la requête permettant de faire ça ?

J'ai tenté en mettant la condition d'exclusion dans la clause where, les relations sont conservées mais le résultat retourné est...

Code :

1
2
3
4
Père  Fils
----  ----
 1     2
 3     4

Or 3 n'existe pas donc le lien est cassé.


Ma base de donnée est composée d'une table OBJET contenant les objets (pères et fils) et une table RELATION contenant uniquement l'idpere et l'idfils.
Mon boolean d'affichage est dans une troisième table CAPTEUR, la jointure se faisant sur un attribut codecapteur de OBJET.
Structures des tables (en souligné la clé primaire, en italique la clé étrangère) :
OBJET : identifiant (int) | nom (varchar) | codecapteur (varchar)
RELATION : idpere (int) | idfils (int)
CAPTEUR : codecapteur (varchar) | affichable (boolean)

[Waldar]

Je pense que votre cas de figure est un peu trop simplifié.

La solution qui a mon avis n'en est pas une car codée en dur :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
WITH relation AS
(
SELECT 1 AS idpere, 2 AS idfils FROM dual union ALL
SELECT 2          , 3           FROM dual union ALL
SELECT 3          , 4           FROM dual
)
    SELECT case idpere when 3 then lag(idfils) over(ORDER BY level) else idpere end AS idpere
         , idfils
      FROM relation
 WHERE idfils <> 3
start WITH idpere = 1 
connect BY prior idfils = idpere;
 
    IDPERE     IDFILS
---------- ----------
         1          2
         2          4

[Doc.Fusion]

Effectivement, le cas décrit est un peu trop simpliste.

Je précise donc :
Arbre initial :

Code :

1
2
3
4
106
|- 234
   |- 312
      |- 681

Contenu en base :

Code :

1
2
3
4
5
Père  Fils
----  ----
 106   234
 234   312
 312   681

Je ne dois pas afficher 312 car il possède le codecapteur "TOTO" qui a une valeur affichable à 0 :

Code :

1
2
3
106
|- 234
   |- 681

Contenu souhaité retourné par requête :

Code :

1
2
3
4
Père  Fils
----  ----
 106   234
 234   681

Il est possible que 234 ait aussi un codecapteur ("TITI") avec une valeur affichable à 0.
Du coup le contenu souhaité serait :

Code :

1
2
3
Père  Fils
----  ----
 106   681

La requête ne peut donc être écrite "en dur" puisque dépendant du codecapteur.


Cependant, merci de m'avoir fait découvrir la fonction lag qui devrait pouvoir m'aider...

[Rams7s]

Code :

1
2
3
4
5
WITH tmp AS(
   SELECT level AS pere, level+1 AS fils, mod(level,5) AS code_capteur FROM dual connect BY level<10)
SELECT  pere, fils,code_capteur FROM 
   (SELECT pere, fils, code_Capteur, rownum AS champ_tri FROM tmp connect BY pere= prior fils start WITH pere=1) t1
WHERE t1.code_capteur <>2 ORDER BY t1.champ_tri;

Avec ca pour le with:

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
TMP>WITH tmp AS(
  2     SELECT level AS pere, level+1 AS fils, mod(level,5) AS code_capteur FROM dual connect BY level<10)
  3        SELECT * FROM tmp;
 
      PERE       FILS CODE_CAPTEUR
---------- ---------- ------------
         1          2            1
         2          3            2
         3          4            3
         4          5            4
         5          6            0
         6          7            1
         7          8            2
         8          9            3
         9         10            4
 
9 rows selected.

Apres pour exclure les lignes qui ne vont pas en fonction du code capteur, il y a plein de solutions

[Doc.Fusion]

Merci pour ta réponse, Rams7s.

Toutefois, je suis un peu dubitatif. J'arrive à avoir un arbre retourné par requête.

Ce que je n'arrive pas à avoir, c'est un arbre avec des relations conservées malgré l'absence de certains noeuds.

Ci-joint le code SQL de création de ma base (simplifiée) :

Code :

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
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
 
CREATE TABLE CAPTEUR
(
  CAPTEURCODE       VARCHAR2(64 BYTE)           NOT NULL,
  AFFICHECAPTEUR    NUMBER
)
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;
 
 
ALTER TABLE CAPTEUR ADD (
  CONSTRAINT PK_CAPTEURCODE
 PRIMARY KEY
 (CAPTEURCODE));
 
CREATE TABLE OBJET
(
  OBJETID             NUMBER                    NOT NULL,
  OBJETDATE           DATE                      NOT NULL,
  CAPTEURCODE   VARCHAR2(64 BYTE)
)
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;
 
 
ALTER TABLE OBJET ADD (
  CONSTRAINT PK_OBJET
 PRIMARY KEY
 (OBJETID));
 
 
ALTER TABLE OBJET ADD (
  CONSTRAINT FK_OBJET_COMPOSE2_CAPTEUR 
 FOREIGN KEY (CAPTEURCODE) 
 REFERENCES CAPTEUR (CAPTEURCODE));
 
CREATE TABLE RELATION
(
  PERETACHEID  NUMBER,
  FILSTACHEID  NUMBER                           NOT NULL
)
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;
 
 
ALTER TABLE RELATION ADD (
  CONSTRAINT PK_TABLERELATION
 PRIMARY KEY
 (PERETACHEID, FILSTACHEID) DISABLE);
 
ALTER TABLE RELATION MODIFY CONSTRAINT PK_TABLERELATION RELY;
 
ALTER TABLE RELATION ADD (
  CONSTRAINT FK_RELATION_RELATION1_OBJET 
 FOREIGN KEY (PERETACHEID) 
 REFERENCES OBJET (OBJETID));
 
ALTER TABLE RELATION ADD (
  CONSTRAINT FK_RELATION_RELATION2_OBJET 
 FOREIGN KEY (FILSTACHEID) 
 REFERENCES OBJET (OBJETID));
 
ALTER TABLE RELATION MODIFY CONSTRAINT PK_TABLERELATION RELY;

[Rei Ichido]

Envoyé par Doc.Fusion

Merci pour ta réponse, Rams7s.

Toutefois, je suis un peu dubitatif. J'arrive à avoir un arbre retourné par requête.

Ce que je n'arrive pas à avoir, c'est un arbre avec des relations conservées malgré l'absence de certains noeuds.

Ce qu'on te suggère, c'est qu'il n'y a pas d'autre moyen de faire que de construire l'arbre, puis d'en exclure les élements indésirables, avec le filtre de ton choix.

En reprenant l'exemple entamé, pour cadrer avec ton point :

Code :

1
2
3
4
5
6
 
WITH tmp AS(
       SELECT level AS pere, level+1 AS fils, mod(level,5) AS code_capteur_pere, mod(level+1,5) AS code_capteur_fils FROM dual connect BY level<10)
          SELECT * FROM tmp
          WHERE  code_capteur_fils <> 3
              AND  code_capteur_pere <> 3;

[Doc.Fusion]

Envoyé par Rei Ichido

Ce qu'on te suggère, c'est qu'il n'y a pas d'autre moyen de faire que de construire l'arbre, puis d'en exclure les élements indésirables, avec le filtre de ton choix.

Mais en procédant ainsi, le fils perd l'identifiant de son père, non ?

Avec :

Code :

1
2
3
4
106
|- 234
   |- 312
      |- 681

Je vais obtenir en supprimant 312 :

Code :

1
2
3
4
Père  Fils
----  ----
 106   234
 312   681

Alors que je souhaiterai :

Code :

1
2
3
4
Père  Fils
----  ----
 106   234
 234   681

[Rei Ichido]

Ahhh, d'accord. J'avais mal compris le besoin, autant pour moi !
Je pense pouvoir te proposer quelque chose dans l'après-midi, selon ma disponibilité, si personne n'a donné de solution d'ici là

[Rei Ichido]

Je te propose ceci - j'ai fait ça vite fait, donc sans reprendre toutes tes tables, mais l'adaptation devrait être ok :

Code :

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
26
27
28
29
30
 
WITH t AS
     (  SELECT 1 AS objet_id,NULL AS id_pere, 'A' AS code FROM DUAL
        UNION ALL       
        SELECT 2 AS objet_id,1 AS id_pere, 'B' AS code FROM DUAL
        UNION ALL
        SELECT 3 AS objet_id,2 AS id_pere, 'C' AS code FROM DUAL
        UNION ALL
        SELECT 4 AS objet_id,3 AS id_pere, 'D'  AS code FROM DUAL        
        UNION ALL
        SELECT 5 AS objet_id,4 AS id_pere, 'E' AS code FROM DUAL
        UNION ALL    
        SELECT 10 AS objet_id,NULL AS id_pere, 'F' AS code FROM DUAL
        UNION ALL       
        SELECT 11 AS objet_id,10 AS id_pere, 'C' AS code FROM DUAL
        UNION ALL
        SELECT 12 AS objet_id,11 AS id_pere, 'G' AS code FROM DUAL
       )       
SELECT tree.objet_id
      ,lead(tree.objet_id) over (partition BY id_root ORDER BY code_filtre*tree.niveau) AS id_ancetre
  FROM  (
        SELECT t.objet_id
               ,DECODE(t.code,'C',0,1) AS code_filtre -- ici, mettre 0 pour les valeurs à éliminer 
               ,level AS niveau
               , connect_by_root t.objet_id AS id_root
          FROM t
         START WITH objet_id IN(5,12)
        CONNECT BY PRIOR id_pere=objet_id
        ) tree
 WHERE tree.code_filtre <> 0

[Doc.Fusion]

Merci beaucoup

J'avoue que je ne "vois" pas le résultat de la requête par TOAD :

Error at line 2
SQL statement doesn't return rows

Par contre, j'aurai juste une question bête : est-ce que dans le cas où le père et le grand-père sont filtrés, le fils à un lien avec l'arrière grand-père ?

Exemple :

Code :

1
2
3
4
106
|- 234
   |- 312
      |- 681

Si je supprime 234 et 312, vais-je obtenir :

Code :

1
2
3
Père  Fils
----  ----
 106   681

?

P.S.: j'ai cherché dans la FAQ comment afficher le résultat avec TOAD, mais je n'ai pas trouvé :/

[Rei Ichido]

Oui, concrètement il prend l'arbre, puis pour chaque ligne lui associe le premier élément non filtré en remontant.

Par contre ce que tu dis me semble bizarre : tu as adapté ma requête, ou bien tu l'as juste testée ? Je l'ai précisément faite sous TOAD

[Doc.Fusion]

J'ai fait un copier-coller basique dans une fenêtre Editor.

Si je l'execute avec "Execute Statement" ou avec "Execute as script":

Error at line 2
SQL statement doesn't return rows

[Rei Ichido]

Etrange ?
Je dois avouer ne pas avoir testé sur une 10g (je n'en n'ai pas sous la main présentement), mais autant que je me souvienne je n'ai rien utilisé qui ne soit pas disponible sur la 10g.

[Bluedeep]

J'ai fait un copier-coller dans Oracle SQL Developper et ça fonctionne parfaitement tel quel. (en 10g).

[salim11]

Salut,

Une autre méthode avec la version 10g(Model clause).

J'espère que cette solution reponds à ton besoin.

Solution:

Code :

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
26
27
28
29
 
WITH t AS
     ( SELECT idpere,idfils, affichable  FROM relation, capteur
       WHERE relation.idfils = capteur.codecapteur(+)
        start WITH idpere = 106 
        connect BY prior idfils = idpere), 
tt AS (SELECT idpere,idfils,grp
        FROM t
        model
        dimension BY ( row_number()over(partition BY 1 ORDER BY idpere)rn)
        measures(idpere,idfils, affichable, 1 grp)
(idfils[any]= case when affichable [cv()]=0 
                    then NULL 
                   when affichable [cv()] IS NULL 
                   then idfils[cv()] 
                   else idfils[cv()] end,
idpere[any]=  case when cv(rn)=1  
              then  idpere[cv()]  
              when idfils[cv()-1] IS NULL  
              then NULL   
              when affichable [cv()]=0 AND cv(rn)>1 
              then  NULL 
              else idpere[cv()]  end ,
grp[any]= case when idpere[cv()] IS NULL OR idfils[cv()] IS NULL  then nvl(grp[cv()-1],1) 
               else nvl(grp[cv()-1],0) +1 end  )
      )
SELECT min(idpere)idpere,max(idfils)idfils
  FROM tt 
GROUP BY grp;

Démonstration 1

Code :

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
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
 
SELECT *
  FROM relation;
 
 
    IDPERE     IDFILS
---------- ----------
       106        234
       234        312
       312        681
       681        700
       700        950
 
 
5 rows selected.
 
SELECT *
  FROM capteur;
 
CODECAPTEUR AFFICHABLE
----------- ----------
        234          0
        312          0
 
 
2 rows selected.
 
 
WITH t AS
     ( SELECT idpere,idfils, affichable  FROM relation, capteur
       WHERE relation.idfils = capteur.codecapteur(+)
        start WITH idpere = 106 
        connect BY prior idfils = idpere), 
tt AS (SELECT idpere,idfils,grp
        FROM t
        model
        dimension BY ( row_number()over(partition BY 1 ORDER BY idpere)rn)
        measures(idpere,idfils, affichable, 1 grp)
(idfils[any]= case when affichable [cv()]=0 
                    then NULL 
                   when affichable [cv()] IS NULL 
                   then idfils[cv()] 
                   else idfils[cv()] end,
idpere[any]=  case when cv(rn)=1  
              then  idpere[cv()]  
              when idfils[cv()-1] IS NULL  
              then NULL   
              when affichable [cv()]=0 AND cv(rn)>1 
              then  NULL 
              else idpere[cv()]  end ,
grp[any]= case when idpere[cv()] IS NULL OR idfils[cv()] IS NULL  then nvl(grp[cv()-1],1) 
               else nvl(grp[cv()-1],0) +1 end  )
      )
SELECT min(idpere)idpere,max(idfils)idfils
  FROM tt 
GROUP BY grp;
 
    IDPERE     IDFILS
---------- ----------
       106        681
       681        700
       700        950
 
 
3 rows selected.

Démonstration 2

Code :

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
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
 
SELECT *
  FROM relation;
 
    IDPERE     IDFILS
---------- ----------
       106        234
       234        312
       312        681
       681        700
       700        950
 
 
5 rows selected.
 
SELECT *
  FROM capteur;
 
CODECAPTEUR AFFICHABLE
----------- ----------
        234          0
        312          0
        681          0
 
 
3 rows selected.
 
 
 
WITH t AS
     ( SELECT idpere,idfils, affichable  FROM relation, capteur
       WHERE relation.idfils = capteur.codecapteur(+)
        start WITH idpere = 106 
        connect BY prior idfils = idpere), 
tt AS (SELECT idpere,idfils,grp
        FROM t
        model
        dimension BY ( row_number()over(partition BY 1 ORDER BY idpere)rn)
        measures(idpere,idfils, affichable, 1 grp)
(idfils[any]= case when affichable [cv()]=0 
                    then NULL 
                   when affichable [cv()] IS NULL 
                   then idfils[cv()] 
                   else idfils[cv()] end,
idpere[any]=  case when cv(rn)=1  
              then  idpere[cv()]  
              when idfils[cv()-1] IS NULL  
              then NULL   
              when affichable [cv()]=0 AND cv(rn)>1 
              then  NULL 
              else idpere[cv()]  end ,
grp[any]= case when idpere[cv()] IS NULL OR idfils[cv()] IS NULL  then nvl(grp[cv()-1],1) 
               else nvl(grp[cv()-1],0) +1 end  )
      )
SELECT min(idpere)idpere,max(idfils)idfils
  FROM tt 
GROUP BY grp;
 
    IDPERE     IDFILS
---------- ----------
       106        700
       700        950
 
 
2 rows selected.

Cordialement Salim.

[salim11]

Salut,

Deuxième version, solution améliorée.

Code :

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
26
27
28
29
30
31
32
33
34
 
WITH t AS
     ( SELECT     idpere, idfils, affichable, connect_by_root idfils AS id
      FROM relation, capteur
     WHERE relation.idfils = capteur.codecapteur(+)
START WITH idpere IS NULL
CONNECT BY PRIOR idfils = idpere), 
tt AS (SELECT idpere,idfils,grp,i
        FROM t
        model
        partition BY (id i)
        dimension BY ( row_number()over(partition BY id ORDER BY idpere nulls first)rn)
        measures(idpere,idfils, affichable, 1 grp)
(idfils[any]= case when affichable [cv()]=0 
                    then NULL 
                   when affichable [cv()] IS NULL 
                   then idfils[cv()] 
                   else idfils[cv()] end,
idpere[any]=  case when cv(rn)=1  
              then  idpere[cv()]  
              when idfils[cv()-1] IS NULL  
              then NULL 
              when idfils[cv()-1] IS NOT NULL  
              then idpere[cv()] 
              when affichable [cv()]=0 AND cv(rn)>1 
              then  NULL 
              else idpere[cv()]  end ,
grp[any]= case when idpere[cv()] IS NULL OR idfils[cv()] IS NULL  then nvl(grp[cv()-1],1) 
               else nvl(grp[cv()-1],0) +1 end  )
      )
SELECT min(idpere)idpere,max(idfils)idfils
  FROM tt 
GROUP BY grp,i
ORDER BY i,grp;

Code :

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
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
 
SELECT     idpere, idfils, affichable, connect_by_root idfils AS id
      FROM relation, capteur
     WHERE relation.idfils = capteur.codecapteur(+)
START WITH idpere IS NULL
CONNECT BY PRIOR idfils = idpere;
 
 
    IDPERE     IDFILS AFFICHABLE         ID
---------- ---------- ---------- ----------
                  106                   106
       106        234          0        106
       234        312          0        106
       312        681          0        106
       681        700                   106
       700        950                   106
                  200                   200
       200        250          0        200
       250        280                   200
 
 
9 rows selected.
 
SELECT *
  FROM capteur;
 
CODECAPTEUR AFFICHABLE
----------- ----------
        234          0
        312          0
        250          0
        681          0
 
 
4 rows selected.
 
WITH t AS
     ( SELECT     idpere, idfils, affichable, connect_by_root idfils AS id
      FROM relation, capteur
     WHERE relation.idfils = capteur.codecapteur(+)
START WITH idpere IS NULL
CONNECT BY PRIOR idfils = idpere), 
tt AS (SELECT idpere,idfils,grp,i
        FROM t
        model
        partition BY (id i)
        dimension BY ( row_number()over(partition BY id ORDER BY idpere nulls first)rn)
        measures(idpere,idfils, affichable, 1 grp)
(idfils[any]= case when affichable [cv()]=0 
                    then NULL 
                   when affichable [cv()] IS NULL 
                   then idfils[cv()] 
                   else idfils[cv()] end,
idpere[any]=  case when cv(rn)=1  
              then  idpere[cv()]  
              when idfils[cv()-1] IS NULL  
              then NULL 
              when idfils[cv()-1] IS NOT NULL  
              then idpere[cv()] 
              when affichable [cv()]=0 AND cv(rn)>1 
              then  NULL 
              else idpere[cv()]  end ,
grp[any]= case when idpere[cv()] IS NULL OR idfils[cv()] IS NULL  then nvl(grp[cv()-1],1) 
               else nvl(grp[cv()-1],0) +1 end  )
      )
SELECT min(idpere)idpere,max(idfils)idfils
  FROM tt 
GROUP BY grp,i
ORDER BY i,grp;
 
 
    IDPERE     IDFILS
---------- ----------
       106        700
       700        950
       200        280
 
 
3 rows selected.

Cordialement Salim.

[Doc.Fusion]

Merci beaucoup pour toutes vos réponses.

Pour l'anecdote, apparemment mon Toad utilise un driver 8.1 pour s connecter en 10G, d'où les problèmes avec les requêtes en WITH...

Je vais marquer le problème en résolu pour l'instant et si besoin viendrai le ré-éditer.

[salim11]

Salut,

Tu peux le tester avec sqlplus.

[salim11]

Salut,

J'ai amélioré ma requête parce que celle du poste précédant ne fonctionnée pas pour cette exemple.

Code :

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
26
27
28
29
 
WITH t AS
     ( SELECT     idpere, idfils, affichable, connect_by_root idfils AS id
      FROM relation, capteur
     WHERE relation.idfils = capteur.codecapteur(+)
START WITH idpere IS NULL
CONNECT BY PRIOR idfils = idpere), 
tt AS (SELECT idpere,idfils,grp,i,affichable
        FROM t
        model
        partition BY (id i)
        dimension BY ( row_number()over(partition BY id ORDER BY idpere nulls first)rn)
        measures(idpere,idfils, affichable, 1 grp)
(idfils[any]= case when affichable [cv()]=0 
                    then NULL 
              else idfils[cv()] end,
idpere[any] = case when cv(rn)=1  OR (idfils[cv()-1] IS NOT NULL  AND idfils[cv()-1]=idpere[cv()])
              then  idpere[cv()]  
              when idfils[cv()-1] IS NULL OR (affichable [cv()]=0 AND cv(rn)>1) 
              then NULL               
              else idpere[cv()]  end ,
grp[any]    = case when idpere[cv()] IS NULL OR idfils[cv()-1] IS NULL  OR cv(rn)-1=1
              then nvl(grp[cv()-1],1) 
              else nvl(grp[cv()-1],0) +1 end  )
      )
SELECT min(idpere)idpere,max(idfils)idfils
  FROM tt 
GROUP BY grp,i
ORDER BY i,grp;

Demo

Code :

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
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
 
SELECT     idpere, idfils, affichable, connect_by_root idfils AS id
      FROM relation, capteur
     WHERE relation.idfils = capteur.codecapteur(+)
START WITH idpere IS NULL
CONNECT BY PRIOR idfils = idpere;
 
    IDPERE     IDFILS AFFICHABLE         ID
---------- ---------- ---------- ----------
                  106                   106
       106        234          0        106
       234        312          0        106
       312        681                   106
       681        700          0        106
       700        950                   106
                  200                   200
       200        250          0        200
       250        280                   200
 
 
9 rows selected.
 
CODECAPTEUR AFFICHABLE
----------- ----------
        234          0
        250          0
        700          0
        312          0
 
 
4 rows selected.
 
WITH t AS
     ( SELECT     idpere, idfils, affichable, connect_by_root idfils AS id
      FROM relation, capteur
     WHERE relation.idfils = capteur.codecapteur(+)
START WITH idpere IS NULL
CONNECT BY PRIOR idfils = idpere), 
tt AS (SELECT idpere,idfils,grp,i,affichable
        FROM t
        model
        partition BY (id i)
        dimension BY ( row_number()over(partition BY id ORDER BY idpere nulls first)rn)
        measures(idpere,idfils, affichable, 1 grp)
(idfils[any]= case when affichable [cv()]=0 
                    then NULL 
              else idfils[cv()] end,
idpere[any] = case when cv(rn)=1  OR (idfils[cv()-1] IS NOT NULL  AND idfils[cv()-1]=idpere[cv()])
              then  idpere[cv()]  
              when idfils[cv()-1] IS NULL OR (affichable [cv()]=0 AND cv(rn)>1) 
              then NULL               
              else idpere[cv()]  end ,
grp[any]    = case when idpere[cv()] IS NULL OR idfils[cv()-1] IS NULL  OR cv(rn)-1=1
              then nvl(grp[cv()-1],1) 
              else nvl(grp[cv()-1],0) +1 end  )
      )
SELECT min(idpere)idpere,max(idfils)idfils
  FROM tt 
GROUP BY grp,i
ORDER BY i,grp;
 
IDPERE     IDFILS
---------- ----------
       106        681
       681        950
       200        280
 
 
3 rows selected.