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
| select CUF_C_UF, mat_c_agent
from taff b
where
AFF_D_DEBUT <= to_date('01/01/2007','DD/MM/YYYY')
and AFF_D_FIN>=LAST_DAY(to_date('01/01/2007','DD/MM/YYYY'))
and EHR_C_EH='21066'
and AFF_D_DEBUT=
(
select max(c.AFF_D_DEBUT)
from taff c
where c.MAT_C_AGENT=b.MAT_C_AGENT
and c.AFF_D_DEBUT<=to_date('01/01/2007','DD/MM/YYYY')
)
CREATE TABLE TAFF (
EHR_C_EH CHAR (5) NOT NULL,
MAT_C_AGENT VARCHAR2 (14) NOT NULL,
AFF_D_DEBUT DATE NOT NULL,
AFF_C_NATURE CHAR (3) NOT NULL,
EHR_C_EHUF CHAR (5) NOT NULL,
CUF_C_UF VARCHAR2 (17) NOT NULL,
AFF_D_APPAR DATE NOT NULL,
AFF_D_DISPAR DATE NOT NULL,
AFF_D_FIN DATE NOT NULL,
AFF_N_POINTS NUMBER (6),
AFF_N_NUMER NUMBER (6),
AFF_N_DENOM NUMBER (6),
CONSTRAINT PK_TAFF
PRIMARY KEY ( EHR_C_EH, MAT_C_AGENT, AFF_D_DEBUT, AFF_C_NATURE, EHR_C_EHUF, CUF_C_UF, AFF_D_APPAR )
USING INDEX
TABLESPACE AGEINDEX PCTFREE 10
STORAGE ( INITIAL 90232K NEXT 4632K PCTINCREASE 0 ))
TABLESPACE AGEDATA NOLOGGING
PCTFREE 10
PCTUSED 40
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 78499840
NEXT 1331200
MINEXTENTS 1
MAXEXTENTS 99
FREELISTS 1 FREELIST GROUPS 1 )
NOCACHE;
ALTER TABLE PH7.TAFF ADD CONSTRAINT FK_TAFF_AFF_CUF_TCUF
FOREIGN KEY (EHR_C_EH, CUF_C_UF)
REFERENCES PH7.TCUF (EHR_C_EH, CUF_C_UF) ;
ALTER TABLE PH7.TAFF ADD CONSTRAINT FK_TAFF_AFF_MAT_TMAT
FOREIGN KEY (EHR_C_EHUF, MAT_C_AGENT)
REFERENCES PH7.TMAT (EHR_C_EH, MAT_C_AGENT) ;
CREATE INDEX AFF_CUF_FK ON
TAFF(EHR_C_EH, CUF_C_UF)
TABLESPACE AGEINDEX PCTFREE 10 STORAGE(INITIAL 31744000 NEXT 4706304 PCTINCREASE 10 )
;
CREATE INDEX AFF_MAT_FK ON
TAFF(EHR_C_EHUF, MAT_C_AGENT)
TABLESPACE AGEINDEX PCTFREE 10 STORAGE(INITIAL 34816000 NEXT 4943872 PCTINCREASE 10 )
; |
Partager