Dear Expert,
Could you please help me for the creation of a SQL view based on a database with history tables? I do not know which is the best solution (fast and efficient) in order to do that. For information I have not created the application and I cannot update the database tables. I can only create views.
Here the context:
My application allows to manage contracts (a contract has general information and is linked to contacts, legal references and portfolios). When an update is done, a new line is added in the history tables (new ID_HIST). If an update is done on the contacts, legal reference or portfolios, a new line is equally added in the table contrat_HIST (with the same ID_HIST).
My purpose is the creation of a view allowing to display the updates done on a row (ID HIST) compared to the previous ID HIST like:
Thus for each new update (for a specific date t wanted, given thanks to ID HIST), we can see if the general information, the contacts, the legal references or/and the portfolios have been updated.
Here below the structure of the database:
Table contract_HIST (general information)
| ID_HIST | Date_Creation | ID_Contract | Title_Contract | 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 (link with contacts)
| ID_HIST | ID_Contract | Contact_name |
| 1 | 1 | Bernard |
| 1 | 1 | Jean |
| 2 | 1 | Nicolas |
| 2 | 1 | Jean |
| 3 | 2 | Nicolas |
| 5 | 1 | Nicolas |
Here in the table one or several contacts can be assigned to a contract for a same update.
Table Legal Refrence HIST (liaison avec les Legal Reference)
| ID_HIST | ID_Contract | LegalRef_name |
| 1 | 1 | 45 - Information System |
| 2 | 1 | 105 - Health and Consumer |
| 5 | 1 | 27 - Services |
Here in the table one or several Legal references can be assigned to a contract for a same update.
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 |
Here in the table one or several Portfolios can be assigned to a contract for a same update.
For information: if for example during a new update, the contacts have been deleted for a contract, a new line (with a new ID_HIST) is added in contrat_hist for this contract BUT no new line is added in the table contact_hist. But "UPDATED" should be displayed. It's the same way for legal reference and portfolios.
Here the view should display:
| ID_Contract | 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 |
Here the scripts for the database for testing:
--------------------------------------------------------
-- 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,1,'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 (2,1,'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;
Thank you very much in advance for your help.
Seb