Creating a view based on history tables

Question

I want to create a SQL view based on a database with history tables.

This is the best solution (fast and effective) for this. I have not created an application, and I cannot update the database tables. I can only create views.

Here is the context:

My application manages contracts. The contract has general information and is associated with contacts, legal links and portfolios. When the update is done, a new row (new ID_HIST) is added to the history tables. If the update is carried out by contacts, legal links or portfolios, a new row is added equally to the contract_HIST table (with the same ID_HIST).

My goal is to create a view to display updates done in a row (ID HIST) compared to the previous HIST ID, for example:

Thus, for each new update (for a certain date received thanks to ID HIST), we can check whether general information, contacts, legal links and / or portfolios are updated.

The following is the structure of the database:

Here in the table one or more portfolios can be assigned for the same update.

For information: if, for example, during a new update, contacts were deleted for the contract, a new row (with a new ID_HIST) is added to contract_hist for this contract, but a new row is not added to the contact_hist table. This is the same for legal links and portfolios.

The view should be displayed here:

Here are the scripts for the database to test:

-------------------------------------------------------- -- 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 (2,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; 

Answer 1

Here we go:

first: create a saved function (or function in a package) as follows:

 create or replace function test_history(i_contract_id in number, i_date_created in date, i_type in varchar2) return varchar2 is l_sql varchar2(1000); l_result number; begin l_sql := 'select 1 from test_history_tb where id_contract = :1 and date_creation = :2 and ' || i_type || ' = :3 and rownum = 1'; execute immediate l_sql into l_result using i_contract_id, i_date_created, 'update'; return('update'); exception when no_data_found then return('no_update'); end; 

second: create your request based on the function:

 create view xxx as select id_contract, date_creation, test_history(a.id_contract, a.date_creation, 'general_info') general_info, test_history(a.id_contract, a.date_creation, 'contract') contract, test_history(a.id_contract, a.date_creation, 'legal') legal, test_history(a.id_contract, a.date_creation, 'portfolio') portfolio from test_history_tb a group by a.id_contract, a.date_creation; 

this solution is not so fast because for each line we have a function call. but if you filter the data with the where clause, this will be an alternative.

example data in the table:

... and this is what the query looks like in the view: