Bonjour à tous,

j'ai une requête sous MySQL que je dois faire fonctionner sous PostgreSQL. Problème, j'ais des erreurs à l'exectution. voici ma requetes MySQL :
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
 
SELECT 
   (CASE 
      WHEN month(T1.ACCDAT_0) = 1 THEN 'Janvier'
      WHEN month(T1.ACCDAT_0) = 2 THEN 'Fevrier'
      WHEN month(T1.ACCDAT_0) = 3 THEN 'Mars'
      WHEN month(T1.ACCDAT_0) = 4 THEN 'Avril'
      WHEN month(T1.ACCDAT_0) = 5 THEN 'Mai'
      WHEN month(T1.ACCDAT_0) = 6 THEN 'Juin'
      WHEN month(T1.ACCDAT_0) = 7 THEN 'Juillet'
      WHEN month(T1.ACCDAT_0) = 8 THEN 'Aout'
      WHEN month(T1.ACCDAT_0) = 9 THEN 'Septembre'
      WHEN month(T1.ACCDAT_0) = 10 THEN 'Octobre'
      WHEN month(T1.ACCDAT_0) = 11 THEN 'Novembre'
      WHEN month(T1.ACCDAT_0) = 12 THEN 'Decembre' 
   END) as 'Mois', 
round(SUM((T1.AMTNOT_0-T1.AMTPOS_0)*T1.SNS_0)) as ChiffreHP,
(
   SELECT round(sum((T4.AMTNOT_0-T4.AMTPOS_0)*T4.SNS_0))
   FROM SINVOICE T4
   WHERE year(T4.ACCDAT_0) = ${param_date} 
   AND T4.REP_0 = ${param_rep}
   AND T4.EXEPER_0 <= T1.EXEPER_0
   AND T4.EXEANN_0 = T1.EXEANN_0
) as 'ChiffreCumuleHP',
 
(round(
(
   SELECT round(sum((T41.AMTNOT_0-T41.AMTPOS_0)*T41.SNS_0))
   FROM SINVOICE T41
   WHERE year(T41.ACCDAT_0) =${param_date}
   AND T41.REP_0 = ${param_rep}
   AND T41.EXEPER_0 <= T1.EXEPER_0
   AND T41.EXEANN_0 = T1.EXEANN_0
)
*100/
( 
      SELECT round(((T21.CHIFFRE_0/12)*T1.EXEPER_0)) 
      FROM OBJECTIF T21
      WHERE T21.REPNUM_0 = ${param_rep}
      AND T21.EXDEB_0 = ${param_date}
      AND T21.EXFIN_0 = ${param_date}
))) as 'pRealise',
( 
      SELECT round(((T2.CHIFFRE_0/12)*T1.EXEPER_0)) 
      FROM OBJECTIF T2
      WHERE T2.REPNUM_0 = ${param_rep}
      AND T2.EXDEB_0 = ${param_date}
      AND T2.EXFIN_0 = ${param_date}
) as 'Objectif',
round(avg(T1.PREMISE_0)) as PREMISE,
SUM(T1.AMTPOS_0*T1.SNS_0) as Pose,
(
   SELECT round(sum(T4.AMTNOT_0*T4.SNS_0))
   FROM SINVOICE T4
   WHERE year(T4.ACCDAT_0) = ${param_date} 
   AND T4.REP_0 = ${param_rep}
   AND T4.EXEPER_0 <= T1.EXEPER_0
   AND T4.EXEANN_0 = T1.EXEANN_0
) as 'ChiffreCumuleTT'
 
FROM SINVOICE T1
WHERE T1.REP_0 = ${param_rep}
AND year(T1.ACCDAT_0) = ${param_date}
GROUP BY Mois
ORDER BY T1.ACCDAT_0
ici la requete PostgreSQL
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 
   (CASE 
      WHEN date_part('month', T1.ACCDAT_0) = 1 THEN 'Janvier'
      WHEN date_part('month', T1.ACCDAT_0) = 2 THEN 'Fevrier'
      WHEN date_part('month', T1.ACCDAT_0) = 3 THEN 'Mars'
      WHEN date_part('month', T1.ACCDAT_0) = 4 THEN 'Avril'
      WHEN date_part('month', T1.ACCDAT_0) = 5 THEN 'Mai'
      WHEN date_part('month', T1.ACCDAT_0) = 6 THEN 'Juin'
      WHEN date_part('month', T1.ACCDAT_0) = 7 THEN 'Juillet'
      WHEN date_part('month', T1.ACCDAT_0) = 8 THEN 'Aout'
      WHEN date_part('month', T1.ACCDAT_0) = 9 THEN 'Septembre'
      WHEN date_part('month', T1.ACCDAT_0) = 10 THEN 'Octobre'
      WHEN date_part('month', T1.ACCDAT_0) = 11 THEN 'Novembre'
      WHEN date_part('month', T1.ACCDAT_0) = 12 THEN 'Decembre' 
   END) as Mois, 
 (
   SELECT round(sum((T4.AMTNOT_0-T4.AMTPOS_0)*T4.SNS_0))
   FROM SINVOICE T4
   WHERE date_part('year', T4.ACCDAT_0) = ${param_date}
   AND T4.REP_0 = ${param_rep}
   AND T4.EXEPER_0 <= T1.EXEPER_0
   AND T4.EXEANN_0 = T1.EXEANN_0
) as ChiffreCumuleHP,
 
(round(
(
   SELECT sum((T41.AMTNOT_0-T41.AMTPOS_0)*T41.SNS_0)
   FROM SINVOICE T41
   WHERE date_part('year', T41.ACCDAT_0) = ${param_date}
   AND T41.REP_0 = ${param_rep}
   AND T41.EXEPER_0 <= T1.EXEPER_0
   AND T41.EXEANN_0 = T1.EXEANN_0
)
*100/
( 
      SELECT (T21.OBJECTIF_0/12)*T1.EXEPER_0 
      FROM OBJECTIF T21
      WHERE T21.REPNUM_0 = ${param_rep}
      AND date_part('year', T21.DDEB_0) = ${param_date}
)
)) as pRealise,
( 
      SELECT round(((T2.OBJECTIF_0/12)*T1.EXEPER_0)) 
      FROM OBJECTIF T2
      WHERE T2.REPNUM_0 = ${param_rep}
      AND date_part('year', T2.DDEB_0) = ${param_date}
 
) as Objectif
FROM SINVOICE T1
WHERE T1.REP_0 = ${param_rep}
AND date_part('year', T1.ACCDAT_0)= ${param_date}
GROUP BY Mois
ORDER BY T1.ACCDAT_0
et voici l'erreur :
Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
 
ERROR:  subquery uses ungrouped column "t1.exeper_0" from outer query
LINE 21:    AND T4.EXEPER_0 <= T1.EXEPER_0
                                            ^
en claire il me demande d'ajouter T1.EXEPER_0 dans le groupe by mais aussi T1.EXEANN_0,T1.ACCDAT_0

si je le fait, plus d'erreur . sauf que je resultat attendu n'est plus le bon !

Est ce que je me trompe dans mon raisonnement ? comment faut il faire ?

Merci à vous