Discussion :

[boutss]

Bonjour à tous,

Petit exercice pour les experts ... ^^

J'ai une table sous forme d'arbre (données hiérarchiques).

Je souhaiterai récupérer toutes les branches, pour les mettre à jour, en partant d'un point connu (start with) et s'assurant que tous les enfants soient du même état.
En sachant qu'une branche peut en développer d'autres.

Exemple:

1 - 1.1 - 1.1.1
1 - 1.2 - 1.2.1
1 - 1.3

Si un seul des éléments n'est pas dans le même état, je ne dois pas les récupérer.

Code :

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

1
2
3
4
5
select SYS_CONNECT_BY_PATH(id, ' - ') "Path", Level
 from TLIGNEDECOMPTE LC
start with LC.LIGNEDECOMPTE_ID is null and lc.etatobjet=4 
 connect by LC.LIGNEDECOMPTE_ID = prior LC.ID
 and etatobjet=2

En vous remerciant d'avance.
Boutss

[boutss]

Si je ne suis pas très clair, n'hésitez pas à me poser des questions.

[laurentschneider]

peux-tu donner un exemple de données (avec create table et insert) et un exemple de resultat attendu afin de nous indiquer ton problème.

N'oublie pas d'indiquer ta version

tu peux aussi partir de

Code :

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

1
2
3
4
5
6
7
8
9
10
 
select r
from (
      select connect_by_root id r, etatobjet
      from tlignedecompte
      connect by lignedecompte_id=prior id
)
having max(etatobjet)=2 and min(etatobjet)=2
group by r
order by 1;

[boutss]

Ah oui désolé, je voulais l'indiquer car de plus il y a des fonctionnalités qui n'existe pas dans la 9i concernant la lecture des données hiérarchiques...

Je suis donc en 9i.

Je vous prépare ça et vous le transmet.

Tu pourrais décrire ta requête, je ne comprend pas tout ?

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
select r
from (
que retournes "connect_by_root id r" ?
      select connect_by_root id r, etatobjet
      from tlignedecompte
      connect by lignedecompte_id=prior id
)
à condition que etatobjet=2 ?
having max(etatobjet)=2 and min(etatobjet)=2
Tu regroupes par id ?
group by r
order by 1;

[boutss]

Je souhaite récupérer la branche "3" car c'est la seule complète qui a l'attribut etatobjet=2.

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
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
drop table TEST;
 
create table TEST
(
 ID VARCHAR2(25),
 ETATOBJET NUMBER(1),
 NOM VARCHAR2(25),
 PARENT VARCHAR2(25),
 constraints PK_AGENT PRIMARY KEY (ID)
);
 
drop table TEST;
 
create table TEST
(
 ID VARCHAR2(25),
 ETATOBJET NUMBER(1),
 NOM VARCHAR2(25),
 PARENT VARCHAR2(25),
 constraints PK_AGENT PRIMARY KEY (ID)
);
 
INSERT INTO TEST ( ID, ETATOBJET, NOM, PARENT ) VALUES ( 
'1', 2, '1', NULL); 
INSERT INTO TEST ( ID, ETATOBJET, NOM, PARENT ) VALUES ( 
'2', 2, '2', NULL); 
INSERT INTO TEST ( ID, ETATOBJET, NOM, PARENT ) VALUES ( 
'3', 2, '3', NULL); 
INSERT INTO TEST ( ID, ETATOBJET, NOM, PARENT ) VALUES ( 
'4', 2, '4', NULL); 
INSERT INTO TEST ( ID, ETATOBJET, NOM, PARENT ) VALUES ( 
'5', 0, '5', NULL); 
INSERT INTO TEST ( ID, ETATOBJET, NOM, PARENT ) VALUES ( 
'6', 0, '1.1', '1'); 
INSERT INTO TEST ( ID, ETATOBJET, NOM, PARENT ) VALUES ( 
'6', 2, '1.2', '1'); 
INSERT INTO TEST ( ID, ETATOBJET, NOM, PARENT ) VALUES ( 
'8', 2, '1.1.1', '7'); 
INSERT INTO TEST ( ID, ETATOBJET, NOM, PARENT ) VALUES ( 
'9', 2, '2.1', '2'); 
INSERT INTO TEST ( ID, ETATOBJET, NOM, PARENT ) VALUES ( 
'10', 0, '2.2', '2'); 
INSERT INTO TEST ( ID, ETATOBJET, NOM, PARENT ) VALUES ( 
'11', 2, '2.1.1', '9'); 
INSERT INTO TEST ( ID, ETATOBJET, NOM, PARENT ) VALUES ( 
'12', 2, '3.1', '3'); 
INSERT INTO TEST ( ID, ETATOBJET, NOM, PARENT ) VALUES ( 
'13', 2, '3.2', '3'); 
INSERT INTO TEST ( ID, ETATOBJET, NOM, PARENT ) VALUES ( 
'14', 2, '3.3', '3'); 
INSERT INTO TEST ( ID, ETATOBJET, NOM, PARENT ) VALUES ( 
'15', 2, '3.3.1', '14'); 
INSERT INTO TEST ( ID, ETATOBJET, NOM, PARENT ) VALUES ( 
'16', 2, '4.1', '4'); 
INSERT INTO TEST ( ID, ETATOBJET, NOM, PARENT ) VALUES ( 
'17', 0, '4.1.1', '16'); 
INSERT INTO TEST ( ID, ETATOBJET, NOM, PARENT ) VALUES ( 
'18', 2, '4.1.1.1', '17'); 
INSERT INTO TEST ( ID, ETATOBJET, NOM, PARENT ) VALUES ( 
'19', 2, '4.2', '4'); 
INSERT INTO TEST ( ID, ETATOBJET, NOM, PARENT ) VALUES ( 
'20', 2, '4.2.1', '19'); 
COMMIT;

Pour visualiser :

Code :

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

1
2
3
select sys_connect_by_path(NOM, ' | ') from test 
start with parent is null and etatobjet=2
connect by prior id = parent

[laurentschneider]

connect_by_root c'est du 10g

en 9i, je ferais un truc du style

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
 
select
    substr(nom,2,instr(nom||'/','/',2)-2) nom
from (
    select
        sys_connect_by_path(NOM, '/') nom,
        sys_connect_by_path(etatobjet, '/') etat
    from test
    start with parent is null and etatobjet=2
    connect by prior id = parent
    )
group by
    substr(nom,2,instr(nom||'/','/',2)-2)
having
    max(replace(etat,'/2')) is null;
 
NOM
---
3

[laurentschneider]

soit connect_by_path (un peu moche)

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
 
select nom from (
  select
    nom,
	max(replace(p_etatobjet,'/2')) over (partition by substr(p_nom,2,instr(p_nom||'/','/',2)-2)) x
  from (
    select
        nom, 
		sys_connect_by_path(NOM, '/') p_nom,
        sys_connect_by_path(etatobjet, '/') p_etatobjet
    from test
    start with parent is null and etatobjet=2
    connect by prior id = parent
    )
) where x is null;
 
NOM
------
3
3.1
3.3.1
3.3
3.2

soit tu écris une fonction en plsql

[boutss]

Je découvre Les fonctions analytiques !!
Je regarde tout ça et tiens au courant.

[boutss]

A quoi sert-il d'utiliser une fonction analytique si on utilise pas une clause fenêtrage ?
Pour les performances ?

[laurentschneider]

Il y a beaucoup d'exemple de over() sans window.

ex:
les employés de emp avec leur salaire par rapport au salaire du département et par rapport au salaire de tous les employés

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
26
 
select deptno,ename, sal, 
  to_char(
    ratio_to_report(sal) over (partition by deptno)
	*100,'990')||'%' pct_dept, 
  to_char(
	ratio_to_report(sal) over ()
	*100,'990')||'%' pct
from emp; 
 
    DEPTNO ENAME             SAL PCT_D PCT
---------- ---------- ---------- ----- -----
        10 CLARK            2450   28%    8%
        10 KING             5000   57%   17%
        10 MILLER           1300   15%    4%
        20 JONES            2975   27%   10%
        20 FORD             3000   28%   10%
        20 ADAMS            1100   10%    4%
        20 SMITH             800    7%    3%
        20 SCOTT            3000   28%   10%
        30 WARD             1250   13%    4%
        30 TURNER           1500   16%    5%
        30 ALLEN            1600   17%    6%
        30 JAMES             950   10%    3%
        30 BLAKE            2850   30%   10%
        30 MARTIN           1250   13%    4%

[boutss]

Bon, et bien cela à l'air de fonctionner.

Cela démontre une belle maitrise de ta part et cela m'a permis d'affiner mes connaissances.

Tu l'avais déjà construite cette requête ou tu l'as faite sur le tas ? (je suis bien curieux )