Requête hiérarchique avec même état [Résolu]

[Hobbi1]

Bonjour,

J'ai une table qui contient des dossiers avec une référence sur elle-même car un dossier peut être le père d'un ou plusieurs fils (un fils n'ayant qu'un seul père). Ce qui est représenté par un colonne ID et une autre ID_PERE.

Je dois extraire tous les dossiers clos. Si un dossier a des fils ou est le père de fils, il faut que tous les dossiers soient clos. Si un seul de ces dossiers n'est pas clos, on n'extraie rien.

Je n'arrive pas à faire ça avec une requête. Je me dis que ça doit être faisable avec une fonction analytique mais comme je ne connais pas, j'ai besoin de votre aide.

Merci.

[Waldar]

Regardez plutôt du côté de la syntaxe CONNECT BY

[Hobbi1]

Merci.
J'avais commencé à chercher dans ce sens mais je n'arrive pas à n'extraire que lorsque tous les dossiers liés sont terminé.

Code :

1
2
3
4
5
SELECT *
FROM dossier
WHERE ETAT_DOSSIER = 'TERMINE'
connect BY prior ID_DOSSIER = ID_DOSSIER_PERE
start WITH ID_DOSSIER_PERE IS NULL

Par exemple, j'ai un dossier père qui est terminé et qui a 3 fils dont 1 est ouvert.
Cette requête me retourne 3 lignes : le père et les 2 fils terminé
Mais dans ce cas, je veux que la requête ne retourne rien car un des dossiers liés n'est pas terminé.

[Rei Ichido]

Tu vas être obligé de passer par 1ère requête hiérarchique sans filtre sur le statut, puis de faire à partir de ce résultat un filtre sur les objets ayant le même père mais qui n'ont pas le même statut ...

EDIT : ça peut donner quelque chose du genre :

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
 
WITH dossier AS
( SELECT 1 AS ID_DOSSIER, NULL AS ID_DOSSIER_PERE, 'T' AS ETAT_DOSSIER FROM DUAL
  UNION ALL
  SELECT 11 AS ID_DOSSIER, 1 AS ID_DOSSIER_PERE, 'T' AS ETAT_DOSSIER FROM DUAL
  UNION ALL
  SELECT 12 AS ID_DOSSIER, 1 AS ID_DOSSIER_PERE, 'T' AS ETAT_DOSSIER FROM DUAL
  UNION ALL
  SELECT 2 AS ID_DOSSIER, NULL AS ID_DOSSIER_PERE, 'T' AS ETAT_DOSSIER FROM DUAL
  UNION ALL
  SELECT 21 AS ID_DOSSIER, 2 AS ID_DOSSIER_PERE, 'T' AS ETAT_DOSSIER FROM DUAL
  UNION ALL
  SELECT 22 AS ID_DOSSIER, 2 AS ID_DOSSIER_PERE, 'E' AS ETAT_DOSSIER FROM DUAL
)
SELECT id_dossier, id_dossier_pere, etat_dossier FROM (
SELECT d2.id_dossier, d2.id_dossier_pere, d2.etat_dossier, d2.id_root
      ,MIN(DECODE(ETAT_DOSSIER,'T',1,0)) OVER (PARTITION BY ID_ROOT) AS MIN_ETAT_DOSSIER
  FROM
    (
    SELECT d.id_dossier, d.id_dossier_pere, d.etat_dossier
          ,CONNECT_BY_ROOT ID_DOSSIER AS ID_ROOT
        FROM dossier d
    --WHERE ETAT_DOSSIER = 'T'
    connect BY prior ID_DOSSIER = ID_DOSSIER_PERE
    start WITH ID_DOSSIER_PERE IS NULL
    ) d2
   )
WHERE min_etat_dossier = 1

[Hobbi1]

Merci bien.

J'avais trouvé une solution un peu différente. Mais la mienne m'a l'air un peu plus lourde.

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
WITH dossier AS
( SELECT 1 AS ID_DOSSIER, NULL AS ID_DOSSIER_PERE, 'T' AS ETAT_DOSSIER FROM DUAL
  UNION ALL
  SELECT 11 AS ID_DOSSIER, 1 AS ID_DOSSIER_PERE, 'T' AS ETAT_DOSSIER FROM DUAL
  UNION ALL
  SELECT 12 AS ID_DOSSIER, 1 AS ID_DOSSIER_PERE, 'T' AS ETAT_DOSSIER FROM DUAL
  UNION ALL
  SELECT 2 AS ID_DOSSIER, NULL AS ID_DOSSIER_PERE, 'T' AS ETAT_DOSSIER FROM DUAL
  UNION ALL
  SELECT 21 AS ID_DOSSIER, 2 AS ID_DOSSIER_PERE, 'T' AS ETAT_DOSSIER FROM DUAL
  UNION ALL
  SELECT 22 AS ID_DOSSIER, 2 AS ID_DOSSIER_PERE, 'E' AS ETAT_DOSSIER FROM DUAL
)
SELECT ID_DOSSIER, ID_DOSSIER_PERE , ETAT_DOSSIER , tot_doss_lies, tot_doss_clos
FROM (
SELECT ID_DOSSIER, ID_DOSSIER_PERE , ETAT_DOSSIER
, count(*) over (partition BY ID_DOSSIER_PERE) tot_doss_lies
, count(*) over (partition BY ID_DOSSIER_PERE, ETAT_DOSSIER) tot_doss_clos
FROM (
SELECT ID_DOSSIER, NVL(ID_DOSSIER_PERE, ID_DOSSIER) ID_DOSSIER_PERE, ETAT_DOSSIER
FROM dossier
start WITH ID_DOSSIER_PERE IS NULL
connect BY prior ID_DOSSIER = ID_DOSSIER_PERE 
ORDER BY 1, ETAT_DOSSIER
)
)
WHERE tot_doss_lies=tot_doss_clos;

[Rei Ichido]

Et il faut rajouter une condition sur l'état du dossier final ; le nom "tot_dossier_clos" prête à confusion, il s'agit en fait du nombre de dossiers ayant le même statut. Un père avec tous les fils ayant un statut autre que Terminé serait sélectionné.

[Hobbi1]

Oui très juste.
C'est pour ça que je préfère ta solution
Merci bien.