Créer une vue affichant les champs modifiés sur des tables d'historisation

Version imprimable

Voir 40 message(s) de cette discussion en une page

22/07/2015, 17h38
aieeeuuuuu

il manque le peuplement de la table contrat_hist dans votre jeu d'essai !

essayez ceci :

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
 
WITH contrat AS (
    SELECT 
        ID_HIST
      , ID_CONTRAT
      , DATE_CREATION
      , CASE WHEN
              TITRE_CONTRAT =  LAG(TITRE_CONTRAT) OVER(PARTITION BY ID_CONTRAT ORDER BY ID_HIST)
          AND Description = LAG(Description) OVER(PARTITION BY ID_CONTRAT ORDER BY ID_HIST)
          AND BUDGET = LAG(BUDGET) OVER(PARTITION BY ID_CONTRAT ORDER BY ID_HIST)
        THEN 'NO Update'
        ELSE 'Updated'
      END AS MAJ_CONTRAT
    FROM CONTRAT_HIST
  )
  SELECT 
    contrat.ID_HIST
    ,contrat.ID_Contrat
    ,contrat.DATE_CREATION
    ,contrat.MAJ_CONTRAT
 
 	,CASE
	  WHEN contact_HIST.ID_Contrat IS NULL THEN 'NO Update'
	  ELSE 'Updated'
	END as MAJ_CO,
	CASE
	  WHEN Legal_Reference_HIST.ID_Contrat IS NULL THEN 'NO Update'
	  ELSE 'Updated' 
	END as MAJ_LG,
	CASE
	  WHEN Portfolio_HIST.ID_Contrat IS NULL THEN 'NO Update'
	  ELSE 'Updated' 
	END as MAJ_PF
  FROM contrat
  LEFT JOIN (SELECT DISTINCT ID_Contrat,ID_HIST FROM contact_HIST )contact_HIST
	ON contact_HIST.ID_Contrat = contrat.ID_Contrat
	AND contact_HIST.ID_HIST = contrat.ID_HIST
  LEFT JOIN (SELECT DISTINCT ID_Contrat,ID_HIST FROM  Legal_Reference_HIST )Legal_Reference_HIST
	ON Legal_Reference_HIST.ID_Contrat = contrat.ID_Contrat
	AND Legal_Reference_HIST.ID_HIST =contrat.ID_HIST
  LEFT JOIN (SELECT DISTINCT ID_Contrat,ID_HIST FROM  Portfolio_HIST )Portfolio_HIST
	ON Portfolio_HIST.ID_Contrat = contrat.ID_Contrat
	AND Portfolio_HIST.ID_HIST =contrat.ID_HIST

23/07/2015, 07h58
coeurdange
Citation:
Envoyé par aieeeuuuuu

Dans votre premier post, dans le tableau attendu comme résultat, vous avez cette ligne (la dernière) :

2 4 01/03/2015 No Update No Update Updated Updated
Pourquoi "No Update" pour GENERAL INFO alors que la description est passée de "Contrat Santé" à "Contrat consommateur" et que le budget est passé de 10 000 à 30 000 ?
Oups, vraiment désolé, c'est une erreur de recopiage de ma part. Je viens de modifier dans le post.

Citation:
Envoyé par aieeeuuuuu
essayez ceci :
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 WITH contrat AS ( SELECT ID_HIST , ID_CONTRAT , DATE_CREATION , CASE WHEN TITRE_CONTRAT = LAG(TITRE_CONTRAT) OVER(PARTITION BY ID_CONTRAT ORDER BY ID_HIST) AND Description = LAG(Description) OVER(PARTITION BY ID_CONTRAT ORDER BY ID_HIST) AND BUDGET = LAG(BUDGET) OVER(PARTITION BY ID_CONTRAT ORDER BY ID_HIST) THEN 'NO Update' ELSE 'Updated' END AS MAJ_CONTRAT FROM CONTRAT_HIST ) SELECT contrat.ID_HIST ,contrat.ID_Contrat ,contrat.DATE_CREATION ,contrat.MAJ_CONTRAT ,CASE WHEN contact_HIST.ID_Contrat IS NULL THEN 'NO Update' ELSE 'Updated' END as MAJ_CO, CASE WHEN Legal_Reference_HIST.ID_Contrat IS NULL THEN 'NO Update' ELSE 'Updated' END as MAJ_LG, CASE WHEN Portfolio_HIST.ID_Contrat IS NULL THEN 'NO Update' ELSE 'Updated' END as MAJ_PF FROM contrat LEFT JOIN (SELECT DISTINCT ID_Contrat,ID_HIST FROM contact_HIST )contact_HIST ON contact_HIST.ID_Contrat = contrat.ID_Contrat AND contact_HIST.ID_HIST = contrat.ID_HIST LEFT JOIN (SELECT DISTINCT ID_Contrat,ID_HIST FROM Legal_Reference_HIST )Legal_Reference_HIST ON Legal_Reference_HIST.ID_Contrat = contrat.ID_Contrat AND Legal_Reference_HIST.ID_HIST =contrat.ID_HIST LEFT JOIN (SELECT DISTINCT ID_Contrat,ID_HIST FROM Portfolio_HIST )Portfolio_HIST ON Portfolio_HIST.ID_Contrat = contrat.ID_Contrat AND Portfolio_HIST.ID_HIST =contrat.ID_HIST
J'ai ajouté les données des contrats dans le SQL.

Merci beaucoup pour la requête

Voilà ce que j'obtiens:

ID_HIST ID_CONTRAT DATE_CREATION MAJ_CONTRAT MAJ_CO MAJ_LG MAJ_PF

3 2 02-FEB-15 Updated Updated NO Update NO Update

1 1 01-JAN-15 Updated Updated Updated NO Update

5 1 01-JUL-15 NO Update NO Update Updated NO Update

4 2 01-MAR-15 Updated NO Update NO Update Updated

2 1 15-JAN-15 Updated NO Update NO Update Updated

Ce tableau est presque correcte. Merci.

J'aurais besoin cependant d'une contrainte supplémentaire: entre les ID_HIST 2 et 5 , les contacts du contrat 1 sont passés de "Nicolas et Jean" à "Nicolas", ainsi il y a eu mise à jour sur la derniere ID_HIST (5) car un contact a été supprimé. Mais dans les résultats, pour ID_HIST=5, MAJ_CO= No Update.

Pour ID_HIST=2, Il y a 2 contacts qui sont ajoutés (ce qui peut arriver), cependant No update est retourné pour MAJ_CO. Par contre une erreur apparaît pour cet ID car MAJ_LG=No update alors qu'il y a ajout d'une legal reference.

23/07/2015, 11h53
aieeeuuuuu

J'avais vu les lignes en double mais j'avais pris ça pour des erreurs dans le jeu d'essai.
Si ce n'en n'est pas, cela devient plus compliqué, voire impossible dans certains cas : comment faites vous par exemple si tous les contacts sont supprimés ? Vu qu'il n'y a plus de contact, vous ne pouvez pas mettre de ligne dans la table d'historique...

Votre requete va devenir beaucoup plus complexe, tant à écrire qu'a exécuter.
Avec votre modèle, pour répondre (partiellement, cf plus haut) à votre besoin, il va falloir inclure de la division relationelle.

Quelle est votre volumétrie, et dans quel contexte cette requête sera-t-elle exécutée.

Si vous pouvez modifier votre modèle, vous devriez envisager la sixième forme normale pour gérer l'historique de vos données
23/07/2015, 16h17
coeurdange

Citation:

Envoyé par aieeeuuuuu

J'avais vu les lignes en double mais j'avais pris ça pour des erreurs dans le jeu d'essai.
Si ce n'en n'est pas, cela devient plus compliqué, voire impossible dans certains cas : comment faites vous par exemple si tous les contacts sont supprimés ? Vu qu'il n'y a plus de contact, vous ne pouvez pas mettre de ligne dans la table d'historique...

Votre requete va devenir beaucoup plus complexe, tant à écrire qu'a exécuter.
Avec votre modèle, pour répondre (partiellement, cf plus haut) à votre besoin, il va falloir inclure de la division relationelle.

Quelle est votre volumétrie, et dans quel contexte cette requête sera-t-elle exécutée.

Si vous pouvez modifier votre modèle, vous devriez envisager la sixième forme normale pour gérer l'historique de vos données

Effectivement si on supprime à un instant t tous les contacts pour un contrat donné, une ligne (avec un nouvel ID_hist) dans contrat_hist est ajoutée pour ce contrat mais aucune nouvelle ligne n'est ajoutée dans la table contact_hist. S'en est de même avec les legal refrence et les portfolios. Par contre je ne comprends pas pourquoi l'erreur avec les legal reference se produit.

Il y a quelques centaines de lignes pour les contrats et quelques milliers de lignes d'historisaton des contrats. De même pour les autres tables. Cette requête est exécutée lors de la génération d'un rapport Business Object.

Je vais regarder les liens que vous m'avez envoyés, car je pourrais créer des vues sur les tables existantes, mais j'avoue être perdue sur la meilleure façon d'arriver à répondre à mon besoin....

Le problème c'est que ce n'est pas moi qui ai modélisé ce schéma relationnel et que je ne peux pas modifier la structure des tables de l'application, vu que je ne l'ai pas développée...Mon rapport est construit sur une base de données basée sur les vues de la base de données de l'application.... Je peux simplement créér de nouvelles vues pour la bdd de mon rapport...

Voir 40 message(s) de cette discussion en une page

ID_HIST	ID_CONTRAT	DATE_CREATION	MAJ_CONTRAT	MAJ_CO	MAJ_LG	MAJ_PF
3	2	02-FEB-15	Updated	Updated	NO Update	NO Update
1	1	01-JAN-15	Updated	Updated	Updated	NO Update
5	1	01-JUL-15	NO Update	*NO Update*	Updated	NO Update
4	2	01-MAR-15	Updated	NO Update	NO Update	Updated
2	1	15-JAN-15	Updated	*NO Update*	*NO Update*	Updated