Pl/sql

voila un exemple

declare

@Erreur int,
@NomProc varchar(30)

/*************************************************************************/

select

C.RecFdsCod as CodeFond,
A.acnominor as NomFond,
F.nav_id as nav,
B.pvl_smsecid as Codeodw,
B.pvl_nominal as Quantité,
B.pvl_positiondate as Date

into
#Req
from
RecFds C,
odw_live_pr_account A,
odw_live_pp_netassetvalue F,
odw_live_pp_positionvalue B
where

C.RecFdsCod = isnull(@CodFds, C.RecFdsCod)
and C.RecFdsFds = A.acnominor
and A.acid=F.nav_acid
and convert(varchar,F.nav_valdate,103)=convert(varchar,@Dat,103)
and F.nav_posntypeid=4
and F.nav_id = B.pvl_nav_id
/*
Select @Erreur = @@Error
if @Erreur != 0 Goto Erreur
*/
select
R.CodeFond as codefonds,
R.Nomfond as nomfonds,
R.Codeodw as codetitre,
R.Date as date,
R.Quantité as quantité,
E1.sxvalue as codehiport,
E2.sxvalue as externalcode1

into
#Req2

from
#Req R,
odw_live_pr_secxref E1,
odw_live_pr_secxref E2

where
R.Codeodw *= E1.smsecid
and E1.sxitm = 100
and R.Codeodw *= E2.smsecid
and E2.sxitm = 1004

select
R2.codefonds as CodeFond,
R2.nomfonds as NomFond,
R2.codetitre as CodeTitre,
R2.date as Date,
D.instrtype as Typeinstrument,
R2.quantité as Quantité,
R2.codehiport as CodeHiport,
SN.smdesc1 as libelle,
R2.externalcode1 as ExternalCode1,
R.RecRefTitCodrga as Code,
D.mktcode as Marche,
R.RecRefTitCodisin as CodeIsin,
Case
When ( R2.codefonds > 1000 and substring(D.instrtype,1,2) != 'DS' ) then R.RecRefTitCodisin
When (substring(D.instrtype,1,2)) = 'DS' Then R2.externalcode1
When (substring(D.instrtype,1,2)) = 'OS' Then R.RecRefTitCodRga
When (substring(D.instrtype,1,2)) = 'DS' Then R2.externalcode1
When (substring(D.instrtype,1,2)) = 'FI' Then RecRefTitCodRga
end as chmp

from
#Req2 R2,
odw_live_pr_secmast D,
odw_live_pr_secname SN,
RecRefTit R

where
R2.codetitre = D.smsecid
and R2.codetitre = SN.smsecid
and R2.codetitre *= R.RecRefTitCodOdw
and substring(D.instrtype,1,1) != 'M'
and substring(D.instrtype,1,2) != 'FX'
and substring(D.instrtype,1,4) = isnull(@TypIns, substring(D.instrtype,1,4))

order by CodeFond,Typeinstrument,chmp

end