Problème d'alias sur requête

Version imprimable

Bonjour,

J'essaie de créer une vue des effectifs scolaires en ordonnant le niveau dans l'ordre de progression de manière à pouvoir réexploiter facilement les données dans un crosstab qui fonctionne bien

J'ai donc deux vues mais pour la première requête j'obtiens l'erreur suivante

Citation:

ERREUR: la sous-requête du FROM doit avoir un alias
LINE 4: FROM (SELECT
^
HINT: Par exemple, FROM (SELECT...) [AS] quelquechose.

Et donc voici les requêtes

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
CREATE OR REPLACE VIEW secto_elem_global_result (nom_sect, niveau, effectifs)
AS
(SELECT c.nom_sect, t.niveau, t.effectifs
FROM (SELECT 
"SIMULATION_SECTEURS_ECOLES_ELEMENTAIRES".id AS nom_sect,
CASE
	WHEN "BD_EFFECTIF_SCOLAIRE_SIMULATION_CLICHY_L932".inscr_2016 = 'CP' THEN 'niveau_1_CP'
	WHEN "BD_EFFECTIF_SCOLAIRE_SIMULATION_CLICHY_L932".inscr_2016 = 'CE1' THEN 'niveau_2_CE1'
	WHEN "BD_EFFECTIF_SCOLAIRE_SIMULATION_CLICHY_L932".inscr_2016 = 'CE2' THEN 'niveau_3_CE2'
	WHEN "BD_EFFECTIF_SCOLAIRE_SIMULATION_CLICHY_L932".inscr_2016 = 'CM1' THEN 'niveau_4_CM1'
	WHEN "BD_EFFECTIF_SCOLAIRE_SIMULATION_CLICHY_L932".inscr_2016 = 'CM2' THEN 'niveau_5_CM2'
	ELSE 'non_elem'
end AS niveau,
count(*) AS effectifs
FROM postgres.postgres."BD_EFFECTIF_SCOLAIRE_SIMULATION_CLICHY_L932" t
INNER JOIN postgres.postgres."SIMULATION_SECTEURS_ECOLES_ELEMENTAIRES" c
ON ST_WITHIN(c.geometry, t.geometry2))
GROUP BY c.nom_sect, t.niveau
ORDER BY c.nom_sect, t.niveau);
 
 
 
 
CREATE OR REPLACE VIEW secto_elem_global_tableau
AS SELECT *
FROM crosstab(
'select nom_sect, niveau, effectifs
from secto_elem_global_result
where niveau = ''niveau_1_CP'' OR niveau = ''niveau_2_CE1'' OR niveau = ''niveau_3_CE2'' OR niveau = ''niveau_4_CM1'' OR niveau = ''niveau_5_CM2''
ORDER BY 1,2')
AS 
(id character varying (25),
niveau_1_CP bigint, 
niveau_2_CE1 bigint, 
niveau_3_CE2 bigint, 
niveau_4_CM1 bigint, 
niveau_5_CM2 bigint);

Avez-vous une idée de la manière à utiliser pour résoudre ce problème ?

J'ai trouvé une solution mais le résultat n'est pas complètement correct car le GROUP BY sur le t.niveau ne se fait pas correctement car j'ai encore plusieurs lignes "non_elem" :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
CREATE OR REPLACE VIEW secto_elem_global_result (nom_sect, niveau, effectifs)
AS
(SELECT t.nom_sect, t.niveau, t.effectifs
FROM (SELECT 
"SIMULATION_SECTEURS_ECOLES_ELEMENTAIRES".id AS nom_sect,
CASE
	WHEN "BD_EFFECTIF_SCOLAIRE_SIMULATION_CLICHY_L932".inscr_2016 = 'CP' THEN 'niveau_1_CP'
	WHEN "BD_EFFECTIF_SCOLAIRE_SIMULATION_CLICHY_L932".inscr_2016 = 'CE1' THEN 'niveau_2_CE1'
	WHEN "BD_EFFECTIF_SCOLAIRE_SIMULATION_CLICHY_L932".inscr_2016 = 'CE2' THEN 'niveau_3_CE2'
	WHEN "BD_EFFECTIF_SCOLAIRE_SIMULATION_CLICHY_L932".inscr_2016 = 'CM1' THEN 'niveau_4_CM1'
	WHEN "BD_EFFECTIF_SCOLAIRE_SIMULATION_CLICHY_L932".inscr_2016 = 'CM2' THEN 'niveau_5_CM2'
	ELSE 'non_elem'
end AS niveau,
count(*) AS effectifs
FROM postgres.postgres."BD_EFFECTIF_SCOLAIRE_SIMULATION_CLICHY_L932" 
INNER JOIN postgres.postgres."SIMULATION_SECTEURS_ECOLES_ELEMENTAIRES"
ON ST_WITHIN("BD_EFFECTIF_SCOLAIRE_SIMULATION_CLICHY_L932".geometry, "SIMULATION_SECTEURS_ECOLES_ELEMENTAIRES".geometry2)
GROUP BY "SIMULATION_SECTEURS_ECOLES_ELEMENTAIRES".id, "BD_EFFECTIF_SCOLAIRE_SIMULATION_CLICHY_L932".inscr_2016) t
GROUP BY t.nom_sect, t.niveau, t.effectifs
ORDER BY t.nom_sect, t.niveau);

24/02/2016, 08h47
escartefigue

En marge de votre sujet, le choix des noms de table et colonnes est bien peu pratique :
- nom de tables à rallonge
- un nom de table tel que BD_EFFECTIF_SCOLAIRE_SIMULATION_CLICHY_L932, si vous voulez étendre l'utilisation à d'autres communes, c'est peu pratique
- un nom de colonne tel que "inscr_2016" vous oblige à revoir vos requêtes chaque année ...
Y avait il une raison particulière à ces choix ?
24/02/2016, 09h03
white_shadow90

Bonjour,

Effectivement, vous avez raison concernant les noms des colonnes, notamment sur le nom de la commune.
Concernant le niveau, l'idée est de glisser vers inscr_2017 pour réaliser la vue de l'année suivante. Mais je ne m'y connais pas du tout en matière de fonction pour rendre ces paramètres dynamiques.
L'idée à terme serait d'arriver à créer des vues automatiquement pour tous les champs qui commencent par "inscr_"
Ainsi, on aurait une vue secto_elem_global_result_inscr_X, une vue secto_elem_global_tableau_inscr_X ou X correspond à l'année indiquée dans les différents champs inscr_ de la table "SIMULATION_SECTEURS_ECOLES_ELEMENTAIRES".

Bonjour,
Pour pallier la longueur des noms de tables il existe les alias.
Votre requete telle quelle s'écrirait avec alias

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
    CREATE OR REPLACE VIEW secto_elem_global_result (nom_sect, niveau, effectifs)
    AS
    (SELECT t.nom_sect, t.niveau, t.effectifs
    FROM (SELECT 
	    S.id AS nom_sect,
	    CASE
	    	WHEN E.inscr_2016 = 'CP' THEN 'niveau_1_CP'
	    	WHEN E.inscr_2016 = 'CE1' THEN 'niveau_2_CE1'
	    	WHEN E.inscr_2016 = 'CE2' THEN 'niveau_3_CE2'
	    	WHEN E.inscr_2016 = 'CM1' THEN 'niveau_4_CM1'
	    	WHEN E.inscr_2016 = 'CM2' THEN 'niveau_5_CM2'
	    	ELSE 'non_elem'
	    end AS niveau,
	    count(*) AS effectifs
	    FROM postgres.postgres."BD_EFFECTIF_SCOLAIRE_SIMULATION_CLICHY_L932" AS E
	    INNER JOIN postgres.postgres."SIMULATION_SECTEURS_ECOLES_ELEMENTAIRES" as S
	    ON ST_WITHIN(E.geometry, S.geometry2)
	    GROUP BY S.id, E.inscr_2016
	) t
    GROUP BY t.nom_sect, t.niveau, t.effectifs
    ORDER BY t.nom_sect, t.niveau);

Et en fait il faut faire le comptage en dehors de la sous requete T, ce qui donne

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
    CREATE OR REPLACE VIEW secto_elem_global_result (nom_sect, niveau, effectifs)
    AS
    (SELECT t.nom_sect, t.niveau,count(*) AS effectifs
    FROM (SELECT 
	    S.id AS nom_sect,
	    CASE
	    	WHEN E.inscr_2016 = 'CP' THEN 'niveau_1_CP'
	    	WHEN E.inscr_2016 = 'CE1' THEN 'niveau_2_CE1'
	    	WHEN E.inscr_2016 = 'CE2' THEN 'niveau_3_CE2'
	    	WHEN E.inscr_2016 = 'CM1' THEN 'niveau_4_CM1'
	    	WHEN E.inscr_2016 = 'CM2' THEN 'niveau_5_CM2'
	    	ELSE 'non_elem'
	    end AS niveau,
 
	    FROM postgres.postgres."BD_EFFECTIF_SCOLAIRE_SIMULATION_CLICHY_L932" AS E
	    INNER JOIN postgres.postgres."SIMULATION_SECTEURS_ECOLES_ELEMENTAIRES" as S
	    ON ST_WITHIN(E.geometry, S.geometry2)
 
	) t
    GROUP BY t.nom_sect, t.niveau
    ORDER BY t.nom_sect, t.niveau);

Je précise que je connais le SQL mais pas postgreSQL.
Cordialement
Soazig

Rebonjour,
Une autre solution qui utilise une table de correspondance
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 CREATE OR REPLACE VIEW secto_elem_global_result (nom_sect, niveau, effectifs) AS ( With Correspondance As ( Select 'CP' as Classe ,'niveau_1_CP' as niveau union Select 'CE1' as Classe ,'niveau_2_CE1' as niveau union Select 'CE2' as Classe ,'niveau_3_CE2' as niveau union Select 'CM1' as Classe ,'niveau_4_CM1' as niveau union Select 'CM2' as Classe ,'niveau_5_CM2' as niveau ) SELECT s.nom_sect, COALESCE(c.niveau,'non_elem') as niveau,count(*) AS effectifs FROM postgres.postgres."BD_EFFECTIF_SCOLAIRE_SIMULATION_CLICHY_L932" AS E INNER JOIN postgres.postgres."SIMULATION_SECTEURS_ECOLES_ELEMENTAIRES" as S ON ST_WITHIN(E.geometry, S.geometry2) Left outer join Correspondance c on E.inscr_2016=C.Classe ) t GROUP BY s.nom_sect, COALESCE(c.niveau,'non_elem') ORDER BY t.nom_sect, COALESCE(c.niveau,'non_elem') );
Je vois encore 2 autres façon d'écrire cette vue, mais je vais m'arrêter là.
Je suis d'accord avec Escartefigue il y a un problème de conception de base de données

Citation:

Effectivement, vous avez raison concernant les noms des colonnes, notamment sur le nom de la commune.
Concernant le niveau, l'idée est de glisser vers inscr_2017 pour réaliser la vue de l'année suivante. Mais je ne m'y connais pas du tout en matière de fonction pour rendre ces paramètres dynamiques.
L'idée à terme serait d'arriver à créer des vues automatiquement pour tous les champs qui commencent par "inscr_"
Ainsi, on aurait une vue secto_elem_global_result_inscr_X, une vue secto_elem_global_tableau_inscr_X ou X correspond à l'année indiquée dans les différents champs inscr_ de la table "SIMULATION_SECTEURS_ECOLES_ELEMENTAIRES".

S'il y a plusieurs colonnes inscr_2017, inscr_2016 etc, c'est que l'année doit être une colonne de la table .
On aurait une ligne pour 2016, et éventuellement une pour 2017 et après au niveau requete il suffit d'un where annee=2016

Oups, je n'avais pas vu, désolée pour le déterrage de topic, avec les années j'aurais du m'en douter.
Cordialement
Soazig

26/04/2018, 09h45
SQLpro

Vous commettez de nombreuses erreur dans la création de votre vue :
1) ORDER BY est interdit dans une vue. Cela n'a aucun intérêt car une vue étant une table virtuelle, vous pouvez faire une requête sur cette vue en ayant un ordre de tri différent.
2) pour forcer le ORDER BY, vous ajoutez des parenthèses inutiles
3) les chaines de caractères doivent être délimitées par des apostrophes ' et non des guillemets "
4) effectivement le nom des tables est non conforme à la norme SQL et vous obligera à délimiter le nom des tables (les noms des éléments des objets du SQL est appelé identifiant SQL : nom de table, de vue, de colonne...) par des apostrophes

RAPPEL :
chaines de caractères (paramètres) => délimité par des apostrophes
identifiants SQL (nom des tables, colonnes...) => délimité par des guillemets.

Pour un cours sur le SQL, lisez mes bouquins ou mon site web : https://sqlpro.developpez.com/

A +