Bonjour voiçi ma requête originale:
Je voudrais rajouter un Count des éléments "Fruit_Cantal_SupBio" de la première sous-requête qui sont (>) plus grand que (0) zéro soit :
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 SELECT SUM ("ClientProducteur_Nombre") AS "ClientProducteur_Nombre", SUM ("Fruit_Cantal_SupBio") AS "Fruit_Cantal_SupBio", SUM ("Fruit_Cantal_SupStd") AS "Fruit_Cantal_SupStd", a.mun_cd_geo_muncp AS "CODEGEO" FROM v_mgx_mpo_muncp_polgn a INNER JOIN (SELECT DISTINCT 1 AS "ClientProducteur_Nombre", SUM (DECODE (b.pve_cd_prodc_veg_c, 'FRUI_CANT', DECODE (b.mpv_val_suprf_bio__c, NULL, 0, b.mpv_val_suprf_bio__c ), 0 ) ) AS "Fruit_Cantal_SupBio", SUM (DECODE (b.pve_cd_prodc_veg_c, 'FRUI_CANT', DECODE (b.mpv_val_suprf_stand__c, NULL, 0, b.mpv_val_suprf_stand__c ), 0 ) ) AS "Fruit_Cantal_SupStd", a.pkcla, a.mun_cd_geo_muncp FROM mgx_cpr_clint_prodc_point a LEFT JOIN (SELECT a.pkcla, NULL AS mpv_val_suprf_bio__c, NULL AS pve_cd_prodc_veg_c, NULL AS mpv_val_suprf_stand__c FROM mgx_cpr_clint_prodc_point a LEFT JOIN mgx_cla_clint_agrnv b ON a.pkcla = b.pkcla UNION ALL SELECT a.pkcla, c.mpv_val_suprf_bio_ha AS mpv_val_suprf_bio__c, c.pve_cd_prodc_vegtl AS pve_cd_prodc_veg_c, c.mpv_val_suprf_stand_ha AS mpv_val_suprf_stand__c FROM mgx_cpr_clint_prodc_point a LEFT JOIN v_mgx_req_prodc_vegtl c ON a.pkcla = c.pkcla ) b ON a.pkcla = b.pkcla GROUP BY a.pkcla, a.mun_cd_geo_muncp, b.pve_cd_prodc_veg_c, b.mpv_val_suprf_bio__c) t ON a.mun_cd_geo_muncp = t.mun_cd_geo_muncp HAVING SUM ("ClientProducteur_Nombre") + SUM ("Fruit_Cantal_SupBio") + SUM ("Fruit_Cantal_SupStd") > 0 GROUP BY a.mun_cd_geo_muncp
qui donnerait comme requête finale:
Code : Sélectionner tout - Visualiser dans une fenêtre à part SELECT COUNT("Fruit_Cantal_SupBio") FROM T WHERE ("Fruit_Cantal_SupBio") > 0
malheureusement ça m'indique pour "T" : Table ou vue inexistante
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 SELECT SUM ("ClientProducteur_Nombre") AS "ClientProducteur_Nombre", SUM ("Fruit_Cantal_SupBio") AS "Fruit_Cantal_SupBio", (SELECT COUNT("Fruit_Cantal_SupBio") FROM T WHERE ("Fruit_Cantal_SupBio") > 0) as "Nb_Prod_Fruit_Cantal_SupBio", SUM ("Fruit_Cantal_SupStd") AS "Fruit_Cantal_SupStd", a.mun_cd_geo_muncp AS "CODEGEO" FROM v_mgx_mpo_muncp_polgn a INNER JOIN (SELECT DISTINCT 1 AS "ClientProducteur_Nombre", SUM (DECODE (b.pve_cd_prodc_veg_c, 'FRUI_CANT', DECODE (b.mpv_val_suprf_bio__c, NULL, 0, b.mpv_val_suprf_bio__c ), 0 ) ) AS "Fruit_Cantal_SupBio", SUM (DECODE (b.pve_cd_prodc_veg_c, 'FRUI_CANT', DECODE (b.mpv_val_suprf_stand__c, NULL, 0, b.mpv_val_suprf_stand__c ), 0 ) ) AS "Fruit_Cantal_SupStd", a.pkcla, a.mun_cd_geo_muncp FROM mgx_cpr_clint_prodc_point a LEFT JOIN (SELECT a.pkcla, NULL AS mpv_val_suprf_bio__c, NULL AS pve_cd_prodc_veg_c, NULL AS mpv_val_suprf_stand__c FROM mgx_cpr_clint_prodc_point a LEFT JOIN mgx_cla_clint_agrnv b ON a.pkcla = b.pkcla UNION ALL SELECT a.pkcla, c.mpv_val_suprf_bio_ha AS mpv_val_suprf_bio__c, c.pve_cd_prodc_vegtl AS pve_cd_prodc_veg_c, c.mpv_val_suprf_stand_ha AS mpv_val_suprf_stand__c FROM mgx_cpr_clint_prodc_point a LEFT JOIN v_mgx_req_prodc_vegtl c ON a.pkcla = c.pkcla ) b ON a.pkcla = b.pkcla GROUP BY a.pkcla, a.mun_cd_geo_muncp, b.pve_cd_prodc_veg_c, b.mpv_val_suprf_bio__c) t ON a.mun_cd_geo_muncp = t.mun_cd_geo_muncp HAVING SUM ("ClientProducteur_Nombre") + SUM ("Fruit_Cantal_SupBio") + SUM ("Fruit_Cantal_SupStd") > 0 GROUP BY a.mun_cd_geo_muncp
Je ne sais pas trop quoi faire
Partager