requête : MySQL vers PostgreSQL

Version imprimable

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:

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:

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:

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

16/07/2014, 09h40
al1_24

La norme du langage SQL - que respecte ici PostgreSQL - impose que toutes les colonnes de la clause SELECT qui ne font pas l'objet d'une fonction de regroupement (SUM, MAX...) soient reprises dans la clause GROUP BY.
MySQL est très permissif sur la syntaxe des regroupements et permet d'exécuter des requêtes dont les résultats n'ont pas de sens fonctionnellement parlant.
Ainsi, pour les colonnes du SELECT qui ne sont pas dans le GROUP BY, MySQL retournera des valeurs prises au hasard dans celles qui correspondent au regroupement.
Pour ces colonnes qui posent problème dans la requête sous PostgreSQL, il faut donc identifier quelle valeur est attendue et modifier la requête en conséquence.
16/07/2014, 12h04
siro1

Merci pour toutes ces explication Al1 , mais je ne vois pas comment transposer ma requête sous PostgreSQL.
Le résultat voulu est une somme cumulé sur chaque lignes. Si je m'en tiens au message d'erreur et que je corrige en conséquence. les sommes cumulées deviennent des sommes totales 8O . . . et cela ne répond pas à mon besoin.

Pouvez vous me donner plus de précisions ?

MErci encore

Bonjour,

S'il s'agit de faire des sommes cumulées, alors vous pouvez faire beaucoup plus simple (et beaucoup plus performant) en remplaçant vos sous-requêtes par la fonction analytique SUM() OVER().

Quelque chose comme ceci (pas testé)

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
 
 
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, 
  round(SUM(sum((AMTNOT_0-AMTPOS_0)*SNS_0)) OVER(PARTITION BY EXEANN_0 ORDER BY EXEPER_0)) AS ChiffreCumuleHP,
  round(SUM(sum((AMTNOT_0-AMTPOS_0)*SNS_0)) OVER(PARTITION BY EXEANN_0 ORDER BY EXEPER_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

17/07/2014, 15h48
siro1

Bonjour et merci d'avoir pris le temps de répondre.
Je viens de tester la requête que vous m'avez présentée. Cette dernière présente une erreur (la même)...:calim2:
17/07/2014, 16h31
aieeeuuuuu

Il faudrait grouper sur EXEPER_0 également,
mais j'ai du mal à comprendre les deux dernières sous-requêtes.
Pourquoi ne pas faire simplement une jointure ?

Quelle est le lien entre la table sinvoice et la table objectif.

Les structures des tables seraient les bienvenues.
17/07/2014, 17h00
siro1

J'utilise le résultat de la requête pour générer des tableaux et des graphiques.

Il existe deux tables :
- SINVOICE (facture) :
num_0 character varying(15) Numéro de facture
bpr_0 character varying(10) Numéro client
poscod_0 character varying(10) Code postale client
accdat_0 timestamp without time zone Date facture
sivtyp_0 character varying(3) Type de facture
cry_0 character varying(3) Pays
rep_0 character varying(10) Représentant (commercial)
dep_0 character varying(3) Departement de vente
amtnot_0 double precision montant HT
sns_0 integer Sens (-1 ou +1)
exeann_0 integer Année d'exercice
exeper_0 integer Numéro du mois de l'exercice de 1 à 12
amtpos_0 double precision Montant de main d'oeuvre
premise_0 double precision Pourcentage de remise
majdate_0 timestamp without time zone Date de MAJ

- OBJECTIF (objectif annuel d'un commercial) :
repnum_0 character varying(10) Code du commercial
ddeb_0 timestamp without time zone Date de debut de l'objectif
dfin_0 timestamp without time zone Date de fin de l'objectif
objectif_0 integer Montant de l'objectif

Les champs de résultat :
Dep : Dépratement de vente
Janvier : chiffre généré au mois de janvier pour le département en question
...
Decembre :chiffre généré au mois de décembre pour le département en question
ChiffreCumuleHP : Chiffre d'affaire cumulé mois par mois
pRealise : pourcentage réalisé
Objectif : objectif cumulé (permet d'avoir une courbe de comparaison par rapport au ChiffreCumle)
par exemple pour une commercial ayant 24 000 à faire pour une année
le 1er mois il devra générer : 2 000
le 2eme : 4 000 ...etc

Remarque : l'exercice de l'entreprise en question était du 1 avril au 31 Mars ====> passage à un exercice en année civil (du 1er janvier au 31 decembre ) , donc nous n'avons plus besoin de nous référer à la date de début et fin mais à l'année de la date de début (Objectif.ddeb_0).

Voilà toutes les infos ...
18/07/2014, 12h02
siro1

J'ai essayé de passer par une fonction, ça n'a rien donné non plus... même erreur.
À partir du moment où j'essaye d'utiliser la date dans une sous-requête, il me demande de le mettre dans un GROUP BY ! :?
18/07/2014, 12h32
aieeeuuuuu

C'est tout à fait logique, le SGBD ne va pas choisir pour vous quelle valeur il doit prendre (seul MySQL fait ça).

Vous devez donc :
-soit mettre la date dans la GROUP BY, et aurez une ligne par date distincte
-soit utiliser une fonction d’agrégat sur la date pour spécifier quelle date utiliser.
18/07/2014, 14h10
siro1

Je ne peux pas grouper par date, cela me multiplie les lignes.
Avez-vous un exemple avec les fonctions d'agrégat ? Je vais regarder l'aide en ligne.

---

Je viens de regarder l'aide en ligne, je ne pense pas que cela résoudra mon problème puisque même si je crée une fonction d'agrégation , je devrai faire passer la date ou la période (T1.ACCDAT_0) en paramètres. Nécessairement, il va me demander de grouper par date ... :calim2:

Je me trompe ? Voyez-vous la chose différemment ?
18/07/2014, 16h06
aieeeuuuuu

Non, ce n'est pas ACCDAT_0 que vous utilisez dans la sous-requête mais EXEPER_0. C'est donc cette colonne qu'il faut ajouter au group by. Si j'ai bien compris votre modèle, cela ne devrait pas multiplier le nombre de lignes, puisque EXEPER_0 ne devrait prendre qu'une seule valeur distincte par mois.

Par contre, vous utilisez ACCDAT_0 dans l'ORDER BY, et je pense que c'est également ce qui vous pose problème.

On va prendre les problèmes un par un : supprimez l'ORDER BY pour le moment (ou remplacez-le par un ORDER BY Mois).