Etat avec des sous-totaux [Résolu]

[OraAide]

bonjour,
J'ai la table compte(numero,debit,credit), les numeros de comptes sont sur 5 chiffres :

numero debit credit
10000 ... ....
10001
..
20000
...
Je veux realiser un etat de sortie qui contient les comptes, leurs credit et debit ainsi que les sous comptes (d'ordre 4, 3, 2, 1) et leur totaux,
c'est a dire dans le rapport doit figurer
un etat semblable a

numero debit credit
10000 ... ....
10001
..
1000
10010
..
1001
10020
..
1002
..

100
..
10
..
1
Merci.

[McM]

tu le fais en SQL

Code :

1
2
3
4
SELECT trunc(numero / 10000) AS ordre1, trunc(numero / 1000) ordre2,
trunc(numero / 100) ordre3, trunc(numero / 10) ordre4, 
numero
FROM ...

Puis tu fais une groupe (repeating frame) sous report par ordre.

[OraAide]

peux-tu me donner plus d'explications, car ce que je veux est tous les comptes , sous comptes soient affiches avec leurs sous totaux dans une meme colonne, tel qu'indique dans mon premier enonce.

[McM]

C'est du report : Tu peux afficher un element à la fin d'une repeating qui fait la somme des données de cette repeating.

Avec des repeating ordre1, ordre2, ordre3, ordre4, compte et des sommes des débits/crédits pour chaque ordre, tu as tous tes éléments.

[jamjam19]

Dans ton SQL statment tu met par exemple :
select substr(compte, 1, 1) cpt1,
substr(compte, 1, 2) cpt2,
substr(compte, 1, 3) cpt3,
substr(compte, 1, 4) cpt4
......
from table

Et cpt2 est un sous groupe de cpt1
et cpt3 est un sous groupe de cpt2
et cpt4 est un sous groupe de cpt3
.........

[salim11]

Envoyé par McM

C'est du report : Tu peux afficher un element à la fin d'une repeating qui fait la somme des données de cette repeating.

Avec des repeating ordre1, ordre2, ordre3, ordre4, compte et des sommes des débits/crédits pour chaque ordre, tu as tous tes éléments.

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
 
Oui c'est vrai tu peux faire le calcul des sous-totaux avec report, mais le 
problème se pose au niveau de l'affichage des comptes et sous-comptes.
 
Avec ta solution tu aura:
 
10000 ... ....
1000
100
10
1
10001 ... ....
1000
100
10
1

Tu peux adapter ce code à ton besoin, il est fait pour un compte de 4 positions :

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
 
SELECT SUBSTR(numero,1,4)  ACC_ID ,DEBIT,CREDIT ,
SUBSTR(numero,1,1) ACC_ID1,SUBSTR(numero,2,1) ACC_ID2,SUBSTR(numero,
3,1) ACC_ID3,SUBSTR(numero,4,1) ACC_ID4
FROM compte
union ALL
SELECT  DISTINCT  SUBSTR(numero,1,3)  ACC_ID ,SUM(DEBIT) over(partition 
BY SUBSTR(numero,1,3))  deb_ACC_ID3 ,SUM(CREDIT)over(partition BY 
SUBSTR(numero,1,3)) cr_ACC_ID3,SUBSTR(numero,1,1) ACC_ID1,SUBSTR
(numero,2,1) ACC_ID2,SUBSTR(numero,3,1) ACC_ID3,NULL ACC_ID4
FROM compte
union ALL
SELECT  DISTINCT
SUBSTR(numero,1,2)   ACC_ID,SUM(DEBIT) over(partition BY SUBSTR(numero,
1,2)) deb_ACC_ID2,SUM(CREDIT) over(partition BY SUBSTR(numero,1,2)) 
cr_ACC_ID2 , SUBSTR(numero,1,1) ACC_ID1,SUBSTR(numero,2,1) ACC_ID2
,NULL ACC_ID3,NULL ACC_ID4
FROM compte
union ALL
SELECT  DISTINCT
SUBSTR(numero,1,1)  ACC_ID ,SUM(DEBIT) over(partition BY SUBSTR(numero,
1,1)) deb_ACC_ID1,SUM(CREDIT) over(partition BY SUBSTR(numero,1,1)) 
cr_ACC_ID1 , SUBSTR(numero,1,1) ACC_ID1,NULL ACC_ID2,NULL ACC_ID3,NULL 
ACC_ID4
FROM compte
ORDER BY   ACC_ID1, ACC_ID2, ACC_ID3, ACC_ID4

Le résultat de la requete :

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
 
ACC_      DEBIT     CREDIT A A A A
---- ---------- ---------- - - - -
1000        100        200 1 0 0 0
1001        120        130 1 0 0 1
1002        140        150 1 0 0 2
1003        160        200 1 0 0 3
100         520        680 1 0 0
1010        180        320 1 0 1 0
1011        400        600 1 0 1 1
1012        500        450 1 0 1 2
101        1080       1370 1 0 1
10         1600       2050 1 0
1100        750        850 1 1 0 0
 
ACC_      DEBIT     CREDIT A A A A
---- ---------- ---------- - - - -
1101        950       1100 1 1 0 1
1102        120        600 1 1 0 2
110        1820       2550 1 1 0
1110        700        340 1 1 1 0
111         700        340 1 1 1
11         2520       2890 1 1
1200        900        112 1 2 0 0
1201        400        245 1 2 0 1
120        1300        357 1 2 0
1210        450        800 1 2 1 0
121         450        800 1 2 1
 
ACC_      DEBIT     CREDIT A A A A
---- ---------- ---------- - - - -
1280        200        120 1 2 8 0
128         200        120 1 2 8
12         1950       1277 1 2
1          6070       6217 1
2000        100        200 2 0 0 0
2001        120        130 2 0 0 1
2002        140        150 2 0 0 2
2003        160        200 2 0 0 3
200         520        680 2 0 0
2010        180        320 2 0 1 0
2011        400        600 2 0 1 1
 
ACC_      DEBIT     CREDIT A A A A
---- ---------- ---------- - - - -
2012        500        450 2 0 1 2
201        1080       1370 2 0 1
20         1600       2050 2 0
2100        750        850 2 1 0 0
2101        950       1100 2 1 0 1
2102        120        600 2 1 0 2
210        1820       2550 2 1 0
2110        700        340 2 1 1 0
211         700        340 2 1 1
21         2520       2890 2 1
2200        900        112 2 2 0 0
 
ACC_      DEBIT     CREDIT A A A A
---- ---------- ---------- - - - -
2201        400        245 2 2 0 1
220        1300        357 2 2 0
2210        450        800 2 2 1 0
221         450        800 2 2 1
2280        200        120 2 2 8 0
228         200        120 2 2 8
22         1950       1277 2 2
2          6070       6217 2

[orafrance]

Il me semble que la clause WITH n'a pas été abordée alors qu'elle pourrait sensiblement améliorer les perfs

[salim11]

Envoyé par orafrance

Il me semble que la clause WITH n'a pas été abordée alors qu'elle pourrait sensiblement améliorer les perfs

Salut Orafrance,

Je te remercie pour ta remarque pertinente, même c'est plus lisible

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
 
WITH CPT  AS 
( SELECT SUBSTR(NUMERO,1,4) ACC_ID,DEBIT,CREDIT ,
         SUBSTR(NUMERO,1,3)  ACC_3 ,SUM(DEBIT) OVER(PARTITION BY SUBSTR(NUMERO,1,3))DEB_ACC_3 ,
		 SUM(CREDIT)OVER(PARTITION BY SUBSTR(NUMERO,1,3)) CR_ACC_3,
		 SUBSTR(NUMERO,1,2)  ACC_2,SUM(DEBIT) OVER(PARTITION BY SUBSTR(NUMERO,1,2)) DEB_ACC_2,
		 SUM(CREDIT) OVER(PARTITION BY SUBSTR(NUMERO,1,2))CR_ACC_2,
		 SUBSTR(NUMERO,1,1)  ACC_1 ,SUM(DEBIT) OVER(PARTITION BY SUBSTR(NUMERO,1,1))DEB_ACC_1,
		 SUM(CREDIT) OVER(PARTITION BY SUBSTR(NUMERO,1,1)) CR_ACC_1 ,
		 SUBSTR(NUMERO,1,1) ACC_ID1,SUBSTR(NUMERO,2,1) ACC_ID2,SUBSTR(NUMERO,3,1) ACC_ID3,
		 SUBSTR(NUMERO,4,1) ACC_ID4 FROM COMPTE)
SELECT  ACC_ID ,DEBIT,CREDIT, ACC_ID1, ACC_ID2, ACC_ID3, ACC_ID4
FROM CPT
UNION ALL
SELECT  DISTINCT ACC_3 , DEB_ACC_3 , CR_ACC_3,ACC_ID1, ACC_ID2, ACC_ID3,NULL ACC_ID4
FROM CPT
UNION ALL
SELECT  DISTINCT ACC_2 , DEB_ACC_2 , CR_ACC_2 ,  ACC_ID1, ACC_ID2,NULL ACC_ID3,NULL ACC_ID4
FROM CPT
UNION ALL
SELECT  DISTINCT ACC_1 , DEB_ACC_1 ,CR_ACC_1 , ACC_ID1,NULL ACC_ID2,NULL ACC_ID3,NULL ACC_ID4
FROM CPT
ORDER BY   ACC_ID1, ACC_ID2, ACC_ID3, ACC_ID4

Essaie ce code pour un compte de 5 positions :

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 CPT  AS 
( SELECT SUBSTR(NUMERO,1,5) ACC_5,DEBIT DEB_ACC_5,CREDIT CR_ACC_5 ,
         SUBSTR(NUMERO,1,4) ACC_4,SUM(DEBIT) OVER(PARTITION BY SUBSTR(NUMERO,1,4))DEB_ACC_4 ,
		 SUM(CREDIT)OVER(PARTITION BY SUBSTR(NUMERO,1,4)) CR_ACC_4,
         SUBSTR(NUMERO,1,3)  ACC_3 ,SUM(DEBIT) OVER(PARTITION BY SUBSTR(NUMERO,1,3))DEB_ACC_3 ,
		 SUM(CREDIT)OVER(PARTITION BY SUBSTR(NUMERO,1,3)) CR_ACC_3,
		 SUBSTR(NUMERO,1,2)  ACC_2,SUM(DEBIT) OVER(PARTITION BY SUBSTR(NUMERO,1,2)) DEB_ACC_2,
		 SUM(CREDIT) OVER(PARTITION BY SUBSTR(NUMERO,1,2))CR_ACC_2,
		 SUBSTR(NUMERO,1,1)  ACC_1 ,SUM(DEBIT) OVER(PARTITION BY SUBSTR(NUMERO,1,1))DEB_ACC_1,
		 SUM(CREDIT) OVER(PARTITION BY SUBSTR(NUMERO,1,1)) CR_ACC_1 ,
		 SUBSTR(NUMERO,1,1) ACC_ID1,SUBSTR(NUMERO,2,1) ACC_ID2,SUBSTR(NUMERO,3,1) ACC_ID3,
		 SUBSTR(NUMERO,4,1) ACC_ID4,SUBSTR(NUMERO,5,1) ACC_ID5 FROM COMPTE)
SELECT  DISTINCT ACC_5 ,DEB_ACC_5,CR_ACC_5, ACC_ID1, ACC_ID2, ACC_ID3, ACC_ID4, ACC_ID5
FROM CPT
UNION ALL
SELECT  DISTINCT ACC_4 ,DEB_ACC_4,CR_ACC_4, ACC_ID1, ACC_ID2, ACC_ID3, ACC_ID4,NULL ACC_ID5
FROM CPT
UNION ALL
SELECT  DISTINCT ACC_3 , DEB_ACC_3 , CR_ACC_3,ACC_ID1, ACC_ID2, ACC_ID3,NULL ACC_ID4,NULL ACC_ID5
FROM CPT
UNION ALL
SELECT  DISTINCT ACC_2 , DEB_ACC_2 , CR_ACC_2 ,  ACC_ID1, ACC_ID2,NULL ACC_ID3,NULL ACC_ID4,NULL ACC_ID5
FROM CPT
UNION ALL
SELECT  DISTINCT ACC_1 , DEB_ACC_1 ,CR_ACC_1 , ACC_ID1,NULL ACC_ID2,NULL ACC_ID3,NULL ACC_ID4,NULL ACC_ID5
FROM CPT
ORDER BY   ACC_ID1, ACC_ID2, ACC_ID3, ACC_ID4,ACC_ID5

[OraAide]

Ca marche tres bien