une requete qui me rend folle "sqlplus"

aidez moi pleaaaase!!!

3 mai 2011 à 14:04:29

j'ai réussi à faire marcher cette requete dans le cadre de mon stage ( elle donne un indicateur temporel pour une date ainsi qu'une voie donnée, Mon problème est le suivant: je veux qu'elle me donne le résultat(lheure d'appel +heure de passage + temps attente + moyenne) sur toutes les voies d'une gare et qu'elle s'execute tous les jours pour le jour d'avant) je suis sous ORACLE 8i je vous vontre ma requete sql avec son résultat qui parait compliqué a premiere vue:

SELECT * FROM
  2  			  (SELECT DISTINCT (to_char(DATE_EVENEMENT , 'HH24:MI:SS')) AS "APPEL",
  3  			  to_char(DATE_IMAGE, 'HH24:MI:SS') AS "PASSAGE",
  4  			  DATE_IMAGE,
  5  			  DATE_EVENEMENT,
  6  			  E.VOIE,
  7  			  E.ID_GARE,
  8  			  NUMERO_TRANSACTION,
  9  			  ((DATE_IMAGE - DATE_EVENEMENT)*24*60*60) AS "ATTENTE",
 10  			  (SELECT ROUND(AVG(MAXIMUM),2) FROM (SELECT MAX((DATE_IMAGE - DATE_EVENEMENT)*24*60*60)AS "MAXIMUM" FROM EVENEMENT E , IMG_VIDEO_IMAGE I WHERE I.DATE_IMAGE =	(SELECT DATE_IMAGE FROM IMG_VIDEO_IMAGE WHERE DATE_IMAGE > DATE_EVENEMENT   AND ID_GARE='25006012' AND VOIE='004' AND ID_EVENEMENT = '027' AND ROWNUM < 2)
 11  			  AND DATE_EVENEMENT >= '12/04/11' AND DATE_EVENEMENT < '29/04/11' AND E.ID_GARE='25006012' AND E.VOIE='004' AND I.DATE_IMAGE < '29-04-11' AND I.ID_GARE='25006012' AND I.VOIE='004' AND ROWNUM < 100 GROuP BY NUMERO_TRANSACTION)) AS "MOYENNE"
 12  			  FROM EVENEMENT E , IMG_VIDEO_IMAGE I
 13  			  WHERE I.DATE_IMAGE =	(SELECT DATE_IMAGE FROM IMG_VIDEO_IMAGE WHERE DATE_IMAGE > DATE_EVENEMENT   AND ID_GARE='25006012' AND VOIE='004' AND ID_EVENEMENT = '027' AND ROWNUM < 2)
 14  			  AND DATE_EVENEMENT >= '28/04/11'
 15  			  AND DATE_EVENEMENT < '29/04/11'
 16  			  AND E.ID_GARE = '25006012'
 17  			  AND E.VOIE='004'
 18  			  AND I.DATE_IMAGE < '29-04-11'
 19  			  AND I.ID_GARE='25006012'
 20  			  AND I.VOIE='004'
 21  			  ORDER BY DATE_EVENEMENT ASC, I.DATE_IMAGE ASC)
 22  			  WHERE DATE_EVENEMENT IN (SELECT MIN(DATE_EVENEMENT) AS FROM EVENEMENT E , IMG_VIDEO_IMAGE I WHERE I.DATE_IMAGE =  (SELECT DATE_IMAGE FROM IMG_VIDEO_IMAGE WHERE DATE_IMAGE > DATE_EVENEMENT	AND ID_GARE='25006012' AND VOIE='004' AND ID_EVENEMENT = '027' AND ROWNUM < 2) AND DATE_EVENEMENT >= '28/04/11' AND DATE_EVENEMENT < '29/04/11' AND E.ID_GARE='25006012' AND E.VOIE='004' AND I.DATE_IMAGE < '29-04-11' AND I.ID_GARE='25006012' AND I.VOIE='004' AND ROWNUM < 100 GROUP BY NUMERO_TRANSACTION);

j'aimerai avoir une variable que je pourai parametrer au moment de lexecution de mon fichier :(:(:( Cette requete me permet de Lister toutes les heures d' appels effectué le 28/04/11 sur la voie 004 sur la gare 12 et me donner les heure de passage de ces client qui lui correspondent eespectivement et me calculer ensuite le temps d'attente ainsi que la moyenne !!

06:09:30 06:35:21 28/04/11 28/04/11 004 25006012 145 1551 98,82
07:25:23 07:25:51 28/04/11 28/04/11 004 25006012 210 28 98,82
08:44:11 08:44:50 28/04/11 28/04/11 004 25006012 349 39 98,82
09:13:56 09:14:31 28/04/11 28/04/11 004 25006012 400 35 98,82
09:26:59 09:28:46 28/04/11 28/04/11 004 25006012 428 107 98,82
09:34:14 09:34:23 28/04/11 28/04/11 004 25006012 443 9 98,82
09:43:52 09:44:05 28/04/11 28/04/11 004 25006012 474 13 98,82
09:44:54 09:45:17 28/04/11 28/04/11 004 25006012 478 23 98,82
10:28:44 10:30:55 28/04/11 28/04/11 004 25006012 575 131 98,82
10:55:46 10:56:14 28/04/11 28/04/11 004 25006012 629 28 98,82
11:32:56 11:34:31 28/04/11 28/04/11 004 25006012 691 95 98,82
12:40:42 12:41:00 28/04/11 28/04/11 004 25006012 813 18 98,82
13:07:53 13:09:17 28/04/11 28/04/11 004 25006012 878 84 98,82
13:57:46 14:01:17 28/04/11 28/04/11 004 25006012 925 211 98,82
14:02:53 14:04:07 28/04/11 28/04/11 004 25006012 928 74 98,82
14:20:07 14:21:02 28/04/11 28/04/11 004 25006012 937 55 98,82
14:22:07 14:23:29 28/04/11 28/04/11 004 25006012 940 82 98,82
15:08:30 15:08:49 28/04/11 28/04/11 004 25006012 970 19 98,82
15:38:04 15:38:23 28/04/11 28/04/11 004 25006012 1007 19 98,82
18:25:18 18:25:38 28/04/11 28/04/11 004 25006012 1294 20 98,82
19:01:20 19:01:43 28/04/11 28/04/11 004 25006012 1342 23 98,82
21:00:54 21:01:06 28/04/11 28/04/11 004 25006012 1471 12 98,82
21:01:57 21:02:09 28/04/11 28/04/11 004 25006012 1472 12 98,82
21:02:12 21:02:48 28/04/11 28/04/11 004 25006012 1473 36 98,82
21:21:33 21:22:10 28/04/11 28/04/11 004 25006012 1490 37 98,82
Quelqu'un peut m'éclairer SVPPP !!!!

Tracker

3 mai 2011 à 18:03:15

Si tu veux de l'aide décris mieux ton modèle et file le contenu des tables clés (pour test).

Tracker.

pythonneries

3 mai 2011 à 23:34:33

Si tu es sous SQL*Plus tu peux utiliser des variables de substitution. Le plus pratique, c'est les variables positionnelles - par exemple:

SQL> @mon_script a b

Si tu as mis &1 et &2 dans ton script, SQL*Plus leur substituera respectivement a et b à l'exécution. Je pense que c'est ce que tu veux. Il y a d'autres moyens, mais on pourrait y passer des heures.

A part ça il y a des trucs que je n'aime pas dans ton script:
1) Ne JAMAIS s'appuyer sur un format de date par défaut. Le DBA peut changer cela quand il veut. Toujours mettre (par exemple) to_date('03/05/2011', 'DD/MM/YYYY') avec un format explicite.
2) Le DISTINCT tout en haut, yek. Cela sent les jointures mal fichues et le "ooops ... j'ai des doublons".

Pour ce genre de requête, je te recommande chaudement ce qu'Oracle appelle fonctions analytiques. Même si tu travailles sur une version quasi médiévale d'oracle, elles existent depuis la 8.1.6.

simona

4 mai 2011 à 11:07:35

Citation : Tracker

Si tu veux de l'aide décris mieux ton modèle et file le contenu des tables clés (pour test).

Tracker.

oups daccor, j'ai deux tables EVENEMENT(DATE_EVENEMENT de type DATETIME, ID_EVENEMENT, ID_GARE, VOIE) et IMG_VIDEO_IMAGE(DATE_IMAGE, ID_GARE, VOIE, NUMERO_TRANSACTION)
ce sont les champs qui m'importent dans les deux tables. je dois vous dire qu'elles manquent de clé primaires qui permettent de faire les jointures c'est pour cette raison qu'elle est compliquée.
Elle a été fourni a l'entreprise comme ca et il ya aucun moyen de changer cela!!
l'idée est des calculer le temps d'attente ( heure de l'evenement - heure du passage)pour toute les gares et voies qui exsiste !!!

Merciiii :):) Ce forum est vraiment génial

Pour pythonneries: Merci pr les variables de substitutions je les essairai des que possibles,
donc pour chaque date utilisé dans mon script je devrai expliciter le format du genre ('DD/MM/YYYU'), okey
et pour le DISTINCT je suis obligé de lutiliser a cet endroi la car il arrive qu'un client appui plusieur fois sur linterphone a la meme seconde ou minute ,"mon evenement est quand un client appui sur un interphone" donc je doi pouvoir calculer le temps dattente seulement a partir du premiere appui et donc du premiere appel!!
Merci beaucoup en tout ca pour votre aide,

guk92

5 mai 2011 à 13:30:00

Purée, elle est affreuse cette requête...

Je ne savais pas qu'on pouvait mettre des guillemets pour les alias venant après le AS.

Sinon sans dire trop de bêtise (car je suis toujours débutant), je ne sais pas s'il est bien de mettre :

DATE_EVENEMENT < '29/04/11'
-- au lieu de 
DATE_EVENEMENT <= '29/04/11' -- ajout du =

-- Ou vaut-il mieux utiliser :
AND DATE_EVENEMENT BETWEEN '28/04/11' AND '29/04/11'

Voilou voilou...

simona

5 mai 2011 à 13:51:08

Oui effectivement je te laccorde elle est affreuse, mais en fait pr les dates et la voie je voudrai les modifier en mettant E.VOIE=I.VOIE et pr la date lui dire de prendre tout le mois d'avant à partir de la date d'aujourdhui quelque chose du genre DATE_EVENEMENT BETWEEN sysdate - 1 AND sysdate - 30 par ex mais je ne suis pas sur que ca marche!

guk92

5 mai 2011 à 14:12:20

J'utilise ...

ChampDate BETWEEN date1 AND date2

... que pour les champs date en faite, en effet l'utiliser pour des entiers (comme entre 1 et 30)... je doute que cela fonctionne, mais pour les dates il ne doit pas y avoir de problème.

En faite, si j'ai fais cette remarque c'était surtout pour dire que j'avais lu qu'il fallait mettre le signe égal pour les dates pour éviter les erreurs :

AND DATE_EVENEMENT >= '28/04/11'
AND DATE_EVENEMENT < '29/04/11' -- Problème


-- Avec les =
AND DATE_EVENEMENT >= '28/04/11'
AND DATE_EVENEMENT <= '29/04/11'

-- Equivalent :
AND DATE_EVENEMENT BETWEEN  '28/04/11' AND '29/04/11'

Vois tu, si celui avec les signes égaux sont équivalent avec le BETWEEN, c'est bien qu'il y aura un problème si on ne place pas les signes =
Concrètement je pense que cette erreur survient surtout si on utilise les heure:minute:seconde (avec 00:00:00), donc je pense que tu n'as pas à t'inquiéter.

EDIT :
J'ai dis une bêtise aussi grosse que ma tête :
Il est possible d'utiliser BETWEEN avec des dates tout comme des entiers,
exemple :

champNombre BETWEEN 1 AND 30
champDate   BETWEEN '28/04/11' AND '29/04/11'

simona

5 mai 2011 à 14:28:52

Oui oui j'ai rectifié cette histoire d"=" et de BETWEEN mais a propos de la date, j'aimerai pouvoir executer mon script de facon automatique tous les premiers du mois pour le mois davant c'est pour cela que je cherche un moyen ...
Et vu que j'ai plusieur voie j'aimerai ne pas avoir a lui dire a chake fois kelle voie je veux utiliser mais qu'il me fasse toutes les voie sans ke je lui dise!!
quand j'ecri par ex dans mes conditions E.VOIE=I.VOIE mon script tourne à l'infini et ne veut plus s'arreter :(:(:( mon patron veut quelque chose d'automatique :(:( qui s'execute pour tte les voies automatiquement,
J'ai pensé aussi aux variables mais bon comme je suis tres tres Débutante

I'm scaared

guk92

5 mai 2011 à 14:39:02

As tu pensé à créer une table de 5 champs dans lequel tu indiques :
ID_voie (en auto incrément) / DateAjout / DateDebutProgram / DateFinProgram / NumeroVoie

Cela te permettrais de connaitre automatiquement le numéro des voies programmé.
ID_voie et la date d'ajout sont un plus qui pourraient te servir de renseignement.

PS: "Débutante" avec une requête de cette longueur :euh:

simona

5 mai 2011 à 21:38:31

bun disons que j'ai passé une semaine a faire mes test et puis sans internet je serai rien du tout

Pour la table je n'ai pas le droit de créer quoique se soit, les tables de données dans oracle sont fournis par un fournisseur donc aucun droit de modification ou d'ajout :s et ils ont fait la belle erreur de ne pa introduir de champ index dans les tables ni de clé primaire cest pour cette raison que jai une requete comme ca, que maintenan j'ai amélioré grace a vos suggestion mais qui ne fait toujours pas ce que je souhaite

Merci pour ton interet en tout cas

Lord Casque Noir

5 mai 2011 à 23:39:26

Citation : simona

Oui oui j'ai rectifié cette histoire d"=" et de BETWEEN mais a propos de la date, j'aimerai pouvoir executer mon script de facon automatique tous les premiers du mois pour le mois davant c'est pour cela que je cherche un moyen .

Bon je connais pas du tout Oracle mais en gros si on est le '2011-05-06' (en format ISO) si tu veux traiter tous les machins du mois dernier c'est entre '2011-04-01' (inlcusif) et '2011-05-01' (exclusif).

Donc du fais un truc du genre

date_evenement >= ((NOW() - 1 mois) formaté en '01/MM/YYYY') ce qui te donne le 1er jour du mois précédent
AND
date_evenement < (NOW() formaté en '01/MM/YYYY') ce qui te donne le 1er jour du mois courant

Les oracleux vont bien donner la bonne fonction XD

simona

6 mai 2011 à 10:32:56

Je te remercie Deja pour ce tres bon debut