NVL, base Oracle et conditions

Version imprimable

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:

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 :

Citation:

"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 :

Citation:

"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 !

02/09/2010, 16h58
Waldar

Il suffit de faire une jointure externe.

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:

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

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:

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 :

Citation:

"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.

02/09/2010, 18h01
spdev666

Ca me semble bon

(Sinon, la solution de Waldar etait sans doute meilleur)
02/09/2010, 18h52
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 ?

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

Code:

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"
;

03/09/2010, 09h18
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...
03/09/2010, 10h07
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.

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

Code:

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);

03/09/2010, 10h15
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...
03/09/2010, 10h45
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

Citation:

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:

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:

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

J'ai notamment du mal à comprendre la ligne :

Code:

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

Pourrais-tu m'expliquer ceci ? Merci.
03/09/2010, 11h06
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.
03/09/2010, 11h18
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.