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
|
TABLE RESSOURCE (
IDRESSOURCE numeric(10,0) IDENTITY,
IDPTR numeric(10,0) NULL,
DATECREATIONRESSOURCE datetime NULL,
HEURECREATIONRESSOURCE datetime NULL,
IDTYPERESSOURCE numeric(6,0) NULL,
IDTYPECONDITIONNEMENT numeric(6,0) NULL,
IDPATIENT numeric(6,0) NULL,
IDACTION_MERE numeric(6,0) NULL,
CODEBARRERESSOURCE varchar(13) NULL,
QUANTITERESSOURCE numeric(6,0) NULL,
IDUNITEQUANTITERESSOURCE numeric(6,0) NULL,
QUALITERESSOURCE varchar(25) NULL,
COMMENTAIRERESSOURCE varchar(255) NULL,
CONFORMITERESSOURCE numeric(18,0) NOT NULL,
IDEMPLACEMENTDATA numeric(10,0) NULL,
NUMEROEXT1RESSOURCE varchar(25) NULL,
NUMEROEXT2RESSOURCE varchar(25) NULL,
NUMEROEXT3RESSOURCE varchar(25) NULL,
ETATRESSOURCE varchar(20) NULL,
IDDEVENIR numeric(6,0) NULL,
TUMORALE char(1) NULL
)
TABLE ACTIONELEMENTAIRE (
IDACTION numeric(6,0) IDENTITY,
IDRESSOURCE numeric(10,0) NOT NULL,
IDTYPEACTION numeric(6,0) NOT NULL,
IDCOLLABORATEUR numeric(6,0) NULL,
DATEACTION datetime NOT NULL,
CODEBARREACTION varchar(13) NULL,
NBELEMENTS numeric(18,0) NULL,
QUANTITEUTILISEE numeric(18,0) NULL
)
select
lpad('L......................................................',4*(level-1)) || r.IDRESSOURCE as idRes,
r.IDRESSOURCE as idRessource,
p.NOMPATIENT||' '||p.PRENOMPATIENT as nomInd,
level
from
RESSOURCE r LEFT OUTER JOIN PATIENT p ON p.IDPATIENT = r.IDPATIENT,
RESSOURCE r2 LEFT OUTER JOIN EMPLACEMENTDATA ed ON ed.RESSOURCESTOCKEE_RESID = r2.IDRESSOURCE
where
r.IDRESSOURCE = r2.IDRESSOURCE
and r.idpatient = {IDPATIENT}
start with r.IDACTION_MERE is null
connect by (
select ae.IDRESSOURCE
from ACTIONELEMENTAIRE ae
where ae.IDACTION= r.IDACTION_MERE)
= prior r.IDRESSOURCE order siblings by r.IDRESSOURCE desc |