A voir également:
- Pl/sql
- Logiciel sql - Télécharger - Bases de données
- Requete sql pix ✓ - Forum MySQL
- Sql lister les tables ✓ - Forum Programmation
- Winrar 64 ccm pl - Télécharger - Compression & Décompression
- Ora-06502: pl/sql : erreur numérique ou erreur sur une valeur: tampon de chaîne de caractères trop petit ✓ - Forum Oracle
1 réponse
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
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
merci thierry