Filtre de paramètre dans requête avec sous-requêtes

**Nerva** · 10/11/2022, 12h54

Bjr.

J'ai deux tables (HSQLDB) dans lesquelles sont saisies les représentants et leur chiffre d'affaires mensuel :

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
CREATE TABLE T_REPRESENTANTS (
	REP_ID INTEGER GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1) NOT NULL,
	REP_NOM VARCHAR(32) NOT NULL,
	REP_PRENOM VARCHAR(32) NOT NULL,
	CONSTRAINT PK_REP_ID PRIMARY KEY (REP_ID)
);
 
CREATE TABLE T_VENTES (
	VEN_ID INTEGER GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1) NOT NULL,
	REP_ID INTEGER NOT NULL,
	VEN_DATE DATE NOT NULL,	-- Date du dernier jour du mois
	VEN_MNT DECIMAL(10,2) DEFAULT 0 NOT NULL,
	CONSTRAINT PK_VEN_ID PRIMARY KEY (VEN_ID),
	CONSTRAINT FK_VEN_REP FOREIGN KEY (REP_ID) REFERENCES T_REPRESENTANTS (REP_ID),
	CONSTRAINT UC_REP_DATE UNIQUE (REP_ID, VEN_DATE)
);

Après tâtonnements, j'ai créé cette requête qui permet d'afficher les données sous forme tabulaire :

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
SELECT REP_NOM || ' ' || REP_PRENOM AS REPRESENTANT,
(SELECT SUM(T_VENTES.VEN_MNT) FROM T_VENTES WHERE T_VENTES.REP_ID = T_REPRESENTANTS.REP_ID AND MONTH(T_VENTES.VEN_DATE) = 1) AS JAN,
(SELECT SUM(T_VENTES.VEN_MNT) FROM T_VENTES WHERE T_VENTES.REP_ID = T_REPRESENTANTS.REP_ID AND MONTH(T_VENTES.VEN_DATE) = 2) AS FEV,
(SELECT SUM(T_VENTES.VEN_MNT) FROM T_VENTES WHERE T_VENTES.REP_ID = T_REPRESENTANTS.REP_ID AND MONTH(T_VENTES.VEN_DATE) = 3) AS MAR,
(SELECT SUM(T_VENTES.VEN_MNT) FROM T_VENTES WHERE T_VENTES.REP_ID = T_REPRESENTANTS.REP_ID AND MONTH(T_VENTES.VEN_DATE) = 4) AS AVR,
(SELECT SUM(T_VENTES.VEN_MNT) FROM T_VENTES WHERE T_VENTES.REP_ID = T_REPRESENTANTS.REP_ID AND MONTH(T_VENTES.VEN_DATE) = 5) AS MAI,
(SELECT SUM(T_VENTES.VEN_MNT) FROM T_VENTES WHERE T_VENTES.REP_ID = T_REPRESENTANTS.REP_ID AND MONTH(T_VENTES.VEN_DATE) = 6) AS JUI,
(SELECT SUM(T_VENTES.VEN_MNT) FROM T_VENTES WHERE T_VENTES.REP_ID = T_REPRESENTANTS.REP_ID AND MONTH(T_VENTES.VEN_DATE) = 7) AS JUL,
(SELECT SUM(T_VENTES.VEN_MNT) FROM T_VENTES WHERE T_VENTES.REP_ID = T_REPRESENTANTS.REP_ID AND MONTH(T_VENTES.VEN_DATE) = 8) AS AOU,
(SELECT SUM(T_VENTES.VEN_MNT) FROM T_VENTES WHERE T_VENTES.REP_ID = T_REPRESENTANTS.REP_ID AND MONTH(T_VENTES.VEN_DATE) = 9) AS SEP,
(SELECT SUM(T_VENTES.VEN_MNT) FROM T_VENTES WHERE T_VENTES.REP_ID = T_REPRESENTANTS.REP_ID AND MONTH(T_VENTES.VEN_DATE) = 10) AS OCT,
(SELECT SUM(T_VENTES.VEN_MNT) FROM T_VENTES WHERE T_VENTES.REP_ID = T_REPRESENTANTS.REP_ID AND MONTH(T_VENTES.VEN_DATE) = 11) AS NOV,
(SELECT SUM(T_VENTES.VEN_MNT) FROM T_VENTES WHERE T_VENTES.REP_ID = T_REPRESENTANTS.REP_ID AND MONTH(T_VENTES.VEN_DATE) = 12) AS DEC,
(SELECT SUM(T_VENTES.VEN_MNT)
FROM T_VENTES
WHERE T_VENTES.REP_ID = T_REPRESENTANTS.REP_ID) AS TOTAL
FROM T_REPRESENTANTS
ORDER BY T_REPRESENTANTS.REP_NOM ASC

Je voudrais intégrer dans cette requête un filtre de paramètre pour l'année et je ne parviens pas à le faire.

Vous en remerciant...

**tatayo** · 10/11/2022, 14h28

Bonjour,
Tu peux largement simplifier ta requête:

Code sql :

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

1
2
3
4
5
6
7
8
9
 
SELECT REP_NOM || ' ' || REP_PRENOM AS REPRESENTANT,
sum(case when MONTH(T_VENTES.VEN_DATE) = 1 then T_VENTES.VEN_MNT else 0 end) as jan,
sum(case when MONTH(T_VENTES.VEN_DATE) = 2 then T_VENTES.VEN_MNT else 0 end) as fev,
...
from T_REPRESENTANTS
inner join T_VENTES 
on T_VENTES.REP_ID = T_REPRESENTANTS.REP_ID
group by REP_NOM,REP_PRENOM AS REPRESENTANT

La requête sera sans aucun doute bien plus performante, et l'ajout d'un filtre sur l'année devient trivial.

Tatayo.

**Nerva** · 10/11/2022, 18h34

Et oui, une jointure au lieu de 13 sous-requêtes... Merci...

Las clauses WHERE et ORDER BY sont intégrées mais j'ai encore un petit problème. Pour que cette requête soit complète, il faut également le total par colonne. Je ne sais vraiment pas si ça peut s'intégrer dans cette requête, alors pour l'instant, je l'enregistre sous forme de vue nommée V_VENTES (en tant que requête, la suivante ne fonctionne pas) et j'obtiens la somme des colonnes avec celle-ci :

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
SELECT * FROM V_VENTES
UNION
SELECT 'TOTAL',
SUM(JAN) AS JAN,
SUM(FEV) AS FEV,
SUM(MAR) AS MAR,
SUM(AVR) AS AVR,
SUM(MAI) AS MAI,
SUM(JUI) AS JUI,
SUM(JUL) AS JUL,
SUM(AOU) AS AOU,
SUM(SEP) AS SEP,
SUM(OCT) AS OCT,
SUM(NOV) AS NOV,
SUM(DEC) AS DEC,
SUM(TOTAL) AS TOTAL
FROM V_VENTES

C'est au niveau de l'ordre que ça coince. La ligne TOTAL est également classée par ordre alphabétique, parmi les représentants.

**al1_24** · 10/11/2022, 19h38

Bonjour,

Ce genre de problème peut-être résolu en ajoutant une colonne :

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
SELECT  1   AS  ORDRE
    ,   * 
FROM V_VENTES
UNION
SELECT  2   AS  ORDRE
    ,   'TOTAL'
    ,   SUM(JAN) AS JAN
    ,   SUM(FEV) AS FEV
    ,   SUM(MAR) AS MAR
    ,   SUM(AVR) AS AVR
    ,   SUM(MAI) AS MAI
    ,   SUM(JUI) AS JUI
    ,   SUM(JUL) AS JUL
    ,   SUM(AOU) AS AOU
    ,   SUM(SEP) AS SEP
    ,   SUM(OCT) AS OCT
    ,   SUM(NOV) AS NOV
    ,   SUM(DEC) AS DEC
    ,   SUM(TOTAL) AS TOTAL
FROM    V_VENTES
ORDER BY ORDRE

**Nerva** · 11/11/2022, 10h23

Ok et en ajoutant REPRESENTANT au ORDER BY, on obtient le résultat escompté.

Merci.

**Waldar** · 15/11/2022, 19h11

Il y a encore mieux :

Code :

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

1
2
3
4
5
6
7
8
9
  select case grouping(REP_NOM || ' ' || REP_PRENOM when 1 then 'Total' else REP_NOM || ' ' || REP_PRENOM end as REPRESENTANT
       , sum(case when month(T_VENTES.VEN_DATE) = 1 then T_VENTES.VEN_MNT else 0 end) as jan
       , sum(case when month(T_VENTES.VEN_DATE) = 2 then T_VENTES.VEN_MNT else 0 end) as fev
       , ...
    from T_REPRESENTANTS
    join T_VENTES        on T_VENTES.REP_ID = T_REPRESENTANTS.REP_ID
group by rollup(REP_NOM || ' ' || REP_PRENOM)
order by grouping_id()
       , REPRESENTANT;

J'ai supposé que les fonctions GROUPING & GROUPING_ID existent dans HSQLDB mais je ne les ai pas trouvées dans la doc, il faudra peut-être les remplacer par un coalesce.

**Nerva** · 16/11/2022, 17h37

Avec coalesce, voici le message d'erreur :

Nom : copie.png
Affichages : 167
Taille : 57,4 Ko

J'ai fait une recherche rapide et il semblerait que HSQL ne gère pas le rollup (je n'en avais d'ailleurs moi-même jamais entendu parlé). Je n'ai donc trouvé que cette référence (qui date tout de même de 18 ans) :

https://sourceforge.net/p/hsqldb/dis...read/498647de/

**Waldar** · 17/11/2022, 17h23

C'est dans la doc au chapitre "GROUP BY" :
https://hsqldb.org/doc/guide/dataaccess-chapt.html
Mais oui peut-être ce n'est pas dans la version que vous utilisez.

**Nerva** · 18/11/2022, 10h32

Effectivement. La documentation dans laquelle je fais des recherches si besoin est n'est pas la même et est beaucoup moins complète (mais j'avoue que je n'ai pas tout scruté) :

https://wiki.openoffice.org/wiki/FR/...n/HSQLDB_Guide

Après ça, il est aussi tout à fait possible que ce rollup ne soit pas implémenté dans le HSQLDB de LibreOffice (je sais qu'il y a des différences).

Filtre de paramètre dans requête avec sous-requêtes

Langage SQL

Discussions similaires

Partager

Partager