Somme de colonnes qui n'ont pas le même nombre de champs

**Eleven O'Clock** · 30/09/2010, 19h17

Bonjour à tous,

Je rencontre actuellement un problème sur l'une de mes requêtes : je cherche en effet à créer une vue me permettant d'afficher des consommations de la semaine précédente et des productions, ainsi qu'un bilan des deux (production-consommation).

Pour ce faire, j'utilise la requête 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
18
19
20
21
select 1 id, 'Energie produite' energie, pd.prd_valeur valeur, pd.prd_date jour from wbx_prod pd
where pd.prd_date BETWEEN trunc(sysdate, 'iw') - 7
                    AND trunc(sysdate, 'iw') - 1/24/60/60
union
select 2 id, 'Energie consommée' energie, sum(cs.cso_valeur) valeur, cs.cso_date jour from gtc_conso cs
where cs.cso_date BETWEEN trunc(sysdate, 'iw') - 7
                    AND trunc(sysdate, 'iw') - 1/24/60/60
group by cs.cso_date
union
select 3, 'Bilan énergétique' energie, sum(valeur), jour from (
select 1 id, 'Energie produite' energie, pd.prd_valeur valeur, pd.prd_date jour from wbx_prod pd
where pd.prd_date BETWEEN trunc(sysdate, 'iw') - 7
                    AND trunc(sysdate, 'iw') - 1/24/60/60
union
select 2 id, 'Energie consommée' energie, -cs.cso_valeur valeur, cs.cso_date jour from gtc_conso cs
where cs.cso_date BETWEEN trunc(sysdate, 'iw') - 7
                    AND trunc(sysdate, 'iw') - 1/24/60/60
)
group by jour
order by id
;

Le problème est que je n'ai pas toutes les semaines le même nombre de données de consommations que de production. L'opération ne peut alors pas s'effectuer, puisqu'il n'y a pas le même nombre de champs dans les 2 colonnes.
Auriez-vous une idée de comment je pourrais modifier ma requête afin d'avoir (0 - conso) s'il manque la donnée de production et (prod - 0) s'il manque la donnée de conso ?

Pour info, voici 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 celle de ma table wbx_prod :

"Column Name","Data Type","Nullable","Data Default","COLUMN ID","Primary Key","COMMENTS"
"ID_PROD","NUMBER(10,0)","No","","1","1","Identifiant de la production"
"PRD_VALEUR","NUMBER(8,1)","No","","2","","Valeur de la production (en kWh)"
"FK_TYPE_PRD","NUMBER(10,0)","No","","3","","ID du type de production, cle etrangere se rapportant a la table CTRL_TYPE_PRD"
"PRD_DATE","DATE","No","","4","","Date du releve de consommation"

Merci d'avance !

Invité · 30/09/2010, 19h21

Envoyé par Eleven O'Clock

Auriez-vous une idée de comment je pourrais modifier ma requête afin d'avoir (0 - conso) s'il manque la donnée de production et (prod - 0) s'il manque la donnée de conso ?

Utilise le case when...
http://sheikyerbouti.developpez.com/...=Chap1#L1.2.12

**Waldar** · 30/09/2010, 19h23

Un petit jeu d'essai ?

Sinon à vue de nez, une table calendrier avec une jointure externe devrait faire l'affaire.

**Eleven O'Clock** · 01/10/2010, 01h11

Merci pour vos réponses !

Une jointure externe sur une table calendrier ? J'ai du mal à comprendre comment faire...
Et comment mettre en place cette table ?

sinon, pour le CASE WHEN, je vais chercher...

EDIT :

Je me suis créé un semblant de table calendrier, puisque ma requête porte sur la semaine en cours avec la requête 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
CREATE OR REPLACE FORCE VIEW "GTCDATAS"."TESTCAL" ("JOURPLUS") AS 
  select sysdate jourplus from dual
			union
			select sysdate-1 jourplus from dual
			UNION
			select sysdate-2 jourplus from dual
			UNION
			select sysdate-3 jourplus from dual
			UNION
			select sysdate-4 jourplus from dual
			UNION
			select sysdate-5 jourplus from dual
			UNION
			select sysdate-6 jourplus from dual;

j'obtiens donc ceci :

"JOURPLUS"
"25/09/2010 11:00:55"
"26/09/2010 11:00:55"
"27/09/2010 11:00:55"
"28/09/2010 11:00:55"
"29/09/2010 11:00:55"
"30/09/2010 11:00:55"
"01/10/2010 11:00:55"

J'essaie alors de faire un OUTER JOIN (ma requête porte ici sur la semaine en cours) :

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 1 id, 'Energie produite' energie, pd.prd_valeur valeur, pd.prd_date jour FROM wbx_prod pd
where trunc(pd.prd_date, 'iw') = trunc(sysdate, 'iw')
union
SELECT 2 id, 'Energie consommée' energie, sum(cs.cso_valeur) valeur, cs.cso_date jour FROM gtc_conso cs
where trunc(cs.cso_date, 'iw') = trunc(sysdate, 'iw')
GROUP BY cs.cso_date
union
SELECT 3, 'Bilan énergétique' energie, sum(valeur), jour FROM (
SELECT 1 id, 'Energie produite' energie, pd.prd_valeur valeur, pd.prd_date jour FROM wbx_prod pd, testcal tb
where trunc(pd.prd_date, 'iw') = trunc(sysdate, 'iw')
AND pd.prd_date = tb.jourplus (+)
union
SELECT 2 id, 'Energie consommée' energie, -cs.cso_valeur valeur, cs.cso_date jour FROM gtc_conso cs, testcal tb
where trunc(cs.cso_date, 'iw') = trunc(sysdate, 'iw')
AND cs.cso_date = tb.jourplus (+)
)
GROUP BY jour
ORDER BY id
;

Mais je n'obtiens pas ce que je veux, puisque j'ai :

"ID" "ENERGIE" "VALEUR" "JOUR"
"1" "Energie produite" "43,8" "27/09/2010 00:05:00"
"1" "Energie produite" "53" "28/09/2010 10:44:58"
"1" "Energie produite" "145" "30/09/2010 10:08:01"
"1" "Energie produite" "150" "29/09/2010 09:08:05"
"2" "Energie consommée" "86,2" "27/09/2010 00:05:00"
"2" "Energie consommée" "92,6" "30/09/2010 00:05:01"
"2" "Energie consommée" "221,4" "29/09/2010 00:05:00"
"3" "Bilan énergétique" "-221,4" "29/09/2010 00:05:00"
"3" "Bilan énergétique" "-92,6" "30/09/2010 00:05:01"
"3" "Bilan énergétique" "-42,4" "27/09/2010 00:05:00"
"3" "Bilan énergétique" "53" "28/09/2010 10:44:58"
"3" "Bilan énergétique" "145" "30/09/2010 10:08:01"
"3" "Bilan énergétique" "150" "29/09/2010 09:08:05"

On voit que les lignes "bilan énergétique" ne sont pas bonnes...

**Waldar** · 01/10/2010, 13h02

Vous voulez le résultat jour par jour ou regroupé sur la semaine ?

Quelle est votre version d'Oracle ?

**Eleven O'Clock** · 01/10/2010, 13h05

Je voudrais les résultats pour tous les jours, mais que sur la semaine en cours (je compte ensuite faire une autre vue pour la semaine précédente, mois en cours, mois précédent, etc), sachant que dans mes tables conso et prod, j'ai des données sur les derniers mois.

Je travaille sur Oracle 10g.

**Waldar** · 01/10/2010, 15h15

Vous devriez trouver votre bonheur là-dedans mais il faudra bien regarder chacune des briques pour comprendre et reproduire !

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
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
with wbx_prod as
(
select  43.8 as prd_valeur, to_date('20/09/2010 00:05:00', 'dd/mm/yyyy hh24:mi:ss') as prd_date from dual union all
select  53                , to_date('21/09/2010 10:44:58', 'dd/mm/yyyy hh24:mi:ss')             from dual union all
select 145                , to_date('23/09/2010 10:08:01', 'dd/mm/yyyy hh24:mi:ss')             from dual union all
select 150                , to_date('22/09/2010 09:08:05', 'dd/mm/yyyy hh24:mi:ss')             from dual
)
  ,  gtc_conso as
(
select  86.2 as cso_valeur, to_date('20/09/2010 00:05:00', 'dd/mm/yyyy hh24:mi:ss') as cso_date from dual union all
select  92.6              , to_date('23/09/2010 00:05:01', 'dd/mm/yyyy hh24:mi:ss')             from dual union all
select 221.4              , to_date('22/09/2010 00:05:00', 'dd/mm/yyyy hh24:mi:ss')             from dual
)
  ,  calendar as
(
select level + trunc(sysdate, 'iw') - 8 as dt
  from dual
connect by level <= 7
)
  ,  sr as
(
  SELECT 1 as id,
         'Energie produite' as energie,
         coalesce(pd.prd_valeur, 0) as valeur,
         cl.dt as jour
    FROM calendar cl
         left outer join wbx_prod pd
           on trunc(pd.prd_date) = cl.dt
   WHERE cl.dt BETWEEN trunc(sysdate, 'iw') - 7
                   AND trunc(sysdate, 'iw') - 1/24/60/60
   UNION ALL
  SELECT 2,
         'Energie consommée',
         coalesce(-1 * cs.cso_valeur, 0),
         cl.dt
    FROM calendar cl
         left outer join gtc_conso cs
           on trunc(cs.cso_date) = cl.dt
   WHERE cl.dt BETWEEN trunc(sysdate, 'iw') - 7
                   AND trunc(sysdate, 'iw') - 1/24/60/60
)
  select trunc(jour) as jour,
         case grouping(id) when 1 then 3 else id end as id,
         case grouping(energie) when 1 then 'Bilan énergétique' else energie end as energie,
         case id when 2 then -1 else 1 end * sum(valeur) as valeur
    from sr
group by trunc(jour), rollup(id, energie)
  having grouping(id) = 1
      or grouping(energie) = 0
order by jour asc, id asc;
 
JOUR         ID    ENERGIE             VALEUR
----------- ----- ------------------- --------
20/09/2010    1    Energie produite      43.8
20/09/2010    2    Energie consommée     86.2
20/09/2010    3    Bilan énergétique   - 42.4
21/09/2010    1    Energie produite      53
21/09/2010    2    Energie consommée      0
21/09/2010    3    Bilan énergétique     53
22/09/2010    1    Energie produite     150
22/09/2010    2    Energie consommée    221.4
22/09/2010    3    Bilan énergétique   - 71.4
23/09/2010    1    Energie produite     145
23/09/2010    2    Energie consommée     92.6
23/09/2010    3    Bilan énergétique     52.4
24/09/2010    1    Energie produite       0
24/09/2010    2    Energie consommée      0
24/09/2010    3    Bilan énergétique      0
25/09/2010    1    Energie produite       0
25/09/2010    2    Energie consommée      0
25/09/2010    3    Bilan énergétique      0
26/09/2010    1    Energie produite       0
26/09/2010    2    Energie consommée      0
26/09/2010    3    Bilan énergétique      0

Somme de colonnes qui n'ont pas le même nombre de champs

SQL Oracle

Vue hybride

Discussions similaires

Partager

Partager