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
|
///la procedure à appler
CREATE PROCEDURE RECU ( ANA Varchar(20) )
RETURNS ( DESCLT Varchar(200) )
AS
DECLARE VARIABLE des1 varchar(200);
DECLARE VARIABLE chemin varchar(100);
DECLARE VARIABLE taille smallint;
BEGIN
des1='select des from CLT where (cod='''||ana||''')and(des is not null)';
for SELECT mon$database_name from MON$DATABASE into :chemin do
suspend;
taille=char_length(chemin);
chemin='localhost:'||substring(chemin from 1 for taille-12)||'dossier.fdb';
EXECUTE STATEMENT des1 ON EXTERNAL DATA SOURCE chemin AS USER 'sysdba' PASSWORD 'masterkey' INTO :desclt;
suspend;
///////////////////
/////la vue
CREATE VIEW V1 (DAT, BON, DESCL, PRO1, DESPR1, QTE1, PUV, MTV, PAC, FAC, DESF, PRO2, DESPR2, QTE2, PUA, MTA, PAF, DESD, DEP)
AS
select dat,'' as bon,'' as descl,'' as pro1,'' as despr1,null as qte1,null as puv,null as mtv,null as pac,fac,desf,pro as pro2,des as despr2,qte as qte2,pua,mht as mta,null as paf,null as dep,null as desd from ACH where (pro is not null)
union all
select dat,null as bon ,null as descl,null as pro1,null as despr1,null as qte1,null as puv,null as mtv,null as pac,fac,null as desf,null as pro2,null as despr2,null as qte2,null as pua ,null as mta,ver as paf,null as dep,null as desd from PAF
union all
select dat,bon,descl,pro as pro1,des as despr1,qte as qte1,puv,mht as mtv,null as pac,null as fac,null as desf,null as pro2,null as despr2,null as qte2,null as pua ,null as mta,null as paf,null as desd,null as dep from VEN where (pro is not null)
union all
select dat,fac as bon ,(select des from recu(pac.ana)) as descl,null as pro1,null as despr1,null as qte1,null as puv,null as mtv,ver as pac,null as fac,null as desf,null as pro2,null as despr2,null as qte2,null as pua ,null as mta,null as paf,null as desd,null as dep from PAC
union all
select dat,null as bon ,null as descl,null as pro1,null as despr1,null as qte1,null as puv,null as mtv,null as pac,null as fac,null as desf,null as pro2,null as despr2,null as qte2,null as pua ,null as mta,null as paf,des as desd,dep from RED where (cas<>2)and(cas<>3)
; |
Partager