Liaison récursive pour déterminer ensemble

Version imprimable

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:

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:

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

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:

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:

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:

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?

06/07/2007, 14h07
boutss

Oui c'est exactement çà.
Merci. :king:

Je n'ai pas pensé du tout à faire

Code:

prior b=b or prior l=l

.

Mais est-ce convertible en 9i ?
Car sans nocycle et connect_by_root ça semble diffcile.
06/07/2007, 14h35
laurentschneider

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

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:

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)

06/07/2007, 15h56
laurentschneider

moi je ferais un curseur dans plsql...

allez, bon week-end
06/07/2007, 15h59
boutss

Si tu as une solution, même en pl/sql je suis preneur.
Si ce n'est pas trop long ou compliqué bien sûr.

En fait je fais l'inverse, je sélectionne ceux qui ne sont archivable pour ensuite aller archiver les restants.

Merci à toi aussi, bon week !! ;)
06/07/2007, 16h52
laurentschneider

oui, bon week-end