Discussion :

[aieeeuuuuu]

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

[aieeeuuuuu]

essayez ceci :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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

[coeurdange]

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.

[coeurdange]

essayez ceci :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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.

[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

[coeurdange]

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...