Requêtes imbriquées complexes [Résolu]

[ZoomBox]

Bonjour, je viens de m'inscrire car j'ai une très grosse requête à faire pour un stage et j'ai du mal à m'en sortir.
Je travail sur Access avec deux tables:

TableA
-TRACKING_ID (clef primaire normalement inutile ici)
-CORRELATION_ID (int)
-STEP (text)
-DATE (date/heure)

TableB
-CORRELATION_ID (int)
-STEP (text)
-DATE (date/heure)


Je traite ici des flux, un flux est composé de 3 STEPs: 'Entrant', 'Traitement', 'Sortant' (dans cet ordre), un flux est démarqué par un correlation_id (la table est donc techniquement composé de "paquet" de 3 fois le même correlation_id).
Un flux est "en erreur" si le STEP 'Sortant' s'est mal passé (c'est une colonne de la table "Status", mais il est inutile de la mentionner)

La TableA comprend absolument TOUT, toutes les données dont j'ai besoin.
La TableB comprend uniquement les données des STEPs 'Sortant' des données "en erreur".

Je vous montre vite-fait:

TableA
TRACKING_ID|CORRELATION_ID|STEP|DATE
------------------------------------------
0001............|A....................|Entr..|10/10/10
0002............|A....................|Trai..|11/10/10
0003............|A....................|Sort..|12/10/10
0004............|B....................|Entr..|13/10/10
0005............|B....................|Trai..|14/10/10
0006............|B....................|Sort..|15/10/10
0007............|B....................|Entr..|16/10/10
0008............|B....................|Trai..|17/10/10
0009............|B....................|Sort..|18/10/10
etc...

TableB (qui est déjà le résultat d'un requette sur la TableA)
CORRELATION_ID|STEP|DATE
--------------------------------
B....................|Sort..|18/10/10
F....................|Sort..|22/04/10
P....................|Sort..|29/06/11

Vous l'avez constaté, un flux (ici B) peut être relancé donc réintégré dans la TableA, à une autre date.


Et la requette que je souhaite executer:
Selectionner les STEP 'Trai' correspondant aux STEP 'Sort' de la TableB.
(Simple sur le papier, mais j'ai l'impression, très complexe en SQL).

Ce que resort la requette sera donc composé de 3 lignes, les STEP 'Trai' sortis seront ceux qui correspondent directement aux STEP 'Sort' de la TableB(càd: les STEP 'Trai' dont la date est la plus grande des dates inférieures au STEP 'Sort' correspondant).

Dans mon exemple de tables simplifié, une des lignes retournées sera

TRACKING_ID|CORRELATION_ID|STEP|DATE
------------------------------------------
0007............|B....................|Trai..|16/10/10
....
....

C'est extrémement complexe à comprendre et aussi à expliquer (et encore, J'ai très, très largement simplifié). S'il vous manque des élements logiques vous pouvez le dire, j'ai peut être oublié certaines choses en simplifiant.

[dehorter olivier]

Bonjour

1ere chose :

TableA
-TRACKING_ID (clef primaire normalement inutile ici)
-CORRELATION_ID (int)
-STEP (text)
-DATE (date/heure)

Hors dans le tableau de données, ce sont des lettres ?????

J'ai un peu de mal à comprendre les tenants et aboutissants, mais
la requête pourrait elle être quelque chose comme cela ?

Code :

1
2
3
4
5
6
7
8
SELECT TableA.TRACKING_ID
         TableA.CORRELATION_ID,
         TableA.STEP,
         TableA.DATE
FROM TableA
          INNER JOIN TableB ON (TableB.CORRELATION_ID = TableA.CORRELATION_ID)
WHERE TableA.Step = 'Trait' 
   AND TableB.Step = 'Sort'  -- Mais dans la table B, il n'y a que des Sort ???

Mais c'est juste pour lancer le débat, parce que confus pour moi

[ZoomBox]

Bonjour et merci de prendre le temps de me répondre,

En effet, petite incohérence dans mon type de donné, comme je l'ai dit j'ai largement simplifié et j'ai mis un type de données au pif, c'est juste parce que j'ai lu dans les règles qu'il fallait préciser un type de colonne

Oui, dans la TableB il n'y a que des 'Sort' car c'est le résultat d'une requette qui m'est étrangère et qui contient toutes les erreurs.

En fait, je vais rééxpliquer après ce bon repas:

Ici, dans ma TableA, on parle de Flux, un flux est composé de 3 STEPs, d'abords Entr, puis Trait et enfin Sort (entrée -> traitement -> sortie).
La TableB est le résultat d'un requette qui sort le CORRELATION_ID et DATE (et autre) dela partie 'Sort' des flux ayant subis une erreur. Pour relancer ce flux, on ne peut pas repartir de ce flux 'Sort' (une histoire de trigger), il faut relancer le flux à partir du STEP 'Trait'.

Le truc c'est que mes flux peuvent déjà avoir été lancé plusieurs fois, il me faut donc les info du flux 'Trait' le plus proche du flux 'Sort' qu'on me donne (le plus proche ET avant car le 'Trait' se passe avant le 'Sort').

Ta requette est assez loin du compte mais comme tu dis, il faut bien commencer quelque part.

Dans TableB je n'ai que des 'Sort' et je ne peux rien y faire; et je ne veux que des 'Trait' en selection dans ma requette.

Je pense que ça peut aider, mais j'ai déjà trouvé (je pense) la requette qui ferait ce que je veux mais pour UNE SEULE des ligne de ma TableB (imaginons qu'il n'y est qu'une erreur) La ligne est la ligne 0006:

Code :

1
2
3
4
5
6
7
8
SELECT STEP, DATE
FROM TableA 
WHERE STEP = 'Trait' AND  CORRELATION_ID = 'B' 
  AND DATE = (SELECT Max(DATE) 
          FROM TableA 
         WHERE CORRELATION_ID = 'B' 
            AND STEP = 'Trait' 
            AND DATE < #15/10/10#)

[dehorter olivier]

re

La TableB est le résultat d'un requette qui sort le CORRELATION_ID et DATE (et autre) dela partie 'Sort' des flux ayant subis une erreur.

Pourquoi ne pas utiliser la requête, plutôt que de générer une table somme toute temporaire ?? Mais c'est là une autre question

Petite question à propos de CORRELATION_ID. Pourquoi dans ton exemple de requête, dans la principale il s'agit de B et dans la sous requete c'est A ? une petite erreur de frappe ?

Et il n'apparait plus la table B ? serait-ce cette sous requête qui génère la tableB ?

[ZoomBox]

Je corrige, je crois que je me suis complétement planté. Je retire la requête, je vais la refaire si j'y arrive.

Edit: En fait il y avait juste une erreur au niveau du 'A' qui n'a rien à faire là mais c'est bien ça.

La TableB intervient bien, c'est elle qui fourni le CORRELATION_ID ainsi que la DATE que j'utilise dans la requête.
Il faudrait que je puisse executer cette requette pour d'autre ligne de la TableB, un peu comme si j'avais:

Code :

1
2
3
4
5
6
7
8
9
10
11
 
QUERY = <<
INSERT INTO TableC
SELECT STEP, DATE
FROM TableA 
WHERE STEP = 'Trait' AND  CORRELATION_ID = vVarC_ID 
  AND DATE = (SELECT Max(DATE) 
          FROM TableA 
         WHERE CORRELATION_ID = vVarC_ID
            AND STEP = 'Trait' 
            AND DATE < vVarDate) >>

•Quand je pointe sur première ligne de ma TableB:
vVarC_ID = 'B'
vVarDate = '18/10/10'
docmd.runsql (Query) '-> Lance la requête sur Access
•Puis sur la deuxième ligne:
vVarC_ID = 'F'
vVarDate = '22/04/10'
docmd.runsql (Query)
•Puis la troisième ... etc...:


Donc j'aimerais avoir ça mais sur une seule requête et sans code si possible et je n'y arrive pas.
Si je n'y arrive pas j'essaierais comme j'ai fait, mais ça me semble difficile aussi (je ne sais pas comme pointer sur une ligne, mais ça doit être possible).

[dehorter olivier]

Je suis désolé mais je ne comprend pas

Code :

1
2
3
4
5
6
7
8
SELECT DISTINCT TableA.TRACKING_ID
         TableA.CORRELATION_ID,
         TableA.STEP,
         TableA.DATE
FROM TableA
          INNER JOIN TableB ON (TableB.CORRELATION_ID = TableA.CORRELATION_ID AND TableB.DATE
 = TableA.DATE)
WHERE TableA.Step = 'Trait'

devrait fonctionner.

Au fait : Très mauvaise idée d'utiliser comme nom de colonne un mot-clé (Date)

[ZoomBox]

Tu n'as pas à être désolé, je pense que le problème est très complexe et il faut considérer absolument chaque élément.

Dans ta requette, tu fais une jointure sur l'égalité des CORRELATION_ID (très bien, normal, c'est ce qui relie les étapes d'un flux) mais tu relis aussi sur l'égalité de la date alors que justement, la date ne peut être la même. Au sein d'un même flux, le STEP 'Entr' arrive premier, ENSUITE le 'Trait' ENSUITE le 'Sort'.
On me donne le 'Sort' et je veux le 'Trait' qui lui correspond donc logiquement, le flux qui a le même CORRELATION_ID ET qui est un 'Trait' ET qui s'est déroulé avant ET qui s'est déroulé au plus tard possible.

C'est vraiment un gros camboui cette base, désolé.

Et pour le champ Date, je savais que c'est un mot réservé, en réalité le nom n'est pas "Date" j'ai juste rendu ça plus clair (et au pire, je met des [ ] )

[dehorter olivier]

je crois commencer à comprendre

TRACKING_ID|CORRELATION_ID|STEP|DATE
------------------------------------------
0004............|B....................|Entr..|13/10/10
0005............|B....................|Trai..|14/10/10
0006............|B....................|Sort..|15/10/10
0007............|B....................|Entr..|16/10/10
0008............|B....................|Trai..|17/10/10
0009............|B....................|Sort..|18/10/10
etc...

L'erreur est parce qu'il y a 2 flux différents pour B, Oui ?

Tu trouve cette erreur en générant une table qui indique le 2eme élément en "Sort", Oui ?

CORRELATION_ID|STEP|DATE
--------------------------------
B....................|Sort..|18/10/10

Question : la sortie doit être le TRACKING_ID #0007 (avec sa date) mais pourquoi le flux est "Trai" qui correspond à 0008 ? pas plus simple d'avoir toute la ligne 0007 Non ?
Sortie

TRACKING_ID|CORRELATION_ID|STEP|DATE
------------------------------------------
0007............|B....................|Trai..|16/10/10

[ZoomBox]

Je crois qu'en effet tu commence à comprendre
Voilà, le problème (pourquoi dire "erreur" ? ) c'est que un flux (Entr Trait Sort) peut être présent plus sieurs fois sur une même table et qu'il me faut celui directement plus récent.


Par contre our le
CORRELATION_ID|STEP|DATE
------------------------------
B....................|Sort..|18/10/10

la sortie doit être

TRACKING_ID|CORRELATION_ID|STEP|DATE
------------------------------------------
0008............|B....................|Trai..|17/10/10

Car comme je l'ai dit, à un STEP 'Sort' je doit obtenir le STEP 'Trai' qui s'est passé juste avant. En fait, le 'Entr' ne doit pas du tout entrer en compte ici.

En fait, dans mon exemple, c'est presque comme ci on me donnais un TRACKING_ID, et que je n'avais qu'à faire " -1 " pour avoir directement la ligne au dessus.
Mais en réalité c'est tout autrement, le TRACKING_ID est une grande chaine de caractère aléatoire et l'espace entre les STEPs se joue à quelques millisecondes donc ils se chevauche souvent.

Encore merci de ton aide, c'est vrai qu'il faut bien s'imprégner de la table qui est assez complexe (enfin, moi je baigne dedans depuis 1 mois) afin d'être efficace. Bonsoir.

[dehorter olivier]

peux tu essayer cela

(j'ai remplace DATE par Date1)

Code sql :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 
SELECT TableA.*
  FROM (SELECT TableA.Correlation_Id,
               MAX(TableA.Date1) AS Date_Step
          FROM TableA
          WHERE TableA.Step = 'Trai'
          GROUP BY TableA.Correlation_Id
          HAVING COUNT(*) > 1) AS Selection
    INNER JOIN TableA ON (TableA.Correlation_Id = Selection.Correlation_Id AND TableA.Date1 = Selection.Date_Step)
  WHERE EXISTS(SELECT 1
                 FROM TableA Sortie
                 WHERE Sortie.Correlation_Id = TableA.Correlation_Id
                         AND Sortie.Step = 'Sort'
                         AND Sortie.Date1 >= TableA.Date1)

La sous requete recherche tous les 'Correlation_Id' qui ont présentés plus de 1 'Trait'.
Le clause WHERE indique que seuls seront affichés les 'correlation_id' qui sont 'sorti' APRES le dernier 'Trai'

[ZoomBox]

J'essaierais, merci !
Je n'ai pas accès à la BDD de chez moi.

[ZoomBox]

Bonjour !

Petit question: Ligne 11, tu as mis "FROM TableA Sortie" qu'est ce que ça signifie ?
Ce serait pas plutôt 11. FROM TableB AS Sortie ?

Si c'est bien ça, ta requête m'a l'air sur la bonne voie mais je n'ai pas encore les résultats escomptés, elle ne me retourne que 2 lignes (alors qu'il y en a un 30aine dans TableB). Ces 2 lignes sont bonnes cependant.

Autre question à propos de ta requête, pourquoi limites-tu le résultat de la sous-requête aux flux ayant été rejoués (càd, quand il y a plusieurs fois le STEP 'Trai' pour un même CORRELATION_ID) à la ligne 8. Car je veux aussi les STEP 'Trai' correspondant aux CORRELATION_ID donnés même s'il n'ont pas été rejoués (donc même si on ne trouve qu'une fois le groupe de 3 STEPs pour un CORRELATION_ID donné).

Merci encore. Globalement j'ai un peu de mal à comprendre ta requête, c'est ma première année d'informatique de gestion (BTS) et même si je connais tous les termes, j'ai encore un peu de mal à comprendre ce qu'elle fait exactement.

[dehorter olivier]

Petit question: Ligne 11, tu as mis "FROM TableA Sortie" qu'est ce que ça signifie ?
Ce serait pas plutôt 11. FROM TableB AS Sortie ?

oui, c'est un allias, selon les SGBD le AS peut être omis (mais je prefere le mettre, oublies de ma part)

Autre question à propos de ta requette, pourquoi limites-tu le resultat de la sous-requête aux flux ayant été rejoué (càd, quand il y a plusieurs fois le STEP 'Trai' pour un même CORRELATION_ID) à la ligne 8. Car je veux aussi les STEP 'Trai' correspondant aux CORRELATION_ID donné même s'il n'ont pas été rejoué (donc même si on ne trouve qu'une fois le groupe de 3 STEPs pour un CORRELATION_ID donné).

c'est ce que j'avais cru comprendre

Un flux est "en erreur" si le STEP 'Sortant' s'est mal passé (c'est une colonne de la table "Status", mais il est inutile de la mentionner)

recontruit la sous-requête pour intégrer ce problème de "Status" et enlève le HAVING

[ZoomBox]

À propos du "As" oui, ça j'avais cru comprendre. Par contre tu parles de la TableA et non de la TableB à la ligne 11. je me demande si c'est une erreur de ta part (je suppose que oui).

C'est ce que j'avais cru comprendre

Au temps pour moi si je me suis mal exprimé, mais dans n'importe quel cas, je veux les info du STEP 'Trait' le plus récent, même si le flux n'est pas en double.

recontruit la sous-requête pour intégrer ce problème de "Status" et enlève le HAVING

Non, il n'y a pas de soucis avec ça, ce n'était qu'une précision superflue, en fait les flux en erreur je ne peux pas vraiment les connaitres entièrement (ça dépend de plusieurs chose [dont le status] que je ne connais pas), tout ce que je sais c'est les infos des STEPs 'Sort' de ces flux en erreur. Ces infos sont dans la TableB.


En fait, j'ai décomposé ma requête "en français" et voici la partie que je n'arrive pas à traduire en sql:

Pour chaque couple CORRELATION_ID/DATE d'une table, obtenir la DATE (et les autre infos) des STEP 'Trait' plus récents ayant le même CORRELATION_ID

Donc j'a iessayé ça:

Code :

1
2
3
4
SELECT A.CORRELATION_ID, A.STEP, max(A.DATE) AS Date_Max
FROM TableA AS A INNER JOIN TableB AS B ON (A.Date < B.DATE) AND (A.CORRELATION_ID = B.CORRELATION_ID)
WHERE STEP = 'Trait'
GROUP BY A.CORRELATION_ID, A.STEP

Mais en fait, impossible de savoir si ça correspond (en fait, il n'y a pas d'erreur de ce type aujourd'hui... et mes tables sont liés donc misent à jour et donc impossible de vérifier.

Ca te semble correct ? (comme tu peux le deviner, c'est surtout sur la jointure que j'ai un petit doute, étant donné que je n'ai jamais appris à faire de jointure à plusieurs arguments en cours (...ON... AND ...).)

[dehorter olivier]

avec tes éléments en exemple (1er message)

en modifiant ta requête, j’obtiens les résultats escomptés

Code sql :

1
2
3
4
5
6
7
8
SELECT A.Correlation_id,
       A.Step,
       MAX(A.Date) AS Date_max
  FROM Tablea AS A
    INNER JOIN Tableb AS B ON (A.Date <= B.Date AND A.Correlation_id = B.Correlation_id 
AND A.Step = 'Trai')
GROUP BY A.Correlation_id, A.Step

mais c'est dommage de passer par une table intermédiaire

[ZoomBox]

Merci !
Etonnant de préciser "WHERE STEP = 'Trai' " sur la jointure, mais j'ai lu il y a peu que c'était possible donc je veux bien te croire
Excuse l'indentation absente de mon sql, j'avais oublié (c'est parce que j'execute mes requête dans du code VBA et on ne peut pas vraiment les mettres en page car un saut de ligne en VBA signifie une fin d'instruction).

Et oui, je sais pour la table intermédiaire, mais elle m'est fournie par un logiciel que je dois exploiter (très utilisé dans toute l'entreprise) sous forme de fichier Excel à importer sous Access.

Bon bah en te souhaitant une bonne soirée! Merci de ton aide, j'ai plus qu'à compléter la requête et me faire mon interface !

Edit: Je mettrais "Résolu" une fois le formulaire terminé car je vais peut être rencontrer d'autre problèmes (et comme tu es imprégné de la structure de la base...!).