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

**coeurdange** · 22/07/2015, 11h48

Bonjour,

Pourriez-vous m'aider concernant l'élaboration d'une vue en SQL sur une base de données contennat des tables d'historisation? Je ne sais pas trop comment m'y prendre pour que ce soit le plus efficace possible

Voici le Contexte:

Mon application permet de gérer des contrats (un contrat contient un ensemble d'informations générales et est lié à des contacts, des "legal references" et des portfolios). Quand une mise à jour est réalisée, une nouvelle entrée est insérée dans les tables d'historisation. Si une modification est faite sur les contacts, legal reference ou portfolio, une nouvelle ligne est également ajoutée dans la table contrat_HIST.

Mon objectif est de créér une vue qui me permettrait sur base des différentes tables d'historisation d'afficher les mises à jour (par rapport à un ID HIST donné).

Je souhaiterais avoir quelque chose comme cela:

Nom : Cap.PNG
Affichages : 500
Taille : 18,9 Ko

Nom : Cap.PNG
Affichages : 500
Taille : 18,9 Ko

Ainsi pour chaque nouvelle mise à jour (à une date t grâce à ID HIST), on peut savoir si ce sont les données générales, les contacts, les légales références ou/et les activités qui ont été mis à jour.

Voici la structure des tables de ma base de données:

Table contrat HIST (informations générales)

ID HIST	Date Creation	ID Contrat	Titre Contrat	Description	Budget
1	01/01/2015	1	Contrat 1	Contrat Informatique	20 000
2	15/01/2015	1	Contrat 1	Contrat Informatique	50 000
3	02/02/2015	2	Contrat 2	Contrat Santé	10 000
4	01/03/2015	2	Contrat 2	Contrat Consommateur	30 000
5	01/07/2015	1	Contrat 1	Contrat Informatique	50 000

Table contact HIST (liaison avec les contacts)

ID HIST	ID Contrat	Contact name
1	1	Bernard
1	1	Jean
2	1	Nicolas
2	1	Jean
3	2	Nicolas
5	2	Nicolas

Table Legal Refrence HIST (liaison avec les Legal Reference)

ID HIST	ID Contrat	LegalRef name
1	1	45 - Technologies et Systeme d'Information
3	2	105 - Consommateur et Santé
5	1	27 - Services

Table Portfolio HIST (liaison avec les Portfolios)

ID HIST	ID Contrat	ID Portfolio	Portfolio name	Portfolio Value
2	1	1	Portfolio 1	5000
2	1	2	Portfolio 2	7000
4	2	1	Portfolio 1	2000
4	2	2	Portfolio 2	8000

Ainsi je souhaiterais avec cette vue avoir cela:

ID Contrat	ID HIST	Date Creation	GENERAL INFO	CONTACT	LEGAL REFERENCE	PORTFOLIO
1	1	01/01/2015	Updated	Updated	Updated	No Update
1	2	15/01/2015	Updated	Updated	Updated	Updated
1	5	01/07/2015	No Update	Updated	Updated	No Update
2	3	02/02/2015	Updated	Updated	No Update	No Update
2	4	01/03/2015	Updated	No Update	No Update	Updated

Je joints ici les scripts pour la BDD:

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
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
--------------------------------------------------------
--  DDL for Table CONTACT_HIST
--------------------------------------------------------
 
  CREATE TABLE "CONTACT_HIST" 
   (	"ID_HIST" NUMBER, 
	"ID_CONTRAT" NUMBER, 
	"NAME_CONTACT" VARCHAR2(20 BYTE)
   ) SEGMENT CREATION IMMEDIATE 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "RAM" ;
REM INSERTING into BO.CONTACT_HIST
SET DEFINE OFF;
Insert into BO.CONTACT_HIST (ID_HIST,ID_CONTRAT,NAME_CONTACT) values (1,1,'Bernard');
Insert into BO.CONTACT_HIST (ID_HIST,ID_CONTRAT,NAME_CONTACT) values (1,1,'Jean');
Insert into BO.CONTACT_HIST (ID_HIST,ID_CONTRAT,NAME_CONTACT) values (2,1,'Nicolas');
Insert into BO.CONTACT_HIST (ID_HIST,ID_CONTRAT,NAME_CONTACT) values (2,1,'Jean');
Insert into BO.CONTACT_HIST (ID_HIST,ID_CONTRAT,NAME_CONTACT) values (3,2,'Nicolas');
Insert into BO.CONTACT_HIST (ID_HIST,ID_CONTRAT,NAME_CONTACT) values (5,2,'Nicolas');
 
--------------------------------------------------------
--  DDL for Table CONTRAT_HIST
--------------------------------------------------------
 
  CREATE TABLE "BO"."CONTRAT_HIST" 
   (	"ID_HIST" NUMBER, 
	"DATE_CREATION" DATE, 
	"ID_CONTRAT" NUMBER, 
	"TITRE_CONTRAT" VARCHAR2(250 BYTE), 
	"DESCRIPTION" VARCHAR2(250 BYTE), 
	"BUDGET" NUMBER
   ) SEGMENT CREATION IMMEDIATE 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "RAM" ;
REM INSERTING into BO.CONTRAT_HIST
SET DEFINE OFF;
Insert into BO.CONTRAT_HIST (ID_HIST,DATE_CREATION,ID_CONTRAT,TITRE_CONTRAT,DESCRIPTION,BUDGET) values (1,to_date('01-JAN-15','DD-MON-RR'),1,'Contrat 1 ','Contrat Informatique ',20000);
Insert into BO.CONTRAT_HIST (ID_HIST,DATE_CREATION,ID_CONTRAT,TITRE_CONTRAT,DESCRIPTION,BUDGET) values (2,to_date('15-JAN-15','DD-MON-RR'),1,'Contrat 1 ','Contrat Informatique ',50000);
Insert into BO.CONTRAT_HIST (ID_HIST,DATE_CREATION,ID_CONTRAT,TITRE_CONTRAT,DESCRIPTION,BUDGET) values (3,to_date('02-FEB-15','DD-MON-RR'),2,'Contrat 2 ','Contrat Santé ',10000);
Insert into BO.CONTRAT_HIST (ID_HIST,DATE_CREATION,ID_CONTRAT,TITRE_CONTRAT,DESCRIPTION,BUDGET) values (4,to_date('01-MAR-15','DD-MON-RR'),2,'Contrat 2 ','Contrat Consommateur ',30000);
Insert into BO.CONTRAT_HIST (ID_HIST,DATE_CREATION,ID_CONTRAT,TITRE_CONTRAT,DESCRIPTION,BUDGET) values (5,to_date('01-JUL-15','DD-MON-RR'),1,'Contrat 1 ','Contrat Informatique ',50000);
--------------------------------------------------------
--  DDL for Index CONTRAT_HIST_PK
--------------------------------------------------------
 
  CREATE UNIQUE INDEX "BO"."CONTRAT_HIST_PK" ON "BO"."CONTRAT_HIST" ("ID_HIST") 
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "RAM" ;
--------------------------------------------------------
--  Constraints for Table CONTRAT_HIST
--------------------------------------------------------
 
  ALTER TABLE "BO"."CONTRAT_HIST" ADD CONSTRAINT "CONTRAT_HIST_PK" PRIMARY KEY ("ID_HIST")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "RAM"  ENABLE;
  ALTER TABLE "BO"."CONTRAT_HIST" MODIFY ("ID_HIST" NOT NULL ENABLE);
 
--------------------------------------------------------
--  DDL for Table LEGAL_REFERENCE_HIST
--------------------------------------------------------
 
  CREATE TABLE "BO"."LEGAL_REFERENCE_HIST" 
   (	"ID_HIST" NUMBER, 
	"ID_CONTRAT" NUMBER, 
	"LEG_REF_NAME" VARCHAR2(250 BYTE)
   ) SEGMENT CREATION IMMEDIATE 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "RAM" ;
REM INSERTING into BO.LEGAL_REFERENCE_HIST
SET DEFINE OFF;
Insert into BO.LEGAL_REFERENCE_HIST (ID_HIST,ID_CONTRAT,LEG_REF_NAME) values (1,1,'45 - Technologies et Systeme d''Information');
Insert into BO.LEGAL_REFERENCE_HIST (ID_HIST,ID_CONTRAT,LEG_REF_NAME) values (3,2,'105 - Consommateur et Santé');
Insert into BO.LEGAL_REFERENCE_HIST (ID_HIST,ID_CONTRAT,LEG_REF_NAME) values (5,1,'27 - Services');
 
--------------------------------------------------------
--  DDL for Table PORTFOLIO_HIST
--------------------------------------------------------
 
  CREATE TABLE "BO"."PORTFOLIO_HIST" 
   (	"ID_HIST" NUMBER, 
	"ID_CONTRAT" NUMBER, 
	"PORTFOLIO_ID" NUMBER, 
	"PORTFOLIO_NAME" VARCHAR2(250 BYTE), 
	"PORTFOLIO_VALUE" NUMBER
   ) SEGMENT CREATION IMMEDIATE 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "RAM" ;
REM INSERTING into BO.PORTFOLIO_HIST
SET DEFINE OFF;
Insert into BO.PORTFOLIO_HIST (ID_HIST,ID_CONTRAT,PORTFOLIO_ID,PORTFOLIO_NAME,PORTFOLIO_VALUE) values (2,1,1,'Portfolio 1',5000);
Insert into BO.PORTFOLIO_HIST (ID_HIST,ID_CONTRAT,PORTFOLIO_ID,PORTFOLIO_NAME,PORTFOLIO_VALUE) values (2,1,2,'Portfolio 2',7000);
Insert into BO.PORTFOLIO_HIST (ID_HIST,ID_CONTRAT,PORTFOLIO_ID,PORTFOLIO_NAME,PORTFOLIO_VALUE) values (4,2,1,'Portfolio 1',2000);
Insert into BO.PORTFOLIO_HIST (ID_HIST,ID_CONTRAT,PORTFOLIO_ID,PORTFOLIO_NAME,PORTFOLIO_VALUE) values (4,2,2,'Portfolio 2',8000);
commit;

Merci d'avance pour votre aide.

Seb

**Waldar** · 22/07/2015, 12h08

Je pense que vos données dans contact_hist sont fausses, les ID_HIST et les ID_CONTRAT ne sont pas les mêmes que dans la table contrat_HIST.
Il manque aussi un id_contact dans cette table.

Essayez de présenter vos données sous forme de create table + insert, ça permet à tout le monde de tester.

**coeurdange** · 22/07/2015, 12h21

Envoyé par Waldar

Je pense que vos données dans contact_hist sont fausses, les ID_HIST et les ID_CONTRAT ne sont pas les mêmes que dans la table contrat_HIST.
Il manque aussi un id_contact dans cette table.

Essayez de présenter vos données sous forme de create table + insert, ça permet à tout le monde de tester.

Les données sont correctes, un contrat peut avoir 0 ou plusieurs contacts. Chaque fois qu'il y a une modification au niveau des contacts d'un contrat, on réinsère dans la tables contacts_hist les liens.

**escartefigue** · 22/07/2015, 12h46

Si je comprends bien, chacune des 3 tables historiques est facultative, et un occurrence n'est créée que si les modification faites à un instant "t" dans un contrat la concerne.
Du coup il faut faire une jointure full outer join entre les 3 tables historique et mettre en forme les "updated" / "not/updated" avec des case

**escartefigue** · 22/07/2015, 13h28

Un truc de ce genre du coup :

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
   SELECT COALESCE(GE.ID_Contrat, CO.ID_Contrat, LG.ID_Contrat, PF.ID_Contrat)
         ,CASE
             WHEN GE.ID_Contrat IS NULL THEN "NO Upd" 
             ELSE "Upd" 
          END as MAJ_GE
         ,CASE
             WHEN CO.ID_Contrat IS NULL THEN "NO Upd" 
             ELSE "Upd" 
          END as MAJ_CO
         ,CASE
             WHEN LG.ID_Contrat IS NULL THEN "NO Upd" 
             ELSE "Upd" 
          END as MAJ_LG
         ,CASE
             WHEN PF.ID_Contrat IS NULL THEN "NO Upd" 
             ELSE "Upd" 
          END as MAJ_PF
   FROM contrat_HIST as GE
   FULL OUTER JOIN contact_HIST as CO
     ON CO.ID_Contrat = GE.ID_Contrat
   FULL OUTER JOIN Legal_Refrence_HIST as LG
     ON LG.ID_Contrat = GE.ID_Contrat
   FULL OUTER JOIN Portfolio_HIST as PF
     ON PF.ID_Contrat = GE.ID_Contrat

En espérant que les tables ne sont pas trop volumineuses, car full outer join sur 4 tables ca risque de dépoter !

**mnitu** · 22/07/2015, 13h57

En espérant que les tables ne sont pas trop volumineuses, car full outer join sur 4 tables ca risque de dépoter !

Cela a plus l'air mythologique que réel. Pour ne pas dire que "volumineuse" signifie tout et n'importe quoi!

**coeurdange** · 22/07/2015, 14h49

Envoyé par escartefigue

Un truc de ce genre du coup :

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
   SELECT COALESCE(GE.ID_Contrat, CO.ID_Contrat, LG.ID_Contrat, PF.ID_Contrat)
         ,CASE
             WHEN GE.ID_Contrat IS NULL THEN "NO Upd" 
             ELSE "Upd" 
          END as MAJ_GE
         ,CASE
             WHEN CO.ID_Contrat IS NULL THEN "NO Upd" 
             ELSE "Upd" 
          END as MAJ_CO
         ,CASE
             WHEN LG.ID_Contrat IS NULL THEN "NO Upd" 
             ELSE "Upd" 
          END as MAJ_LG
         ,CASE
             WHEN PF.ID_Contrat IS NULL THEN "NO Upd" 
             ELSE "Upd" 
          END as MAJ_PF
   FROM contrat_HIST as GE
   FULL OUTER JOIN contact_HIST as CO
     ON CO.ID_Contrat = GE.ID_Contrat
   FULL OUTER JOIN Legal_Refrence_HIST as LG
     ON LG.ID_Contrat = GE.ID_Contrat
   FULL OUTER JOIN Portfolio_HIST as PF
     ON PF.ID_Contrat = GE.ID_Contrat

En espérant que les tables ne sont pas trop volumineuses, car full outer join sur 4 tables ca risque de dépoter !

Merci pour les réponses :-)

j'essaye de tester cela mais j'ai une erreur "00933. 00000 - 'SQL command not properly ended' " au niveau de la ligne "FROM contrat_HIST as GE". Je n'arrive pas à voir pourquoi... la syntaxe semble être correcte