Requete qui renvoi un cumul et un detail

**shaun_the_sheep** · 28/04/2009, 09h51

Bonjour,

Je dois faire une requete qui doit me presenter sur une colonne un cumul de versement et sur d'autres colonnes le détail de chacun des versements.

Exple :

Entreprise A | Montant Total | Montant recu 1 | Montant recu 2 | ...

Voici un début soit le cumul :

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
64
65
66
67
68
69
70
71
72
 
SELECT t.contact_category,
t.contact_type,
t.name,
t.contact_id,
t.company_id as SIRET,
t.importance,
t.status,
t.client,
coalesce(j0.address_type,' ') AS address_type,
coalesce(j0.address,' ') AS address,
coalesce(j0.place,' ') AS place,
coalesce(j0.zip_code,' ') AS zip_code,
coalesce(j0.telephone_1,' ') AS telephone,
coalesce(j0.telephone_2,' ') AS fax,
coalesce(j0.telephone_3,' ') AS telex,
coalesce(j0.telephone_4,' ') AS mobile,
coalesce(j0.e_mail,' ') AS e_mail,
coalesce(j0.url_path,' ') AS url_path,
h.year_no AS annee, 
 
sum(coalesce(d.cont_amount, 0)) AS versement_Brut_AnneeC , --montant brut
sum(coalesce(d.NET_AMOUNT, 0)) AS versement_Net_AnneeC -- montant net
 
 
FROM
ae6contact t 
 
--Type de groupe
inner join ae6contactgroup g 
ON t.contact_group = g.contact_group 
AND g.sys_value = 'LEGAL' 
AND g.client=t.client 
AND g.status='N'
 
--Adresse
left outer join agladdress j0 
ON j0.attribute_id='JC02' 
AND t.client=j0.client 
AND t.contact_id=j0.dim_value 
AND j0.address_type='1'
 
--Promesse
INNER JOIN ae6aptxhead h
ON h.client = t.client
AND h.company_id = t.contact_id
AND h.STATUS = 'N' -- vu que vous êtes en jointure forte, ceci peut aller dans le where
 
--Detail Taxe
INNER JOIN ae6aptxdet d
ON d.client = h.client
AND d.apptaxhead_no = h.apptaxhead_no
AND d.STATUS = 'N' -- idem
 
--Collecteur
--inner join ae6aptxremit r 
--- ON r.client = d.client 
-- AND r.aptxremit_no = d.aptxremit_no 
-- AND d.aptxremit_no > 0
 
WHERE
t.client = 'P1' 
AND t.STATUS = 'N'
AND t.contact_type = 'ENTREPRISE'
AND t.contact_category = 'ENTREPRISE'
AND h.year_no='2009'
--AND t.contact_id = '46734'
Group by t.contact_category,t.contact_type,t.name,t.contact_id,t.company_id,t.importance,t.status,t.client,j0.address_type,
j0.address,j0.place,j0.zip_code,j0.telephone_1,j0.telephone_2,j0.telephone_3,j0.telephone_4,j0.e_mail,j0.url_path,h.year_no
ORDER BY
t.name ASC,
h.year_no ASC

Je cherche maintenant le moyen d'intégrer le détail de mes versements à ma requete.

J'avoue être un peu à court d'idée , est ce que quelqu'un a une idée ?

Nota: je suis en oracle 10G.

**Waldar** · 28/04/2009, 10h57

A quoi correspondrait le détail dans votre requête ?

**shaun_the_sheep** · 28/04/2009, 11h00

Bonjour,

Dsl je ne l'avais pas préciser , en faite aux champs :

d.cont_amount pour le versement brut et d.NET_AMOUNT pour le versement net.

soit mes deux cumuls.

Merci

**Waldar** · 28/04/2009, 11h08

Et si vous recevez 5.000 montants vous voulez 5.001 colonnes ?

**shaun_the_sheep** · 28/04/2009, 11h12

sans aller jusque la en théorie je devrais avoir entre 1 à moins de 10 montants au maximun pour une entreprise.

La moyenne étant 2 à 3.

**Waldar** · 28/04/2009, 11h44

Il faut dans ce cas faire une première requête qui vous donnera un numéro de versement par entreprise, puis faire un pivot sur celle-ci.

Oracle ne supporte la syntaxe pivot qu'à partir de la 11g, mais vous pouvez le faire avec la méthode classique :

Code :

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

1
2
3
sum(case numero when 1 then montant else 0 end ) as montant1,
sum(case numero when 2 then montant else 0 end ) as montant2,
...

Mais si vous en prévoyez 10 et qu'un jour vous avez 11 versements, vous ne le verrez pas.

C'est pour ça que cette partie "visuelle" est très rarement gérée en SQL.

**shaun_the_sheep** · 28/04/2009, 11h46

J'ai exploré une autre piste dans mon post :
Comment mettre en colonne des resultats de plusieurs lignes

J'ai une astuce pour mettre ma table des versements en colonne , reste à l'integrer à ma requete.

Code :

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

1
2
3
4
5
 
Select apptaxhead_no,
            max(decode(rn,1,cont_amount)) as cont_amount1,
            max(decode(rn,2,cont_amount)) as cont_amount2
     From (Select apptaxhead_no,cont_amount,row_number() OVER(partition BY  apptaxhead_no ORDER BY cont_date ASC) as rn from ae6aptxdet Where status='N') group by apptaxhead_no

L'idée est de faire une vue que j'exploite par la suite dans mon systeme intégré.

C'est certain c'est pas evident , mais le client le veut comme cela.

**shaun_the_sheep** · 28/04/2009, 14h10

Bon, j'ai fais une vue avec ma requete du post du dessus , et je fais une jointure dessus, ce qui donne :

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
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
SELECT t.contact_category,
       t.contact_type,
       t.name,
       t.contact_id,
       t.company_id as SIRET,
       t.importance,
       t.status,
       t.client,
       coalesce(j0.address_type,' ') AS address_type,
       coalesce(j0.address,' ') AS address,
       coalesce(j0.place,' ') AS place,
       coalesce(j0.zip_code,' ') AS zip_code,
       coalesce(j0.telephone_1,' ') AS telephone,
       coalesce(j0.telephone_2,' ') AS fax,
       coalesce(j0.telephone_3,' ') AS telex,
       coalesce(j0.telephone_4,' ') AS mobile,
       coalesce(j0.e_mail,' ') AS e_mail,
       coalesce(j0.url_path,' ') AS url_path,
       h.year_no AS annee, 
      
       sum(coalesce(d.cont_amount, 0)) AS versement_Brut_AnneeC , --montant brut
       --sum(coalesce(d.NET_AMOUNT, 0)) AS versement_Net_AnneeC, -- montant net
       --count(d.cont_amount)           As nombre_versement
       
       cl.cont_amount1,
       cl.cont_amount2


       
FROM
    ae6contact t 
    
      --Type de groupe
     inner join  ae6contactgroup g 
       ON t.contact_group = g.contact_group 
       AND g.sys_value = 'LEGAL' 
       AND g.client=t.client 
       AND g.status='N'
       
     --Adresse
     left outer join agladdress j0 
      ON j0.attribute_id='JC02'   
      AND t.client=j0.client   
      AND t.contact_id=j0.dim_value  
      AND j0.address_type='1'
 
    --Promesse
    INNER JOIN ae6aptxhead h
      ON h.client = t.client
     AND h.company_id = t.contact_id
     AND h.STATUS = 'N' -- vu que vous êtes en jointure forte, ceci peut aller dans le where
     
    --Detail Taxe
    INNER JOIN ae6aptxdet d
      ON d.client = h.client
      AND d.apptaxhead_no = h.apptaxhead_no
      AND d.STATUS = 'N' -- idem
     
    --Collecteur
    --inner join ae6aptxremit r 
    --- ON r.client = d.client   
    -- AND r.aptxremit_no = d.aptxremit_no   
    -- AND d.aptxremit_no > 0
    INNER JOIN AVI_TAXE_VERSEMENT_COLONNE cl
      ON  cl.apptaxhead_no = h.apptaxhead_no
      
WHERE
    t.client = 'P1' 
AND t.STATUS = 'N'
AND t.contact_type = 'ENTREPRISE'
AND t.contact_category = 'ENTREPRISE'
AND h.year_no='2009'

Group by t.contact_category,t.contact_type,t.name,t.contact_id,t.company_id,t.importance,t.status,t.client,j0.address_type,
j0.address,j0.place,j0.zip_code,j0.telephone_1,j0.telephone_2,j0.telephone_3,j0.telephone_4,j0.e_mail,j0.url_path,h.year_no,
cl.cont_amount1,cl.cont_amount2

ORDER BY
    t.name ASC,
    h.year_no ASC

j'arrive à un resultat satisfaisant , mais si quelqu' un a plus simple.