Discussion :

[su4p aka]

Contexte : la *** a un parc de 50 vehicules situés dans différentes villes. La *** souhaiterais un site afin de suivre leurs interventions et leurs consomations. mais aussi des statistiques d'aide à la prise de décision :
je souhaiterais pouvoir calculer le cout des consomations et des interventions des vehicules de chacunes des villes afin d'emettre un graphique ou l'on pourais voir la ville qui dépense le plus etc...

Ma vue pour le calcul des interventions sur les vehicules organisés par ville :

Code :

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

1
2
3
4
5
6
7
8
9
CREATE VIEW dbo.vueClassementVillePlusCherIntervention
AS
SELECT TOP 100 PERCENT dbo.ville.vil_libelle, SUM(dbo.commande.cmd_montant_ttc) AS somInt
FROM dbo.ville INNER JOIN
dbo.vehicule ON dbo.ville.vil_id = dbo.vehicule.veh_vil_id INNER JOIN
dbo.Interventions ON dbo.vehicule.veh_immat = dbo.Interventions.int_veh_immat INNER JOIN
dbo.commande ON dbo.Interventions.int_id = dbo.commande.cmd_int_id
GROUP BY dbo.ville.vil_libelle
ORDER BY somInt DESC

Ma vue pour le calcul des consomations sur les vehicules organisés par ville :

Code :

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

1
2
3
4
5
6
7
CREATE VIEW dbo.vueClassementVillePlusCherConso
AS
SELECT dbo.ville.vil_libelle, SUM(dbo.consomation.conso_montant) AS somConso
FROM dbo.ville INNER JOIN
dbo.vehicule ON dbo.ville.vil_id = dbo.vehicule.veh_vil_id INNER JOIN
dbo.consomation ON dbo.vehicule.veh_immat = dbo.consomation.conso_veh_immat
GROUP BY dbo.ville.vil_libelle

Ma vue pour le calcul des consomations sur les vehicules organisés par ville :

Code :

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

1
2
3
4
5
6
7
8
CREATE VIEW dbo.vueClassementVillePlusCher
AS
SELECT TOP 100 PERCENT dbo.vueClassementVillePlusCherIntervention.vil_libelle,
( dbo.vueClassementVillePlusCherIntervention.somInt + dbo.vueClassementVillePlusCherConsommation.somConso) AS somCout
FROM dbo.vueClassementVillePlusCherIntervention,
dbo.vueClassementVillePlusCherConsommation
 
ORDER BY somCout DESC

Probleme : mes villes sont en double (normal il n'y a pas de group by) donc :

Code :

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

1
2
3
4
5
6
7
8
9
10
CREATE VIEW dbo.vueClassementVillePlusCher
AS
SELECT TOP 100 PERCENT dbo.vueClassementVillePlusCherIntervention.vil_libelle,
( dbo.vueClassementVillePlusCherIntervention.somInt + dbo.vueClassementVillePlusCherConsommation.somConso) AS somCout
FROM dbo.vueClassementVillePlusCherIntervention,
dbo.vueClassementVillePlusCherConsommation
 
group by dbo.vueClassementVillePlusCherIntervention.vil_libelle
 
ORDER BY somCout DESC

Probleme : MICROSOFT SQL-DMO SQLState:42000
Erreur 8120 la colonne somInt n'est pas valide dans la liste de sélection parce qu'elle n'est pas contenue dans une fonction d'agrégation ou dans la clause du GROUP BY. la colonne somConso n'est pas valide dans la liste de sélection parce qu'elle n'est pas contenue dans une fonction d'agrégation ou dans la clause du GROUP BY.

Merci pour la lecture et l'aide
cdlt

[onc31]

La solution n'est pas de rajouter un GROUP BY mais de faire une jointure correcte ;-)

Code :

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

1
2
3
4
5
6
7
 
CREATE VIEW dbo.vueClassementVillePlusCher
AS
SELECT dbo.vueClassementVillePlusCherIntervention.vil_libelle,
( dbo.vueClassementVillePlusCherIntervention.somInt + dbo.vueClassementVillePlusCherConsommation.somConso) AS somCout
FROM dbo.vueClassementVillePlusCherIntervention
LEFT JOIN dbo.vueClassementVillePlusCherConsommation ON dbo.vueClassementVillePlusCherConsommation.vil_libelle=dbo.vueClassementVillePlusCherIntervention.vil_libelle

Nb : on ne peut pas mettre de ORDER BY dans une vue (ça n'a pas de sens)... il faut le préciser lors de l'utilisation de la vue.
Sauf si on VEUT mettre un "TOP x PERCENT" dans la vue, mais dans ce cas on met un %age différent de 100 et un ORDER BY sur le montant et non le libellé de la ville ;-)

Quelques remarques :
- Niveau performance ça doit pas être top... A mon avis il vaudrait mieux faire une 3ème vue qui n'utilise pas les 2 premières...
- J'ai fais la jointure sur le libellé de la ville parceque c'est ce que renvoient les vues, mais ce serait surement plus performant avec le code de la ville
- Utiliser des alias de tables rendraient le code plus lisible... surtout avec ces noms de vues à rallonge ;-) Je ne les ai pas utilisé pour rester le plus proche possible de ta requête initiale.
- Faudrait supprimer le TOP 100 PERCENT de la première vue aussi