Discussion :

[Eleven O'Clock]

Bonjour à tous,

J'ai actuellement un souci avec une requête SQL sur ma base Oracle 10g :

Dans cette base sont poussées tous les jours 5 types de données de consommation.
Je cherche à afficher les données de consommation pour l'année précédente, pour ces 5 types, plus un total de ces consommations.

Or, comme pour l'instant, aucune valeur n'existe pour l'année dernière, les valeurs qui me sont retournées sont nulles. Je n'obtiens donc rien à l'affichage. Mais j'aimerais quand même que soient affichées mes 6 lignes avec pour valeur "0".

J'ai donc utilisé cette requête :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT ctrl_type_cso.type_cso "Type de consommation", sum(gtc_conso.cso_valeur) "Année précédente"
FROM gtc_conso, ctrl_type_cso
WHERE to_number(to_char(cso_date,'YYYY')) = to_number(to_char(sysdate,'YYYY'))-1
AND gtc_conso.fk_type_cso = ctrl_type_cso.id_type_cso
group by ctrl_type_cso.type_cso
union
SELECT 'Total' "Type de consommation", sum(gtc_conso.cso_valeur)
FROM gtc_conso, ctrl_type_cso
WHERE to_number(to_char(cso_date,'YYYY')) = to_number(to_char(sysdate,'YYYY'))-1
AND gtc_conso.fk_type_cso = ctrl_type_cso.id_type_cso
group by to_char(gtc_conso.cso_date,'MONTH')
order by "Type de consommation"
;

et essayé d'y ajouter des NVL, mais rien n'y fait...

Je précise aussi la structure de ma table gtc_conso :

"Column Name" "Data Type" "Nullable" "Data Default" "COLUMN ID" "Primary Key" "COMMENTS"
"ID_CONSO" "NUMBER(10,0)" "No" "" "1" "1" "Identifiant de la consommation"
"CSO_VALEUR" "NUMBER(8,1)" "No" "" "2" "" "Valeur de consommation (en kWh)"
"FK_TYPE_CSO" "NUMBER(10,0)" "No" "" "3" "" "ID du type de consommation, cle etrangere se rapportant a la table CTRL_TYPE_CSO"
"CSO_DATE" "DATE" "No" "" "4" "" "Date du releve de consommation"

et de ma table ctrl_type_cso :

"Column Name" "Data Type" "Nullable" "Data Default" "COLUMN ID" "Primary Key" "COMMENTS"
"ID_TYPE_CSO" "NUMBER(10,0)" "No" "" "1" "1" "Identifiant du type de consommation"
"TYPE_CSO" "VARCHAR2(50 BYTE)" "No" "" "2" "" "Type de consommation (provenance)"

J'espère vous avoir donné tous les éléments nécessaires.
J'ai planché sur le sujet pendant un moment, fait des recherches, mais en vain.

Auriez-vous une idée pour m'aider ?

Merci d'avance !

[Waldar]

Il suffit de faire une jointure externe.

[spdev666]

Tant que la requète ne ramène pas de ligne, il n y aura rien affiché (Nvl travaille sur les données ramenées par la requète ; ca ne change rien au probleme)

Il faut forcer Oracle a ramener qq chose :

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
SELECT type_cso "Type de consommation", sum(Valeur) "Année précédente"
From
(SELECT ctrl_type_cso.type_cso, sum(gtc_conso.cso_valeur) Valeur
FROM gtc_conso, ctrl_type_cso
WHERE to_number(to_char(cso_date,'YYYY')) = to_number(to_char(sysdate,'YYYY'))-1
AND gtc_conso.fk_type_cso = ctrl_type_cso.id_type_cso
GROUP BY ctrl_type_cso.type_cso
Union 
Select type_cso, 0 From ctrl_type_cso)
GROUP BY type_cso
union
SELECT 'Total' "Type de consommation", sum(gtc_conso.cso_valeur)
FROM gtc_conso, ctrl_type_cso
WHERE to_number(to_char(cso_date,'YYYY')) = to_number(to_char(sysdate,'YYYY'))-1
AND gtc_conso.fk_type_cso = ctrl_type_cso.id_type_cso
GROUP BY to_char(gtc_conso.cso_date,'MONTH')
ORDER BY "Type de consommation"
;

J ai modifié la premiere partie de la requete.
Pour le Group By par mois, tu peux utiliser le meme principe si besoin en ajoutant une ligne "fictive" par mois

[Eleven O'Clock]

Oups, pour le Group By par mois, c'est une erreur de ma part, un vestige d'une autre requête, désolé...

Sinon, bravo, et merci ! Mon problème est complètement résolu ! Et ce en un temps record !

Pour ce qui est de la ligne "Total, je peux ici utiliser NVL, puisque c'est bien un (null) qui m'est retourné.

Ma requête sera donc la suivante :

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 type_cso "Type de consommation", sum(Valeur) "Année précédente"
From
(SELECT ctrl_type_cso.type_cso, sum(gtc_conso.cso_valeur) Valeur
FROM gtc_conso, ctrl_type_cso
WHERE to_number(to_char(cso_date,'YYYY')) = to_number(to_char(sysdate,'YYYY'))-1
AND gtc_conso.fk_type_cso = ctrl_type_cso.id_type_cso
GROUP BY ctrl_type_cso.type_cso
Union 
Select type_cso, 0 From ctrl_type_cso)
GROUP BY type_cso
union
SELECT 'Total' "Type de consommation", NVL(sum(gtc_conso.cso_valeur),0)
FROM gtc_conso, ctrl_type_cso
WHERE to_number(to_char(cso_date,'YYYY')) = to_number(to_char(sysdate,'YYYY'))-1
AND gtc_conso.fk_type_cso = ctrl_type_cso.id_type_cso
ORDER BY "Type de consommation"
;

et j'obtiens bien le tableau :

"Type de consommation" "Année précédente"
"Conso. auxiliaire PAC" "0"
"Conso. calories PAC" "0"
"Conso ECS" "0"
"Conso PAC" "0"
"Conso ventilation" "0"
"Total" "0"

Merci encore pour ton aide, peux-tu simplement jeter un dernier coup d'oeil à cette requête, afin que je sois sûr que celle-ci est juste ?
Merci.

[spdev666]

Ca me semble bon

(Sinon, la solution de Waldar etait sans doute meilleur)

[Eleven O'Clock]

J'ai beau me creuser, je n'y arrive pas avec le OUTER JOIN...

J'explore de ce côté là, puisque quand j'essaie d'utiliser la solution de spdev666 dans un SqlDataSource en ASP.NET, j'obtiens : "erreur de syntaxe : identificateur ou identificateur entre guillemets attendu"...

J'essaie donc d'autres méthodes.

EDIT : Effectivement, VS2003 ne supporte pas les requêtes comportant plusieurs FROM...
Waldar, pourrait tu m'expliquer comment utiliser une jointure externe dans ce cas STP ?

[spdev666]

La jointure externe doit ressembler à cela (attention aux critères sur la tables qui est du "coté externe" de la jointure) :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
SELECT ctrl_type_cso.type_cso "Type de consommation", sum(Nvl(gtc_conso.cso_valeur,0)) "Année précédente"
FROM gtc_conso Left Outer Join ctrl_type_cso On gtc_conso.fk_type_cso = ctrl_type_cso.id_type_cso
WHERE (to_number(to_char(cso_date,'YYYY')) = to_number(to_char(sysdate,'YYYY'))-1 Or cso_date Is Null)
GROUP BY ctrl_type_cso.type_cso
union
SELECT 'Total' "Type de consommation", NVL(sum(gtc_conso.cso_valeur),0)
FROM gtc_conso Left Outer Join ctrl_type_cso On gtc_conso.fk_type_cso = ctrl_type_cso.id_type_cso
WHERE (to_number(to_char(cso_date,'YYYY')) = to_number(to_char(sysdate,'YYYY'))-1 Or cso_date Is Null)
ORDER BY "Type de consommation"
;

[Eleven O'Clock]

Qu'entends tu par "attention aux critères" ? Je débute en SQL, et j'ai encore un peu de mal avec ce genre de jointures...

Telle quelle, la requête n'a pas l'air de fonctionner, elle ne me retourne que la ligne "Total : 0". La première partie ne semble donc pas marcher...

[Waldar]

Désolé, je n'ai pas pu suivre ce fil avant ce matin.
Voici ce que donnerait ma solution :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
  SELECT case grouping(ct.type_cso)
           when 1 then 'Total'
           else ct.type_cso
         end as "Type de consommation",
         nvl(sum(gc.cso_valeur), 0) as "Année précédente"
    FROM ctrl_type_cso ct
         LEFT OUTER JOIN gtc_conso gc 
           ON gc.fk_type_cso = ct.id_type_cso
          AND gc.cso_date between add_months(trunc(sysdate, 'YYYY'), -12)
                              and trunc(sysdate, 'YYYY') - 1/24/60/60
GROUP BY rollup(ct.type_cso);

[spdev666]

Autant pour moi, j avais mis la jointure externe du mauvais coté (j ai corrigé mon message précédent).

Par "attention aux critères", je veux dire que quand tu fais une jointure externe, avec une table, les restrictions sur les colonnes de cette table doivent prendre en compte que la colonne peut être vide. Si tu enlèves le "Or cso_date Is Null" de ta requete, elle ne renverra rien.

[Eleven O'Clock]

Au risque de paraître énervant... ça ne fonctionne pas non plus...
EDIT : Je parle de la solution de spdev666, j'essaie celle de Waldar de suite...

[Eleven O'Clock]

La solution de Waldar semble très bien marcher, j'ai toujours une erreur quand je teste la requête dans VS 2003 disant

Erreur de syntaxe : ',' ou 'FROM' attendu après l'alias de colonne ou '*'.

Mais lorsque je génère le projet, la requête me retourne bien ce que je cherche. Je suppose donc qu'il s'agit d'un bug de VS. Bref, ceci n'est pas le sujet. Pour ce qui est de la requête, merci beaucoup !

Par contre, j'aime bien comprendre ce que je fais, et j'ai un peu de mal avec cette requête :

Pourquoi ceci :

Code :

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

1
2
          AND gc.cso_date BETWEEN add_months(trunc(sysdate, 'YYYY'), -12)
                              AND trunc(sysdate, 'YYYY') - 1/24/60/60

est mieux que ceci ? :

Code :

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

WHERE to_number(to_char(cso_date,'YYYY')) = to_number(to_char(sysdate,'YYYY'))-1

J'ai notamment du mal à comprendre la ligne :

Code :

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

AND trunc(sysdate, 'YYYY') - 1/24/60/60

Pourrais-tu m'expliquer ceci ? Merci.

[Waldar]

Parce que si vous avez un index sur cso_date, il ne sera pas utilisé si vous appliquez des fonctions dessus.

Et de manière générale, il vaut mieux modifier les constantes que les colonnes (c'est moins coûteux puisque les calculs ne sont fait qu'une fois), et quand on travaille sur des dates il vaut mieux rester avec des dates !

Le -1/24/60/60 c'est juste pour préciser le 31/12/2009 à 23:59:59.
Quand on soustrait un nombre à une date on soustrait un nombre de jour.
1 jour / 24 = 1 heure , /60 = 1 minute, /60 = 1 seconde !

Si vos dates sont précises à la journée, vous pouvez juste écrire -1.

[Eleven O'Clock]

D'accord, c'est noté ! Merci beaucoup à tous les deux pour votre aide et votre implication !
Je marque le sujet comme résolu.