Discussion :

[antonomase]

Bonjour,

J'ai une requête Oracle 10g assez classique avec une sommation et un group by

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
 
select sum(a.lasteffectif) as SUMEFF,
       c.DEPA_CODE,
       d.libelle as PAYS
from etablissement a,
     commune c,
     pays d
where a.commune_code = c.codeinsee
  and c.depa_code in ('01','02','03','04')
  and a.nationalite = d.codeiso2
group by c.depa_code, d.libelle
order by d.libelle, c.depa_code

qui me renvoie très classiquement

Code :

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

1
2
3
4
5
6
7
8
 
39    03 AFRIQUE DU SUD 
2821  01 ALLEMAGNE 
894   02 ALLEMAGNE 
17258 03 ALLEMAGNE 
1068  04 ALLEMAGNE 
101   03 AUTRICHE 
469   04 AUTRICHE

Pour une utilisation très spécifique dans un moteur de templates HTML, j'ai absolument besoin que des null soient indiqués à la place de la somme là où il n'existe aucun couple (depa_code, pays). Plus précisément qu'il y ait toujours 4 lignes pour chaque pays, correspondant aux 4 départements.
En résumé, je veux ça :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
 
null  01 AFRIQUE DU SUD 
null  02 AFRIQUE DU SUD 
39    03 AFRIQUE DU SUD 
null  04 AFRIQUE DU SUD 
2821  01 ALLEMAGNE 
894   02 ALLEMAGNE 
17258 03 ALLEMAGNE 
1068  04 ALLEMAGNE 
null  01 AUTRICHE 
null  02 AUTRICHE 
101   03 AUTRICHE 
469   04 AUTRICHE

J'ai commencé à regarder du côté des group by cube et autres group by rollup qui font des choses très sympathiques, mais pas ce que je cherche.

Est-ce que quelqu'un aurait une solution ? Peut-être que du côté des MODEL PARTITION DIMENSION ?

Merci de votre aide

[nuke_y]

Peut-être avec une jointure ouverte ou la fonction NVL.

[antonomase]

nvl transforme un null en chaîne, mais le null existe.
Là, il faut créer une ligne avec une partie à null.

Quant à la jointure ouverte, non, ça ne rajoute rien non plus.

[orafrance]

normalement la jointure externe fonctionne parfaitement. Qu'as-tu essayé pour que ça ne fonctionne pas ?

[remi4444]

y'a un truc qui me choque dans ta requête, tu fais un parcours de jointures

d -> a -> c

Or, ta table A contient des enregistrements facultatifs (là ou tu veux des null) donc si l'enregistrement n'existe pas, comment veux tu que le système fasse la jointure a -> c et donc determiner le bon DEPA_CODE. Fonctionnellement ce que tu demande n'est pas possible...

Il me semble qu'une commune doit appartenir à un pays unique non ? donc il doit bien y avoir cette information dans la table C...

si c'est le cas, il faut alors faire alors une jointure normale entre D et C puis une jointure externe entre C et A.

Si il n'y a pas de lien entre C et D, alors ça veux dire que tu dois faire un produit cartésien !! (pas de lien, c'est pas de lien... ). Dans ce cas une methode est de passer par une fonction qui elle, te fera un comptage individuellement, mais fonctionnellement, ça parait complètement abhérant...

[nuke_y]

Quand je parlais de jointure ouverte je voyais effectivement un produit cartésien, qui est pour moi le seul moyen de récupérer ça. J'y arrive mais ensuite c'est pour la somme que ça pose problème (elle est multipliée par toutes les lignes "en trop" du produit cartésien).

[antonomase]

J'ai à peu près tout essayé avec cette syntaxe

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
 
SELECT sum(a.lasteffectif) AS SUMEFF,
       c.DEPA_CODE,
       d.libelle AS PAYS
FROM etablissement a,
     commune c,
     pays d
WHERE a.commune_code = c.codeinsee (+)
  AND c.depa_code IN ('01','02','03','04')
  AND a.nationalite = d.codeiso2
GROUP BY c.depa_code, d.libelle
ORDER BY d.libelle, c.depa_code

et avec celle-ci

Code :

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

1
2
3
4
5
6
7
8
9
10
 
SELECT sum(a.lasteffectif) AS SUMEFF,
       c.DEPA_CODE,
       d.libelle AS PAYS
FROM etablissement a left join commune c on a.commune_code = c.codeinsee,
     pays d
WHERE c.depa_code IN ('01','02','03','04')
  AND a.nationalite = d.codeiso2
GROUP BY c.depa_code, d.libelle
ORDER BY d.libelle, c.depa_code

sur la jointure etablissement-commune, sur la jointure etablissement-pays et sur les deux.
Cela ne rajoute jamais des lignes avec des sommes nulles.

J'ai trouvé cet exemple

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 times.time_id, product, quantity FROM inventory 
   PARTITION BY  (product) 
   RIGHT OUTER JOIN times ON (times.time_id = inventory.time_id) 
   WHERE times.time_id BETWEEN TO_DATE('01/04/01', 'DD/MM/YY') 
      AND TO_DATE('06/04/01', 'DD/MM/YY') 
   ORDER BY  2,1; 
 
TIME_ID   PRODUCT      QUANTITY
--------- ---------- ----------
01-APR-01 bottle             10
02-APR-01 bottle
03-APR-01 bottle
04-APR-01 bottle
05-APR-01 bottle
06-APR-01 bottle             10
06-APR-01 bottle              8
01-APR-01 can                10
01-APR-01 can                15
02-APR-01 can
03-APR-01 can

Mais encore une fois, c'est la récupération d'une colonne dont la valeur est nulle et pas le calcul d'une colonne sum.

[antonomase]

Il me semble qu'une commune doit appartenir à un pays unique non ? donc il doit bien y avoir cette information dans la table C...

Non. On a des établissements qui sont implantés dans des communes et ces établissements (entreprises) ont des nationalités qui n'ont rien à voir avec les communes. Exemple : IBM est une entreprise sur la commune de Puteaux et avec une nationalité US ; et dans la commune de Puteaux, tu as également Siemens qui a une nationalité allemande.

Je ne rentre pas dans le détail, mais le but fonctionnel de cette requête est d'obtenir des un tableau croisé des sommes d'effectifs des entreprises en fonction de la nationalité (en ligne) sur 4 départements fixes (en colonne). Et avec les outils utilisés destinés à des utilisateurs non informaticiens et qui possèdent donc forcément peu de fonctionnalités, je suis obligé de leur fournir le tableau indiqué dans mon premier post.

Ce n'est pas une jointure d -> a -> c, mais deux jointures : une a -> c et l'autre a -> d (à moins que je me sois gouré parce que j'ai simplifié la requête présentée ici par rapport à la requêtre originale).

Avec un traitement dans un langage tel que PHP, je peux récupérer le tableau, regarder la liste des départements différents et recréer les lignes manquantes. Mais le but est de le faire directement en SQL, si c'est possible. Peut-être les fonctions de data warehousing d'Oracle, mais je ne les maîtrise pas bien.

[nuke_y]

aié, j'ai trouvé :

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
75
76
SQL> DROP TABLE PAYS;
 
Table supprimée.
 
SQL> DROP TABLE DEPT;
 
Table supprimée.
 
SQL> CREATE TABLE PAYS (NOM_PAYS VARCHAR2(10));
 
Table créée.
 
SQL> CREATE TABLE DEPT (NOM_PAYS VARCHAR2(10), NUM_DEPT NUMBER(2), VALUE NUMBER(
5));
 
Table créée.
 
SQL> INSERT INTO PAYS VALUES ('FRANCE');
 
1 ligne créée.
 
SQL> INSERT INTO PAYS VALUES ('ALLEMAGNE');
 
1 ligne créée.
 
SQL> INSERT INTO DEPT VALUES ('FRANCE',1, 10);
 
1 ligne créée.
 
SQL> INSERT INTO DEPT VALUES ('FRANCE',2, 10);
 
1 ligne créée.
 
SQL> INSERT INTO DEPT VALUES ('FRANCE',3, 10);
 
1 ligne créée.
 
SQL> INSERT INTO DEPT VALUES ('FRANCE',4, 20);
 
1 ligne créée.
 
SQL> INSERT INTO DEPT VALUES ('ALLEMAGNE',2, 20);
 
1 ligne créée.
 
SQL> SELECT * FROM PAYS;
 
NOM_PAYS
----------
FRANCE
ALLEMAGNE
 
SQL> SELECT * FROM DEPT;
 
NOM_PAYS     NUM_DEPT      VALUE
---------- ---------- ----------
FRANCE              1         10
FRANCE              2         10
FRANCE              3         10
FRANCE              4         20
ALLEMAGNE           2         20
 
SQL> SELECT T1.NOM_PAYS, T2.NUM_DEPT, SUM(CASE WHEN T1.NOM_PAYS=T2.NOM_PAYS THEN VALUE ELSE 0 END) FROM PAYS T1, DEPT T2 GROUP BY T1.NOM_PAYS, T2.NUM_DEPT;
 
NOM_PAYS     NUM_DEPT SUM(CASEWHENT1.NOM_PAYS=T2.NOM_PAYSTHENVALUEELSE0END)
---------- ---------- -----------------------------------------------------
FRANCE              1                                                    10
FRANCE              2                                                    10
FRANCE              3                                                    10
FRANCE              4                                                    20
ALLEMAGNE           1                                                     0
ALLEMAGNE           2                                                    20
ALLEMAGNE           3                                                     0
ALLEMAGNE           4                                                     0
 
8 ligne(s) sélectionnée(s).

Evidemment, s'il ne faut ça ne correspond pas du tout à ton besoin...

[remi4444]

d'accord, j'ai été trompé par le nom des tables... désolé....

donc tu veux bien un produit cartésien...

En fait tu veux:

(D x C) -> A

tu peux donc essayer:

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
 
SELECT sum(a.lasteffectif) AS SUMEFF,
       V.DEPA_CODE,
       V.libelle AS PAYS
FROM etablissement a,
    ( select c.DEPA_CODE, 
               c.codeinsee,
               d.codeiso2, 
               d.libelle as PAYS 
      from  commune c,  pays d
      where c depa_code IN ('01','02','03','04')
    ) V
WHERE V.codeinsee = a.commune_code (+)
  AND  V.codeiso2 = a.nationalite (+)
GROUP BY v.depa_code, v.libelle
ORDER BY v.libelle, v.depa_code

ça devrais répondre à ton besoin.
Attention cependant au problème de performance car c'est le genre de vue qui se calcule entièrement meme si on ne veux en afficher qu'une toute petite partie dans le code.

Si tu veux en faire une vue et que tu as des problèmes de performance, je te conseille de la jouer "brutal" avec un appel de fonction.

exemple:

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
 
 
create or replace function TA_SOMME (p_insee varchar2, p_iso2 varchar2) return number is
l_sum number;
BEGIN 
 select SUM(lasteffectif) finto l_sum rom etablissement 
  where commune_code = p_insee and nationalite = p_iso2;
 return l_sum;
END;
/
 
CREATE OR REPACE VIEW TA_VUE as 
 select     TA_SOMME ( c.codeinsee, d.codeiso2)  AS SUMEFF
              c.DEPA_CODE, 
              d.libelle as PAYS 
   from  commune c,  pays d
 where c depa_code IN ('01','02','03','04')
 ORDER BY d.libelle, c.depa_code;

[antonomase]

Merci à tous.
Y a les gosses qui gueulent parce qu'ils ont faim.

Je teste ça demain et je vous tiens au courant.

mais encore une fois, merci

[antonomase]

J'ai pas pu résister ...

@nuke_y
Super la syntaxe avec le case ; je ne connaissais pas du tout ce genre de syntaxe. C'est exactement ça et ça fonctionne.

@remi4444
je n'ai pas essayé ta syntaxe avec la vue, ça a l'air un peu plus compliqué. Mais je vais quand même essayer.

Je ne peux pas passer par des fonctions et du PL/SQL. En fait, c'est destiné à un outil où ce sont les utilisateurs eux-même qui se créent leurs propres pages HTML dynamiques à partir de requêtes SQL. Ces quelques utilisateurs avertis -mais non informaticiens- ont eu une formation SQL de base pour les requêtes courantes qui correspondent à 98% de leurs besoins et on était tombé sur LE cas tordu d'un tableau croisé. Il va donc déjà falloir que je leur explique ces syntaxes "avancées", mais je ne peux pas aller plus loin.

Encore merci à tous pour cette résolution aussi rapide