aide pour une requête complexe [Résolu]

[marsupio49]

Bonjour à tous,
Je suis actuellement en stage informatique et on me demande de faire un certains nombres de requête afin d'afficher les résultats de transfert de fichier. Le problème est que pour un affichage, j'ai besoin de trois requètes et j'aimerais n'en faire que une, si bien sûr cela est possible. Je travail sur une base Oracle. Dont voici la table. (tout les nom son fictif, mais le principe reste le même)

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
 
-- 
-- Serveur: localhost
-- Généré le : Dimanche 10 Juin 2007 à 12:48
-- Version du serveur: 4.1.9
-- Version de PHP: 4.3.10
-- 
-- Base de données: `mabase`
-- 
 
-- --------------------------------------------------------
 
-- 
-- Structure de la table `matable`
-- 
 
CREATE TABLE `matable` (
  `idtransfert` int(11) NOT NULL AUTO_INCREMENT,
  `date` date NOT NULL DEFAULT '0000-00-00',
  `statut` int(1) NOT NULL DEFAULT '0',
  `nomfichier` varchar(60) NOT NULL DEFAULT '',
  `typefichier` varchar(20) NOT NULL DEFAULT '',
  PRIMARY KEY  (`idtransfert`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=70 ;
 
-- 
-- Contenu de la table `matable`
-- 
 
INSERT INTO `matable` VALUES (15, '2007-11-05', 0, 'rapport_compta', '.txt');
INSERT INTO `matable` VALUES (5, '2007-06-01', 1, 'dossier_entr.', '.txt');
INSERT INTO `matable` VALUES (2, '2007-02-03', 1, 'mémoire', '.doc');
INSERT INTO `matable` VALUES (3, '2007-03-10', 1, 'logo1', '.gif');
INSERT INTO `matable` VALUES (6, '2007-06-01', 1, 'administration', '.doc');
INSERT INTO `matable` VALUES (7, '2007-06-02', 0, 'graphique', '.gif');
INSERT INTO `matable` VALUES (8, '2007-06-06', 0, 'rapport_stage', '.doc');
INSERT INTO `matable` VALUES (13, '2007-08-15', 0, 'reglement', '.txt');
INSERT INTO `matable` VALUES (12, '2007-06-24', 1, 'bannière', '.gif');
INSERT INTO `matable` VALUES (11, '2007-06-15', 1, 'note1', '.txt');
INSERT INTO `matable` VALUES (14, '2007-10-30', 0, 'note2', '.txt');
INSERT INTO `matable` VALUES (10, '2007-06-14', 1, 'rapport', '.doc');
INSERT INTO `matable` VALUES (9, '2007-06-07', 0, 'resultat_concour', '.txt');
INSERT INTO `matable` VALUES (4, '2007-05-21', 0, 'feuille_salaire', '.txt');
INSERT INTO `matable` VALUES (1, '2007-01-11', 0, 'facture', '.pdf');

Résultat d'un :

Code :

SELECT * FROM matable

Pour le statut, zéro signifie que le transfert est réussi (OK), le 1 signifie qu'il a échoué (KO).

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
 
idtransfert	date		statut	nomfichier		typefichier
15 		2007-11-05 	0 	rapport_compta 		.txt
5 		2007-06-01 	1 	dossier_entr. 		.txt
2 		2007-02-03 	1 	mémoire 		.doc
3 		2007-03-10 	1 	logo1 			.gif
6 		2007-06-01 	1 	administration 		.doc
7 		2007-06-02 	0 	graphique 		.gif
8 		2007-06-06 	0 	rapport_stage 		.doc
13 		2007-08-15 	0 	reglement 		.txt
12 		2007-06-24 	1 	bannière 		.gif
11 		2007-06-15 	1 	note1 			.txt
14 		2007-10-30 	0 	note2 			.txt
10 		2007-06-14 	1 	rapport 		.doc
9 		2007-06-07 	0 	resultat_concour 	.txt
4 		2007-05-21 	0 	feuille_salaire 	.txt
1 		2007-01-11 	0 	facture 		.pdf

Ce que je voudrais, c'est avoir un tableau ou nous verrions le dernier fichier de chaque typefichier, le nombre de fichier tranféré et le nombre de fichier KO.
exemple:

Code :

1
2
3
4
5
6
7
8
9
10
11
12
 
-------------------------------------------------------------------------|
   date   |        nomfichier    | typefichier  | Nb_fichier |  nb_erreur|
-------------------------------------------------------------------------|
2007-11-05|    rapport_compta    |   .txt       |     7      |     2     |
-------------------------------------------------------------------------|
2007-06-14|    rapport           |   .doc       |     4      |     3     |
-------------------------------------------------------------------------|
2007-06-24|    bannière          |   .gif       |     3      |     2     |
-------------------------------------------------------------------------|
2007-01-11|    facture           |   .pdf       |     1      |     0     |
-------------------------------------------------------------------------|

Pour l'instant je n'ai réussi qu'en faisant 3 tableaux différents, en faisant trois requêtes :
Une pour les derniers fichier, une autre pour le nommbre de fichier par typefichier et une troisième pour le nombre de fichier KO.

J'aimerais savoir si il ne serait pas possible de pouvoir réduire le nombre de requête. Je ne sais pas du tout si cela est possible. Cela me parait compliquer


Voila ce que j'ai fait:

-requête pour le dernier fichier par type de fichier:

Code :

1
2
3
4
5
6
7
8
 
SELECT 	idtransfert, Statut, date, nomfichier, typefichier
FROM 	MaTable
WHERE 	idtransfert IN 
 
	(SELECT MAX(idtransfert)
	FROM MaTable
	GROUP BY (typefichier))

Résultat:

Code :

1
2
3
4
5
6
7
8
9
10
11
12
 
-------------------------------------------------
   date   |        nomfichier    | typefichier  |
-------------------------------------------------
2007-11-05|    rapport_compta    |   .txt       |
-------------------------------------------------
2007-06-14|    rapport           |   .doc       |
-------------------------------------------------
2007-06-24|    bannière          |   .gif       |
-------------------------------------------------
2007-01-11|    facture           |   .pdf       |
-------------------------------------------------

-requête pour afficher la deuxième partie, le nombre de fichier par type de fichier:

Code :

1
2
3
4
 
SELECT COUNT(*) AS Nb_fichier, typefichier
FROM MaTable
GROUP BY (typefichier)

résultat :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
 
---------------------------|
 typefichier  | Nb_fichier |
---------------------------|
   .txt       |     7      |    
---------------------------|
   .doc       |     4      |    
---------------------------|
   .gif       |     3      |     
---------------------------|
   .pdf       |     1      |     
---------------------------|

-requête pour afficher la troisième partie, le nombre de fichier KO par type de fichier:

Code :

1
2
3
4
5
 
SELECT COUNT(*) AS Nb_fichier, typefichier
FROM MaTable
WHERE statut=1
GROUP BY (typefichier)

résultat :

Code :

1
2
3
4
5
6
7
8
9
10
 
---------------------------|
 typefichier  | nb_erreur  |
---------------------------|
   .txt       |     2      |    
---------------------------|
   .doc       |     3      |    
---------------------------|
   .gif       |     2      |     
---------------------------|

!! De plus ici le type .pdf qui n'a acun fichier KO n'apparait pas, alors que je me doit de le faire apparaitre. :s

Donc voila je voulais savoir si vous pouviez m'aider à réduire le nombre de requête.En esperant vous avoir donné les informations qu'il fallait tout en étant clair.

Je vous remercie d'avance.

Cordialement,

[ylarvor]

Code :

1
2
3
4
5
6
7
8
 
CREATE VIEW VueIntermediaire1 AS
SELECT idtransfert, Statut, date, nomfichier, typefichier FROM MaTableWHERE idtransfert IN (SELECT MAX(idtransfert) FROM MaTable GROUP BY (typefichier))
CREATE VIEW VueIntermediaire2 AS
SELECT COUNT(*) AS Nb_fichier, typefichierFROM MaTableGROUP BY (typefichier)
CREATE VIEW VueIntermediaire3 AS
SELECT COUNT(*) AS Nb_Erreur, typefichierFROM MaTableWHERE statut=1GROUP BY (typefichier)
CREATE VIEW VueFinale AS SELECT 1.idtransfert, 1.Statut, 1.date, 1.nomfichier, 1.typefichier, 2.Nb_fichier, 3.Nb_Erreur FROM VueIntermediaire1 AS 1 RIGHT OUTER JOIN VueIntermediaire2 AS 2 ON 1.typeFichier = 2.typeFichier RIGHT OUTER JOIN VueIntermediaire3 AS 3 ON 2.typeFichier = 3.typeFichier

Pour ton problème de NULL, il suffit de faire un RIGHT OUTER JOIN au lieu d'un JOIN.

[remi4444]

Remarques
- Attention tu es en syntaxe MySql, en oracle on n'encadre pas les noms de champs par des cotes mais par des guillemets, le mieux est de ne pas les encadrer du tout et oralce mettra tout en majuscule, ce qui est l'usage.

- Evite d'utiliser le nom "date" pour un champ qui est un mot reservé (pour le type "date"). Ce n'est pas interdit mais ça entraine parfois des comportements défaillant de pas mal d'outils (Toad notamment...)

Pour la requête, je verais bien un truc comme ça (en renomant le champ "date"):

Code :

1
2
3
4
5
6
7
8
SELECT 
  max(DATE_FIC) date_dernier_fichier,
  max(NOMFICHIER) KEEP (DENSE_RANK LAST ORDER BY DATE_FIC) nom_dernier_fichier,
  TYPEFICHIER,
  sum(decode(STATUT,0,1,0)) NB_OK,
  sum(decode(STATUT,1,1,0)) NB_KO
FROM MATABLE
GROUP BY TYPEFICHIER

[ylarvor]

je ne comprend pas la ligne suivante notamment KEEP:

max(NOMFICHIER) KEEP (DENSE_RANK LAST ORDER BY DATE_FIC) nom_dernier_fichier

peux tu me donner des explications ?

[marsupio49]

Bonjour et merci de vos réponse,
remi4444, j'ai testé ta requete, en réalisant les miodifications, que tu as suggéré et cela marche nickel. Cependant tout comme ylarvor je ne comprend pas la ligne :

Code :

max(NOMFICHIER) KEEP (DENSE_RANK LAST ORDER BY DATE_FIC) nom_dernier_fichier

Même si cela marche j'aimerais vrailment comprendre et apprendre ce que cela signifie, ce qui me permettrait d'avancer dans ce vaste domaine.

Je ne suis également pas bien sûr de comprendre la ligne suivante :

Code :

sum(decode(STATUT,0,1,0)) NB_OK,

Merci beaucoup de l'aide apportée.

[McM]

Envoyé par ylarvor

je ne comprend pas la ligne suivante notamment KEEP:

max(NOMFICHIER) KEEP (DENSE_RANK LAST ORDER BY DATE_FIC) nom_dernier_fichier

peux tu me donner des explications ?

Regarde le tuto de lalystar sur les fonctions analytiques

[marsupio49]

Re bonjour, enfait j'ai un petit souci avec la requête, si j'essaye d'afficher l'emetteur et le destinataire (concernant le dernier fichier), la requête ne marche plus. :s

Est-ce normal?

Cordialement,

[marsupio49]

Re bonjour, enfait j'ai un petit souci avec la requête, si j'essaye d'afficher l'emetteur et le destinataire (concernant le dernier fichier), la requête ne marche plus. :s

Est-ce normal?

De plus pour afficher le dernier fichier tu utilise le champs DATE_FIC. Le problème est qu'il y a également un champs date_heure (que je n'ai pas fait apparaître,car nous avions choisi d'utiliser le champs idtransfert qui est en auto-incrémentation, c'est ainsi plus simple).

Cordialement,

[remi4444]

Envoyé par marsupio49

Re bonjour, enfait j'ai un petit souci avec la requête, si j'essaye d'afficher l'emetteur et le destinataire (concernant le dernier fichier), la requête ne marche plus. :s

Est-ce normal?

De plus pour afficher le dernier fichier tu utilise le champs DATE_FIC. Le problème est qu'il y a également un champs date_heure (que je n'ai pas fait apparaître,car nous avions choisi d'utiliser le champs idtransfert qui est en auto-incrémentation, c'est ainsi plus simple).

Cordialement,

Excuse moi, en l'absence de boule de cristal, j'ai du mal à deviner les champs que tu n'indiques pas ainsi que les besoins que tu n'exprimes pas...

"MAX (MACHIN) KEEP (DENSE_RANK LAST ORDER BY TRUC)" veux dire que tu affiche le champ MACHIN correspondant à la dernière valeur du champ TRUC classée dans l'ordre, c'est à dire lorsque TRUC a la valeur max. A partir de là, à toi de composer ce que tu veux.

[marsupio49]

Envoyé par remi4444

Excuse moi, en l'absence de boule de cristal, j'ai du mal à deviner les champs que tu n'indiques pas ainsi que les besoins que tu n'exprimes pas...

Je sais bien et je m'en escuse, je voulais juste simplifier les choses, pour que vous compreniez ce que je demande, car pour moi une tel requête était impossible. Je te remercie donc énormément pour ta précieuse aide.

J'ai réglé le problème du transfertid, et j'ai compris la ligne max(NOMFICHIER) KEEP.......

Mais j'ai toujours le problème, que quand je veut afficher un nouveau champs (par exemple destinataire), la requête en fonctionne plus. Désolé de t'embeter encore une foi :s

Code :

1
2
3
4
5
6
7
8
9
SELECT 
  max(DATE8FIC) date_dernier_fichier,
  max(NOMFICHIER) KEEP (DENSE_RANK LAST ORDER BY EVENTID) nom_dernier_fichier,
  TYPEFICHIER,
  count(STATUT) NB_FLUX,
  sum(decode(STATUT,1,1,0)) NB_KO,
  DESTINATAIRE
FROM MATABLE
GROUP BY TYPEFICHIER

[remi4444]

Normalement, quand une requête "ne fonctionne pas", oracle te renvoi une information pour te préciser ce qui ne fonctionne pas, il faut etre attentif à chacune de ces informations...

Ici je suppose qu'oracle t'indiques que "DESTINATAIRE" n'est pas dans le groupement... Oracle est strict sur ce point, soit tu affiche un champ que tu dois mettre dans le critère de groupement (dans le group-by) soit tu doit utiliser une fonction de groupement (MAX, MIN, SUM, COUNT etc....)

Si tu veux, comme pour le nom, avoir le destinataire correspondant à ton dernier fichier, alors fait comme pour le nom....

Code :

1
2
3
4
 
[...]
max(DESTINATAIRE) KEEP (DENSE_RANK LAST ORDER BY EVENTID) destinataire_dernier_fichier
[...]

[marsupio49]

Envoyé par remi4444

Normalement, quand une requête "ne fonctionne pas", oracle te renvoi une information pour te préciser ce qui ne fonctionne pas, il faut etre attentif à chacune de ces informations...

En temps normal c'est exact, mais la je n'ai pas d'accès a SQLPLUS a mon stage je dois faire directement via le logiciel de l'entreprose et celui ne retourne aucune erreur (il affiche les données dans le tableau quand la requête fonctionne; sinon le tableau reste vide. Cela ne rend pas les chose évidente. Mais je ne peut avoir accès à SQLPLUS).

Merci beaucoup de ton aide, maintenant tout fonctionne nickel.
MErci encore et bonne jorunée,
Cordialement,

[remi4444]

Envoyé par marsupio49

En temps normal c'est exact, mais la je n'ai pas d'accès a SQLPLUS a mon stage je dois faire directement via le logiciel de l'entreprose et celui ne retourne aucune erreur

Ben dit donc, tu n'as pas du t'amuser tous les jour!

Ce logiciel te donne donc l'exemple à ne pas suivre, comme ça, à l'avenir tu aura compris l'importance de toujours prévoir une remontée d'erreur impécable dans toute programmation...

[marsupio49]

Envoyé par remi4444

Ben dit donc, tu n'as pas du t'amuser tous les jour!

Et encore là, c'est rien. Pour tester une requête faut que je la contrôle (si elle marche pas il contrôle quand même) et apres je met la requete en developpement et seulement après ça je peux visualiser le résultat. Donc quand sa marche pas bah faut avoir du temps quoi


Je te remercie encore