Liaison récursive pour déterminer ensemble

**boutss** · 04/07/2007, 14h43

Bonjour,

Alors je fais un appel à Laurent Schneider, qui me suis aimablement depuis quelques temps. Mais si quelqu'un à une idée, elle est la bienvenue.

J'ai une requête SQL qui me retourne les relations qui lient des objets.

Plus clairement j'ai des bordereaux qui sont liés par des lignes de compte.
Mais cela peut faire B1->L1->L2->B2->L3->L4->B3
Ce qui donne que le bordereau B1 et lié à B2 et B3.

La requête

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
select
lc.ID ligneDeCompteLettree, BOR.ID Bordereau, BOR.ETATOBJET,
from TBORDEREAUOPERATIONFINANCIERE bor, 
	   TDECAISSEMENT enc,  
	   TAFFECTATIONOPERATION aff,
	   TLIGNEDECOMPTE lc
where  mod(bor.etatobjet, 2)=0
and bor.id=enc.BORDEREAU_ID and mod(enc.etatobjet, 2)=0
and aff.OPERATION_ID=enc.ID and mod(aff.etatobjet, 2)=0
and (lc.GENERATEUR_ID=aff.id or aff.LIGNECOMPTELETTREE_ID=lc.id)
and lc.LIGNEDECOMPTE_ID is null
and mod(lc.etatobjet, 2)=0

Ma requête me retourne :

LigneDeCompte - Bordereau - EtatBordereau
L1 - B1 - 2
L2 - B1 - 2
L2 - B2 - 2
L3 - B2 - 2
L3 - B3 - 0

Il faut que je détermine des ensembles de bordereaux, et il faut que l'ensemble des bordereaux ait l'étatobjet à 2.

J'essaie d'utiliser les fonctions analytiques que je maitrise mieux, par ailleurs :

Code :

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

1
2
3
4
5
min (BOR.ETATOBJET) over (partition by lc.ID) mini, 
max (BOR.ETATOBJET) over (partition by lc.ID) maxi,
min (BOR.ETATOBJET) over (partition by BOR.ID) miniBOR, 
max (BOR.ETATOBJET) over (partition by BOR.ID) maxiBOR,
count(*) over(partition by lc.ID order by lc.ID) CNT

Mais le problème, je n'arrive pas à déterminer d'ensemble car c'est recursif, une ligne de compte peut me ramener plusieurs bordereaux et un bordereau peut me ramener plusieurs ligne de compte.

Avez vous une idée ?

Merci.
Boutss

**laurentschneider** · 06/07/2007, 12h58

je ne lis pas tous les messages... mais j'ai fini par trouvé le tien!

bon, d'abord la solution que je te donne est en 10g

je ne suis pas sûr d'avoir compris le résulat désiré, voici donc un exemple :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
 
create table t as (
  select 'L1' l,'B1' b,2 e from dual union all
  select 'L2','B1',2 from dual union all
  select 'L2','B2',2 from dual union all
  select 'L3','B2',2 from dual union all
  select 'L9','B9',2 from dual union all
  select 'L8','B9',2 from dual union all
  select 'L7','B7',2 from dual union all
  select 'L7','B8',2 from dual union all
  select 'L3','B3',0 from dual );

Code :

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

1
2
3
4
5
6
7
8
9
10
11
 
select distinct b,root
from (
     select b, max(replace(p,'/2')) over (partition by b) m,root from (
        select connect_by_root b root,b,sys_connect_by_path(e,'/') p
        from t
        connect by nocycle prior b=b or prior l=l
      )
)
where m is null
order by b,root;

Code :

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

1
2
3
4
5
6
7
8
 
B  RO
-- --
B7 B7
B7 B8
B8 B7
B8 B8
B9 B9

le b1-b2-b3 n'apparait pas car l'etat de b3 est à 0

Ok?

**boutss** · 06/07/2007, 14h07

Oui c'est exactement çà.
Merci.

Je n'ai pas pensé du tout à faire

Code :

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

prior b=b or prior l=l

.

Mais est-ce convertible en 9i ?
Car sans nocycle et connect_by_root ça semble diffcile.

**laurentschneider** · 06/07/2007, 14h35

ouais, en 9i c'est beaucoup moins simple. je pense qu'il vaut mieux passer par plsql.

**boutss** · 06/07/2007, 15h35

Alors j'ai trouvé une solution, mais pas optimale à mon goût.
Mais c'est la seule pour le moment.

Je récupère tous les bordereaux, non archivable, à 1 niveau, les tagge non archivable et boucle ainsi de suite pour traiter tous les niveaux.

J'utilise les fonctions analytiques avec le min, max.

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
INSERT INTO tmp_batch_extraction
   (SELECT DISTINCT (bor_id), ttable, ''
               FROM (SELECT lc_id, bor_id, etatobjet, ttable,
                            MIN (etatobjet) OVER (PARTITION BY lc_id) mini,
                            MAX (etatobjet) OVER (PARTITION BY lc_id) maxi
                       FROM (SELECT lc_id, bor_id, etatobjet, ttable
                               FROM (SELECT lc.ID lc_id, bor.ID bor_id,
                                            bor.etatobjet,
                                            'TBORDEREAUOPERATIONFINANCIERE'
                                                                       ttable
                                       FROM tbordereauoperationfinanciere bor,
                                            tencaissement opfi,
                                            taffectationoperation aff,
                                            tlignedecompte lc
                                      WHERE MOD (bor.etatobjet, 2) = 0
                                        AND bor.ID = opfi.bordereau_id
                                        AND MOD (opfi.etatobjet, 2) = 0
                                        AND aff.operation_id = opfi.ID
                                        AND MOD (aff.etatobjet, 2) = 0
                                        AND (   lc.generateur_id = aff.ID
                                             OR aff.lignecomptelettree_id =
                                                                         lc.ID
                                            )
                                        AND lc.lignedecompte_id IS NULL
                                        AND MOD (lc.etatobjet, 2) = 0
                                     UNION ALL
                                     SELECT lc.ID lc_id, bor.ID bor_id,
                                            bor.etatobjet,
                                            'TBORDEREAUOPERATIONFINANCIERE'
                                                                       ttable
                                       FROM tbordereauoperationfinanciere bor,
                                            tdecaissement opfi,
                                            taffectationoperation aff,
                                            tlignedecompte lc
                                      WHERE MOD (bor.etatobjet, 2) = 0
                                        AND bor.ID = opfi.bordereau_id
                                        AND MOD (opfi.etatobjet, 2) = 0
                                        AND aff.operation_id = opfi.ID
                                        AND MOD (aff.etatobjet, 2) = 0
                                        AND (   lc.generateur_id = aff.ID
                                             OR aff.lignecomptelettree_id =
                                                                         lc.ID
                                            )
                                        AND lc.lignedecompte_id IS NULL
                                        AND MOD (lc.etatobjet, 2) = 0
                                     UNION ALL
                                     SELECT lc.ID, opfi.ID, opfi.etatobjet,
                                            'TOPERATIONDIVERSE'
                                       FROM toperationdiverse opfi,
                                            taffectationoperation aff,
                                            tlignedecompte lc
                                      WHERE MOD (opfi.etatobjet, 2) = 0
                                        AND opfi.ID = aff.operation_id
                                        AND MOD (aff.etatobjet, 2) = 0
                                        AND (   lc.generateur_id = aff.ID
                                             OR aff.lignecomptelettree_id =
                                                                         lc.ID
                                            )
                                        AND lc.lignedecompte_id IS NULL
                                        AND MOD (lc.etatobjet, 2) = 0) ensemble) analyse)
              WHERE (mini <> 2 OR maxi <> 2) AND etatobjet = 2)

**laurentschneider** · 06/07/2007, 15h56

moi je ferais un curseur dans plsql...

allez, bon week-end

Liaison récursive pour déterminer ensemble

SQL Oracle

Vue hybride

Discussions similaires

Partager

Partager