Pb avec case when !

**Brice Yao** · 03/08/2005, 17h37

Salut à tous!
J'ai un pb en sql avec une requete qui ne me donne pas le resultat souhaité.
Voici ma requette:

select r.numcarte,r.nomprenoms,datepart(wk,datejour)as 'semaine',datepart(dw,datejour)as 'jour',
CASE datepart(dw,datejour) WHEN 1 THEN r.he+'-'+r.hs END AS lundi,
CASE datepart(dw,datejour) WHEN 2 THEN r.he+'-'+r.hs END AS Mardi,
CASE datepart(dw,datejour) WHEN 3 THEN r.he+'-'+r.hs END AS Mercredi,
CASE datepart(dw,datejour) WHEN 4 THEN r.he+'-'+r.hs END AS Jeudi,
CASE datepart(dw,datejour) WHEN 5 THEN r.he+'-'+r.hs END AS Vendredi
from resultatsfin r, employes e
where r.numcarte=e.emp_numero_badge and datepart(wk,datejour)='1'
group by r.numcarte,r.nomprenoms,datepart(wk,datejour),datepart(dw,datejour)

et voici le resultat:

numcarte nomprenoms semaine jour lundi mardi mercredi ...
num1 nom1 1 1 he-hs null null ...
num1 nom1 1 2 null he-hs null ...
num1 nom1 1 3 null null he-hs ...
num2 nom2 1 1 he-hs null null ...
num2 nom2 1 2 null he-hs null ...
num2 nom2 1 3 null null he-hs ...

Je vuex pourtant avoir ceci et je n'y arrive pas:

numcarte nomprenoms semaine lundi mardi mercredi ...
num1 nom1 1 he-hs he-hs he-hs ...
num2 nom2 1 he-hs he-he he-hs ...

c'est-a-dire pour chaque nom avoir l'heure d'entrée (he)et de l'heure de sortie (hs)
dans la période d'une semaine sur la même ligne.

Voulez-vous m'aider SVP !
merci.

**Médiat** · 03/08/2005, 23h01

Essaye de retirer ) datepart(dw,datejour) du GROUP BY

**Magnus** · 04/08/2005, 08h33

Au passage, je voudrais juste poser une question (à laquelle Médiat va surement répondre

) sur la manière dont la jointure est faite :

Code :

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

1
2
3
...
from resultatsfin r, employes e 
where r.numcarte=e.emp_numero_badge and datepart(wk,datejour)='1' ...

Est-ce que ça ne serait pas plus performant de faire :

Code :

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

1
2
3
...
FROM resultatsfin r
INNER JOIN employes e ON (r.numcarte = e.emp_numero_badge AND datepart(wk,datejour)='1')...

Je dis ça en supposant que dans le cas de Brice Yao un produit cartésien est effectué en premier, puis la clause WHERE effectue un filtre sur les données du produit cartésien alors que dans la solution que je propose, la requête considère directement les données jointes des tables resultatsfin et employes.

**Médiat** · 04/08/2005, 09h01

Magnus :

Il y a un an à peu près j'ai été amené à me poser cette question, une application devant passer d'un serveur ORACLE 8i à ORACLE 9i, je me suis inquiété en me disant que toutes les requêtes (surtout les OUTER JOIN) étant à l'ancienne syntaxe, il faudrait les modifier pour utiliser la syntaxe normée sinon les performances s'écrouleraient. J'ai donc fait tout un tas de tests sur des "grosses tables" (2 à 10 millions de lignes jointes avec 500 000 lignes), les requêtes étant des requêtes "du monde réel", résultat : performances identiques avec les deux méthodes, donc ORACLE sait reconnaître une jointure quelque soit la syntaxe utilisée. Nous n'avons donc pas changé les requêtes qui fonctionnent depuis un an sans dégradation de performances (Maxence :

(désolé : private joke)), et bien sur, pour les nouvelles, nous utilisons la syntaxe normée, qui si elle n'est pas plus performante est au moins :
1) normée
2) largement plus lisible (même si j'avais depuis longtemps pris l'habitude de mettre les conditions de jointures avant les conditions fonctionnelles).

Hope this Help

**Brice Yao** · 04/08/2005, 10h27

Salut,

Lorsque je retire datepart(dw,datejour), j'obtient un msg qui dit:

Code :

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

1
2
Serveur : Msg 8120, Niveau 16, État 1, Ligne 1
La colonne 'r.DateJour' 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 GROUP BY.

Je crois savoir que le group by doit regrouper toutes les colonnes de select.

**Médiat** · 04/08/2005, 10h47

J'ai répondu trop vite, il faut aussi l'enlever de la liste des SELECT (sélectionner le jour n'a pas de sens, puisque tu veux la semaine entière), et ajouter un MAX à tous tes CASE WHEN (qui ne changera rien car il n'y a qu'une seule réponse, mais c'est imposé par la syntaxe du GROUP BY)

**Magnus** · 04/08/2005, 11h05

Médiat : merci pour les infos mais j'ai encore un petite question => les constats que tu décrits pour ORACLE s'appliquent-t'ils aussi pour tous les autres SGBD ?

**Médiat** · 04/08/2005, 11h16

Envoyé par Magnus

les constats que tu décrits pour ORACLE s'appliquent-t'ils aussi pour tous les autres SGBD ?

N'ayant pas eu l'occasion de faire des test avec d'autres moteurs, je ne peux être formel, mais je dirais volontiers que les moteurs ayant introduit la syntaxe normée depuis peu (mais ce n'est pas limitatif) devraient fonctionner ainsi (si il fallait tout ré-écrire, ce serait un excellent argument pour ne pas migrer).

Il me semble que SQLPro disait qu'il y a une différence avec SQLServer (à vérifier, surtout au niveau de la version)

La dessus le test est facile, tu fais les deux jointures sur tes deux plus grosses tables avec des contraintes hors jointures très restrictives et tu regardes la différence de performance (attention aux effets de cache dans ce genre de test)

**Brice Yao** · 04/08/2005, 14h37

j'ai fait 2 choses:
1- j'ai enlevé datejour dans le group by et dans le select, voici le msg reçu:

Code :

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

1
2
Serveur : Msg 8120, Niveau 16, État 1, Ligne 1
La colonne 'r.DateJour' 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 GROUP BY.

2- j'ai ajouté MAX à case when et supprimer la clause group by de cette manière:

Code :

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

1
2
3
4
5
6
7
8
select r.numcarte,r.nomprenoms,r.heureentree,r.heuresortie,datepart(wk,r.datejour)as 'semaine',
    CASE datepart(dw,datejour) WHEN 1 THEN MAX(r.heureentree+''+r.heuresortie) END AS Lundi,
    CASE datepart(dw,datejour) WHEN 2 THEN MAX(r.heureentree+''+r.heuresortie) END AS Mardi,
    CASE datepart(dw,datejour) WHEN 3 THEN MAX(r.heureentree+''+r.heuresortie) END AS Mercredi,
    CASE datepart(dw,datejour) WHEN 4 THEN MAX(r.heureentree+''+r.heuresortie) END AS Jeudi,
    CASE datepart(dw,datejour) WHEN 5 THEN MAX(r.heureentree+''+r.heuresortie) END AS Vendredi
from resultatsfin r
INNER JOIN employes e ON (r.numcarte = e.emp_numero_badge AND datepart(wk,r.datejour)='1')

Voici le msg reçu:

Code :

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

1
2
Serveur : Msg 8120, Niveau 16, État 1, Ligne 1
La colonne 'r.numcarte 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 GROUP BY.

et cela pour toutes les colonnes de select.

**Médiat** · 04/08/2005, 15h08

Essaye :

Code :

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

1
2
3
4
5
6
7
8
9
SELECT r.numcarte,r.nomprenoms,datepart(wk,r.datejour) as 'semaine', 
       MAX(CASE datepart(dw,datejour) WHEN 1 THEN r.heureentree+''+r.heuresortie END) AS Lundi, 
       MAX(CASE datepart(dw,datejour) WHEN 2 THEN r.heureentree+''+r.heuresortie END) AS Mardi, 
       MAX(CASE datepart(dw,datejour) WHEN 3 THEN r.heureentree+''+r.heuresortie END) AS Mercredi, 
       MAX(CASE datepart(dw,datejour) WHEN 4 THEN r.heureentree+''+r.heuresortie END) AS Jeudi, 
       MAX(CASE datepart(dw,datejour) WHEN 5 THEN r.heureentree+''+r.heuresortie END) AS Vendredi 
FROM resultatsfin r INNER JOIN employes e ON r.numcarte = e.emp_numero_badge 
WHERE datepart(wk,r.datejour)='1'
GROUP BY r.numcarte,r.nomprenoms,datepart(wk,r.datejour)

**Brice Yao** · 04/08/2005, 19h14

Merci infiniment !!!
ça marche à merveille.

**Magnus** · 05/08/2005, 08h20

C'est noté, merci pour les infos Médiat.

Pb avec case when !

Langage SQL

Discussions similaires

Partager

Partager