report matriciel avec trous [Résolu]

[gg2vig]

Bonjour

je reformule

ma question de façon plus generale:

soit des ensemble E avec des sous ensemble SE dont le nombre est variable selon les ensembles.

ces sous ensembles SE ont des defaillances...

je veux faire un tableau matriciel global avec les ensembles E en lignes et les sous ensembles SE en colonnes

les comptage de defaillances des sous ensembles N sont dans la matrice.

certains sous ensembles existent mais ont des valeurs N nulles

mais certains sous ensembles n'existent pas pour tel ou tel ensemble.
et je voudrais pouvoir les discriminer par formatage


comment faire ?


EX CI DESSOUS X = inexistant
SE1 SE2 SE3 SE4 SE5 SE6
E1 0 1 0 5 X X
E2 4 2 1 1 1 X
E3 0 0 X X 5 2
E4 1 0 4 X 4 5
E5 2 X X 0 1 0

[salim11]

Salut,

Code :

1
2
 
je voudrais pouvoir les discriminer par formatage

???

Peux-tu nous donner plus de détail?

Si j'ai bien compris tu veux mettre 0 dans les cases null

Peux-tu poster ta requête ?


PS: j'ai vu un cas semblable

[gg2vig]

Bonjour

non 0 signifie 0 defauts pour le poste existant

par contre 'mettre une couleur de remplissage' signifie que la combinaison ligne colonne n'existe pas
car le nombre de P n'est pas constant pour chaque L

explications:
les postes sont codés LxxPxx dans les tables postes et defauts avec jointure 1 à plusieurs sur poste_code=def_poste

Code :

1
2
SELECT substr(poste_code,2,2) L,substr(poste_code,5,2)P
FROM postes

pour construire le tableau

Code :

1
2
SELECT def_poste,count(def_code) FROM defauts
GROUP BY def_poste

pour remplir la matrice

pour l'instant je code en dur dans le trigger de format les code_postes à griser (nombre malgré tout peu evolutif) mais je voulais savoir si on pouvait automatiser cette action

[salim11]

Salut,

C'est pas encore clair, pour un rapport matriciel t'a besoin d'une seule requete qui contient les lignes, les colonnes et les cellules.

qu'elle sont tes lignes,colonnes et cellules ?

[gg2vig]

Code :

1
2
3
4
SELECT substr(poste_code,2,2) L,substr(poste_code,5,2)P,count(def_code)
FROM postes,defauts
WHERE poste_code = def_poste(+)
GROUP BY substr(poste_code,2,2),substr(poste_code,5,2)

les lignes sont L et les colonnes P

toutes les L n'ont pas autant de P

je veux mettre en evidence les cellules count(def_code) ou P n'existe pas pour le L donné

[salim11]

salut,

Qu'elle est ta version d'oracle ?

Si tu as la 9i tu peux essayer ça
(-1) pour les cellules : P n'existe pas ou L n'existe pas

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 
SELECT DISTINCT postes.l, postes.p,
                DECODE (def_code,
                        NULL, -1,
                        COUNT (def_code) OVER (PARTITION BY def_poste)
                       ) cpt
           FROM (SELECT DISTINCT tl.l, tp.p
                            FROM (SELECT SUBSTR (poste_code, 2, 2) l
                                    FROM postes) tl,
                                 (SELECT SUBSTR (poste_code, 5, 2) p
                                    FROM postes) tp) postes,
                defauts
          WHERE 'L' || postes.l || 'P' || postes.p = def_poste(+)
       ORDER BY postes.l, postes.p

Code :

1
2
3
4
5
6
 
(SELECT DISTINCT tl.l, tp.p
                            FROM (SELECT SUBSTR (poste_code, 2, 2) l
                                    FROM postes) tl,
                                 (SELECT SUBSTR (poste_code, 5, 2) p
                                    FROM postes) tp)

Cette sous-requête (produit cartésien entre les L et P) les te donne toutes les combinaisons possibles entre L et P

Exemple si j'ai dans ma table postes les enregistrement suivants :
L01P01
L01P02
L02P01
L02P02
L02P03

Résultat de la sous-requête :

L P
01 01
01 02
01 03
02 01
02 02
02 03

Dans ton rapport
Tu mets une couleur de remplissage grise pour les cellules = -1

Résultat de la requête

Code :

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
 
SQL> SELECT * FROM postes ;
 
POSTE_
------
L01P01
L01P02
L02P01
L02P02
L02P03
 
SQL> SELECT * FROM defauts;
 
DEF_PO   DEF_CODE
------ ----------
L02P03          0
L01P02          0
L02P03          1
L01P02          1
L01P02          0
 
SQL> SELECT DISTINCT postes.l, postes.p,
  2                  DECODE (def_code,
  3                          NULL, -1,
  4                          COUNT (def_code) OVER (PARTITION BY def_poste)
  5                         ) cpt
  6             FROM (SELECT DISTINCT tl.l, tp.p
  7                              FROM (SELECT SUBSTR (poste_code, 2, 2) l
  8                                      FROM postes) tl,
  9                                   (SELECT SUBSTR (poste_code, 5, 2) p
 10                                      FROM postes) tp) postes,
 11                  defauts
 12            WHERE 'L' || postes.l || 'P' || postes.p = def_poste(+)
 13         ORDER BY postes.l, postes.p
 14  ;
 
L      P             CPT
------ ------ ----------
01     01             -1
01     02              3
01     03             -1
02     01             -1
02     02             -1
02     03              2
 
6 ligne(s) sélectionnée(s).
 
SQL>

[salim11]

Salut,
J'ai modifié la requete : remplacer le -1 par NULL

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT DISTINCT postes.l, postes.p,
                DECODE (def_code,
                        NULL,NULL,
                        COUNT (def_code) OVER (PARTITION BY def_poste)
                       ) cpt
           FROM (SELECT DISTINCT tl.l, tp.p
                            FROM (SELECT SUBSTR (poste_code, 2, 2) l
                                    FROM postes) tl,
                                 (SELECT SUBSTR (poste_code, 5, 2) p
                                    FROM postes) tp) postes,
                defauts
          WHERE 'L' || postes.l || 'P' || postes.p = def_poste(+)
       ORDER BY postes.l, postes.p

Tu mets dans le trigger de format du champ cpt, la cellule colorée en rouge dans la figure, le code suivant
voir la figure suivante
Pièce jointe 23488

Code :

1
2
3
4
5
6
7
8
9
 
FUNCTION F_cptFormatTrigger RETURN BOOLEAN IS
begin
  IF :cpt  IS NULL  then 
      srw.set_foreground_fill_color ('GRAY28');
      srw.set_fill_pattern ('solid');
  end IF; 
  RETURN (true);
end;

Le résultat
Pièce jointe 23487

[gg2vig]

merci pour la solution

j'ai oracle report 6i et oracle 8.1.7

oracle accepte la syntaxe mais visiblement pas report

erreur 'no parsing'

[salim11]

Voici une solution pour la version 8i avec un group by à la place de la fonction analytique count() over

Code :

1
2
3
4
5
6
7
8
9
10
11
 
SELECT  POSTES.L, POSTES.P,DEFAUTS.CPT CPT 
FROM (SELECT DISTINCT TL.L, TP.P
      FROM (SELECT SUBSTR (POSTE_CODE, 2, 2) L
            FROM POSTES) TL,
           (SELECT SUBSTR (POSTE_CODE, 5, 2) P
           FROM POSTES) TP) POSTES,
           (SELECT  DEF_POSTE,COUNT (DEF_CODE) CPT FROM DEFAUTS
			GROUP BY DEF_POSTE) DEFAUTS
WHERE 'L' || POSTES.L || 'P' || POSTES.P = DEF_POSTE(+)
ORDER BY POSTES.L, POSTES.P

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
 
SQL> SELECT  POSTES.L, POSTES.P,DEFAUTS.CPT CPT 
  2  FROM (SELECT DISTINCT TL.L, TP.P
  3        FROM (SELECT SUBSTR (POSTE_CODE, 2, 2) L
  4              FROM POSTES) TL,
  5             (SELECT SUBSTR (POSTE_CODE, 5, 2) P
  6             FROM POSTES) TP) POSTES,
  7             (SELECT  DEF_POSTE,COUNT (DEF_CODE) CPT FROM DEFAUTS
  8     GROUP BY DEF_POSTE) DEFAUTS
  9  WHERE 'L' || POSTES.L || 'P' || POSTES.P = DEF_POSTE(+)
 10  ORDER BY POSTES.L, POSTES.P;
 
L  P         CPT
-- -- ----------
01 01
01 02          3
01 03
02 01
02 02
02 03          2
 
6 rows selected.
 
SQL>

[gg2vig]

merci