Re: Prepared Statement limit ? - Mailing list pgsql-jdbc
| From | ygloriau@siliage.fr |
|---|---|
| Subject | Re: Prepared Statement limit ? |
| Date | |
| Msg-id | 1035909815.3dbebab7bcf5f@195.25.244.199 Whole thread Raw |
| In response to | Re: Prepared Statement limit ? (Dave Cramer <Dave@micro-automation.net>) |
| Responses |
Re: Prepared Statement limit ?
|
| List | pgsql-jdbc |
To test the query in pg explorer i only replace the ? by their values.
Here is the query :
public static String SELECT_FLUX_AVANCE_TOUS= " SELECT "
+" FLT.FLT_ID, PER_ID, PER_LIBELLE, FLR.FLR_ID,UPPER(ech.ECH_LIBELLE) as
ECH_LIBELLE, UPPER(flt.FLT_NOM) as FLT_NOM, "
+" UPPER(flr.FLR_NOMREEL) as FLR_NOMREEL, UPPER(parm.PAR_NOM) as parm_nom,
UPPER(para.PAR_NOM) as parv_nom, flr.FLR_ARRIVEE, "
+" acr.ACR_ATT_CODE, apm.APM_VALEUR, ('OK') as DELAI, flr.FLR_ECHEANCE,
flr.FLR_PEI_CODE, flr.FLR_MODIFIER, "
+" case when COUNT(aer.AER_ERR_CODE) = 0 then 0 else 1 end as ERREUR,
flt.FLT_TFL_CODE, ('9') as REJETE, "
+" ('REELLE') as TYPEFLUX "
+" FROM SEP_FLUX_REEL flr, "
+" (((SEP_FLUX_TYPE as flt left outer join SEP_PARTENAIR_AMONT as pam on
flt.FLT_ID=pam.PAM_FLT_ID) "
+" left outer join SEP_PARTENAIR_AVAL as pav on
flt.FLT_ID=pav.PAV_FLT_ID) "
+" left outer join SEP_REF_PARTENAIRE as parm on
pam.PAM_PAR_ID=parm.PAR_ID) "
+" left outer join SEP_REF_PARTENAIRE as para on
pav.PAV_PAR_ID=para.PAR_ID, "
+" SEP_ACTION_REEL as acr left outer join SEP_ACTION_PARAM_RL as apm on
acr.ACR_ID=apm.APM_ACR_ID AND apm.APM_TYP_CODE='VAL', "
+" SEP_ACTION_REEL as acr_err left outer join SEP_AVOIR_ERREUR as aer on
acr_err.ACR_ID=aer.AER_ACR_ID, "
+" SEP_REF_PERIMETRE, "
+" SEP_DETENIR left outer join SEP_SELECTION_FLUX on
DET_UTI_ID=SLF_UTI_ID, "
+" SEP_REF_PERIODICITE, SEP_REF_GROUPEMENT,SEP_REF_ECHANGE ech,
SEP_REF_INITIATIVE "
+" WHERE "
+" flr.FLR_ID=acr.ACR_FLR_ID AND "
+" acr.ACR_ETA_CODE ilike ? AND "
+" flr.FLR_ID=acr_err.ACR_FLR_ID AND "
+" flr.FLR_FLT_ID=flt.FLT_ID AND flt.FLT_ECH_ID=ech.ECH_ID AND "
+" date_TRUNC('day',flr.FLR_ARRIVEE) BETWEEN ? AND ? AND flt.FLT_TFL_CODE
ilike ? AND "
+" acr.ACR_ATT_CODE ilike ? AND cast('OK' as varchar) ilike ? AND "
+" trim(to_char(DET_PER_ID,'99999999999999999999')) ilike ? AND "
+" trim(to_char(DET_UTI_ID,'99999999999999999999')) ilike ? AND "
+" DET_PER_ID = flt.FLT_PER_ID AND "
+" PER_ID = DET_PER_ID AND "
+" ( PER_SUPP != 1 OR PER_SUPP IS NULL ) AND "
+" trim(to_char(ech.ECH_ID,'999999999999999999999')) ilike ? AND
FLT_ECH_ID = ECH_ID AND "
+" ( trim(to_char(parm.PAR_GRP_ID,'99999999999999999')) ilike ? OR trim
(to_char(para.PAR_GRP_ID,'999999999999999999999')) ilike ? ) AND trim(to_char
(flt.FLT_ID,'99999999999999999999999')) ilike ? AND INI_CODE ilike ? AND "
+" INI_CODE = FLT_INI_CODE AND ( trim(to_char
(parm.PAR_ID,'99999999999999999999')) ilike ? OR trim(to_char
(para.PAR_ID,'99999999999999999999')) ilike ? ) AND "
+" PEI_CODE ilike ? AND PEI_CODE = FLT_PEI_CODE AND "
+" trim(to_char(flt.FLT_ECHEANCE,'999999999999999')) ilike ? "
+" AND "
+" date_TRUNC('day',flr.FLR_ARRIVEE)<= "
+" case when flr.FLR_PEI_CODE = 'MEN' then date(trim(to_char
(flr.FLR_ECHEANCE,'99999999999999999999')) || cast('/' as varchar) ||date_part
('month',flr.FLR_ARRIVEE)|| '/' ||date_part('year',flr.FLR_ARRIVEE)) else "
+" case when flr.FLR_PEI_CODE ='TRI' then date(trim(to_char
(flr.FLR_ECHEANCE,'99999999999999999999')) || cast('/' as varchar) ||date_part
('quarter',flr.FLR_ARRIVEE)*3-2|| '/' ||date_part('year',flr.FLR_ARRIVEE) )
else "
+" case when flr.FLR_PEI_CODE ='SEM' then date(trim
(to_char(flr.FLR_ECHEANCE,'99999999999999999999')) || cast('/' as varchar) ||
case when date_part('month',flr.FLR_ARRIVEE)>=6 then '06' else '01' end|| '/'
||date_part('year',flr.FLR_ARRIVEE)) else "
+" case when flr.FLR_PEI_CODE ='ANN' then date(trim
(to_char(flr.FLR_ECHEANCE,'99999999999999999999')) || cast('/12' as varchar)
||date_part('year',flr.FLR_ARRIVEE)) else "
+" now() "
+" end "
+" end "
+" end "
+" end "
+" GROUP BY FLT.FLT_ID, FLR.FLR_ID, ech.ECH_LIBELLE, flt.FLT_NOM,
flr.FLR_NOMREEL, parm.PAR_NOM, para.PAR_NOM, flr.FLR_ARRIVEE, "
+" acr.ACR_ATT_CODE, apm.APM_VALEUR, flr.FLR_ECHEANCE, flr.FLR_PEI_CODE,
flr.FLR_MODIFIER, flt.FLT_TFL_CODE, PER_ID, PER_LIBELLE "
+" HAVING case when COUNT(aer.AER_ERR_CODE) = 0 then '0' else '1' end
ilike ? "
+" UNION SELECT "
+" FLT.FLT_ID, PER_ID, PER_LIBELLE, FLR.FLR_ID,UPPER(ech.ECH_LIBELLE) as
ECH_LIBELLE, UPPER(flt.FLT_NOM) as FLT_NOM, "
+" UPPER(flr.FLR_NOMREEL) as FLR_NOMREEL, UPPER(parm.PAR_NOM) as parm_nom,
UPPER(para.PAR_NOM) as parv_nom, flr.FLR_ARRIVEE, "
+" acr.ACR_ATT_CODE, apm.APM_VALEUR, ('PAS_OK') as DELAI,
flr.FLR_ECHEANCE, flr.FLR_PEI_CODE, flr.FLR_MODIFIER, "
+" case when COUNT(aer.AER_ERR_CODE) = 0 then 0 else 1 end as ERREUR,
flt.FLT_TFL_CODE, ('9') as REJETE,('REELLE') as TYPEFLUX "
+" FROM SEP_FLUX_REEL flr, "
+" (((SEP_FLUX_TYPE as flt left outer join SEP_PARTENAIR_AMONT as pam on
flt.FLT_ID=pam.PAM_FLT_ID) "
+" left outer join SEP_REF_PARTENAIRE as parm on
pam.PAM_PAR_ID=parm.PAR_ID) "
+" left outer join SEP_PARTENAIR_AVAL as pav on
flt.FLT_ID=pav.PAV_FLT_ID) "
+" left outer join SEP_REF_PARTENAIRE as para on
pav.PAV_PAR_ID=para.PAR_ID, "
+" SEP_ACTION_REEL as acr left outer join SEP_ACTION_PARAM_RL as apm on
acr.ACR_ID=apm.APM_ACR_ID AND apm.APM_TYP_CODE='VAL', "
+" SEP_ACTION_REEL as acr_err left outer join SEP_AVOIR_ERREUR as aer on
acr_err.ACR_ID=aer.AER_ACR_ID, "
+" SEP_REF_PERIMETRE, "
+" SEP_DETENIR left outer join SEP_SELECTION_FLUX on
DET_UTI_ID=SLF_UTI_ID, "
+" SEP_REF_PERIODICITE, SEP_REF_GROUPEMENT,SEP_REF_ECHANGE ech,
SEP_REF_INITIATIVE "
+" WHERE "
+" acr.ACR_ETA_CODE ilike ? AND flr.FLR_ID=acr_err.ACR_FLR_ID AND "
+" date_TRUNC('day',flr.FLR_ARRIVEE) BETWEEN ? AND ? AND "
+" flt.FLT_TFL_CODE ilike ? AND "
+" acr.ACR_ATT_CODE ilike ? AND ('PAS_OK') ilike ? AND "
+" trim(to_char(DET_PER_ID,'99999999999999999999')) ilike ? AND "
+" trim(to_char(DET_UTI_ID,'99999999999999999999')) ilike ? AND "
+" flr.FLR_FLT_ID=flt.FLT_ID AND "
+" DET_PER_ID = flt.FLT_PER_ID AND "
+" flr.FLR_ID=acr.ACR_FLR_ID AND "
+" flt.FLT_ECH_ID=ech.ECH_ID AND "
+" PER_ID = DET_PER_ID AND "
+" ( PER_SUPP != 1 OR PER_SUPP IS NULL ) AND "
+" trim(to_char(ech.ECH_ID,'99999999999999999999')) ilike ? AND FLT_ECH_ID
= ECH_ID AND "
+" ( trim(to_char(parm.PAR_GRP_ID,'999999999999999999')) ilike ? OR trim
(to_char(para.PAR_GRP_ID,'99999999999999999999999999')) ilike ? ) AND trim
(to_char(flt.FLT_ID,'999999999999999999')) ilike ? AND "
+" INI_CODE ilike ? AND INI_CODE = FLT_INI_CODE AND ( trim(to_char
(parm.PAR_ID,'9999999999999999999999')) ilike ? OR trim(to_char
(para.PAR_ID,'99999999999999999999999')) ilike ? ) AND "
+" PEI_CODE ilike ? AND PEI_CODE = FLT_PEI_CODE AND trim(to_char
(flt.FLT_ECHEANCE,'999999999999999999999999999')) ilike ? AND "
+" date_TRUNC('day',flr.FLR_ARRIVEE)> "
+" case when flr.FLR_PEI_CODE = 'MEN' then date(trim(to_char
(flr.FLR_ECHEANCE,'99999999999999999999')) || cast('/' as varchar) ||date_part
('month',flr.FLR_ARRIVEE)|| '/' ||date_part('year',flr.FLR_ARRIVEE)) else "
+" case when flr.FLR_PEI_CODE ='TRI' then date(trim(to_char
(flr.FLR_ECHEANCE,'99999999999999999999')) || cast('/' as varchar) ||date_part
('quarter',flr.FLR_ARRIVEE)*3-2|| '/' ||date_part('year',flr.FLR_ARRIVEE) )
else "
+" case when flr.FLR_PEI_CODE ='SEM' then date(trim
(to_char(flr.FLR_ECHEANCE,'99999999999999999999')) || cast('/' as varchar) ||
case when date_part('month',flr.FLR_ARRIVEE)>=6 then '06' else '01' end|| '/'
||date_part('year',flr.FLR_ARRIVEE)) else "
+" case when flr.FLR_PEI_CODE ='ANN' then date(trim
(to_char(flr.FLR_ECHEANCE,'99999999999999999999')) || cast('/12' as varchar)
||date_part('year',flr.FLR_ARRIVEE)) else "
+" now() "
+" end "
+" end "
+" end "
+" end "
+" GROUP BY "
+" FLT.FLT_ID, FLR.FLR_ID, ech.ECH_LIBELLE, flt.FLT_NOM, flr.FLR_NOMREEL,
parm.PAR_NOM, para.PAR_NOM, flr.FLR_ARRIVEE, "
+" acr.ACR_ATT_CODE, apm.APM_VALEUR, flr.FLR_ECHEANCE, flr.FLR_PEI_CODE,
flr.FLR_MODIFIER, flt.FLT_TFL_CODE, PER_ID, PER_LIBELLE "
+" HAVING case when COUNT(aer.AER_ERR_CODE) = 0 then 0 else 1 end
ilike ? "
+" UNION SELECT flt.FLT_ID, PER_ID, PER_LIBELLE, FLR.FLR_ID, UPPER
(ech.ECH_LIBELLE) as ECH_LIBELLE, UPPER(flt.FLT_NOM) as FLT_NOM, "
+" ('') as FLR_NOMREEL, UPPER(parm.PAR_NOM) as parm_nom, UPPER
(para.PAR_NOM) as parv_nom, flr.FLR_ARRIVEE, "
+" ('MEA') as ACR_ATT_CODE, ('') as APM_VALEUR, ('PAS_OK') as DELAI,
flt.FLT_ECHEANCE as FLR_ECHEANCE, "
+" flt.FLT_PEI_CODE as FLR_PEI_CODE, (0) as FLR_MODIFIER, (0) as ERREUR,
flt.FLT_TFL_CODE, ('9') as REJETE,('TYPE') as TYPEFLUX "
+" FROM "
+" ((((SEP_FLUX_TYPE as flt left outer join SEP_FLUX_REEL as flr on
flt.FLT_ID = flr.FLR_FLT_ID) "
+" left outer join SEP_PARTENAIR_AMONT as pam on
flt.FLT_ID=pam.PAM_FLT_ID) "
+" left outer join SEP_REF_PARTENAIRE as parm on
pam.PAM_PAR_ID=parm.PAR_ID) "
+" left outer join SEP_PARTENAIR_AVAL as pav on
flt.FLT_ID=pav.PAV_FLT_ID) "
+" left outer join SEP_REF_PARTENAIRE as para on
pav.PAV_PAR_ID=para.PAR_ID, "
+" SEP_REF_ECHANGE ech, "
+" SEP_REF_PERIMETRE, SEP_DETENIR left outer join SEP_SELECTION_FLUX on
DET_UTI_ID=SLF_UTI_ID, "
+" SEP_REF_PERIODICITE, SEP_REF_GROUPEMENT, SEP_REF_INITIATIVE "
+" WHERE "
+" trim(to_char(DET_PER_ID,'9999999999999999999')) ilike ? AND "
+" trim(to_char(DET_UTI_ID,'9999999999999999999')) ilike ? AND "
+" DET_PER_ID = flt.FLT_PER_ID AND "
+" PER_ID = DET_PER_ID AND "
+" ('PAS_OK') ilike ? AND "
+" flt.FLT_ECH_ID=ech.ECH_ID AND "
+" flr.FLR_FLT_ID is null AND "
+" ('MEA') ilike ? AND "
+" ( PER_SUPP != 1 OR PER_SUPP IS NULL ) AND "
+" trim(to_char(ech.ECH_ID,'9999999999999999999')) ilike ? AND FLT_ECH_ID
= ECH_ID AND "
+" ( trim(to_char(parm.PAR_GRP_ID,'999999999999999999')) ilike ? OR trim
(to_char(para.PAR_GRP_ID,'99999999999999999999')) ilike ? ) AND trim(to_char
(flt.FLT_ID,'99999999999999999999999')) ilike ? AND flt.FLT_TFL_CODE ilike ?
AND "
+" INI_CODE ilike ? AND INI_CODE = FLT_INI_CODE AND ( trim(to_char
(parm.PAR_ID,'999999999999999999999999999')) ilike ? OR trim(to_char
(para.PAR_ID,'99999999999999999999999')) ilike ? ) AND "
+" PEI_CODE ilike ? AND PEI_CODE = FLT_PEI_CODE AND trim(to_char
(flt.FLT_ECHEANCE,'99999999999999999999999999999')) ilike ? AND "
+" date_TRUNC('day',now()) > "
+" case when flr.FLR_PEI_CODE = 'MEN' then date(trim(to_char
(flr.FLR_ECHEANCE,'9999999999999999999999999999999')) || cast('/' as varchar)
||date_part('month',now())|| '/' ||date_part('year',now())) else "
+" case when flt.FLT_PEI_CODE ='TRI' then date(trim(to_char
(flr.FLR_ECHEANCE,'9999999999999999999999999999999')) || cast('/' as varchar)
||date_part('quarter',now())*3-2|| '/' ||date_part('year',now())) else "
+" case when flt.FLT_PEI_CODE ='SEM' then date(trim(to_char
(flr.FLR_ECHEANCE,'9999999999999999999999999999999')) || cast('/' as varchar)
|| case when date_part('month',now())>=6 then '06' else '01' end || '/'
||date_part('year',now())) else "
+" case when flt.FLT_PEI_CODE ='ANN' then date(trim
(to_char(flr.FLR_ECHEANCE,'9999999999999999999999999999999')) || cast('/12' as
varchar) ||date_part('year',now())) else "
+" now() "
+" end "
+" end "
+" end "
+" end "
+" UNION SELECT "
+" FLT.FLT_ID, PER_ID, PER_LIBELLE, FLR.FLR_ID, UPPER(ech.ECH_LIBELLE) as
ECH_LIBELLE, UPPER(flt.FLT_NOM) as FLT_NOM, "
+" UPPER(flr.FLR_NOMREEL) as FLR_NOMREEL, UPPER(parm.PAR_NOM) as parm_nom,
UPPER(para.PAR_NOM) as parv_nom, flr.FLR_ARRIVEE, "
+" acr.ACR_ATT_CODE, apm.APM_VALEUR, ('OK') as DELAI, flr.FLR_ECHEANCE,
flr.FLR_PEI_CODE, flr.FLR_MODIFIER, case when COUNT((SELECT ACR_ETA_CODE FROM
SEP_ACTION_REEL WHERE ACR_ETA_CODE='ERR' AND ACR_FLR_ID=acr.ACR_FLR_ID GROUP BY
ACR_ETA_CODE)) = 0 then 0 else 1 end +case when COUNT(aer.AER_ERR_CODE) = 0
then 0 else 2 end as ERREUR, flt.FLT_TFL_CODE, case when COUNT((SELECT
ACR_ETA_CODE FROM SEP_ACTION_REEL WHERE ACR_ETA_CODE='REJ' AND
ACR_FLR_ID=acr.ACR_FLR_ID GROUP BY ACR_ETA_CODE)) = 0 then '0' else '1 end as
REJETE, 'REELLE' as TYPEFLUX "
+" FROM SEP_FLUX_REEL flr, "
+" (((SEP_FLUX_TYPE as flt left outer join SEP_PARTENAIR_AMONT as pam on
flt.FLT_ID=pam.PAM_FLT_ID) "
+" left outer join SEP_REF_PARTENAIRE as parm on
pam.PAM_PAR_ID=parm.PAR_ID) "
+" left outer join SEP_PARTENAIR_AVAL as pav on
flt.FLT_ID=pav.PAV_FLT_ID) "
+" left outer join SEP_REF_PARTENAIRE as para on
pav.PAV_PAR_ID=para.PAR_ID, "
+" SEP_ACTION_REEL as acr left outer join SEP_ACTION_PARAM_RL as apm on
acr.ACR_ID=apm.APM_ACR_ID AND apm.APM_TYP_CODE='VAL', "
+" SEP_ACTION_REEL as acr_err left outer join SEP_AVOIR_ERREUR as aer on
acr_err.ACR_ID=aer.AER_ACR_ID, "
+" SEP_REF_PERIMETRE, "
+" SEP_DETENIR left outer join SEP_SELECTION_FLUX on
DET_UTI_ID=SLF_UTI_ID, "
+" SEP_REF_PERIODICITE, SEP_REF_GROUPEMENT,SEP_REF_ECHANGE ech,
SEP_REF_INITIATIVE "
+" WHERE "
+" acr.ACR_ETA_CODE ilike ? AND "
+" flr.FLR_ID=acr.ACR_FLR_ID AND "
+" flr.FLR_FLT_ID=flt.FLT_ID AND flt.FLT_ECH_ID=ech.ECH_ID AND "
+" acr.ACR_NIVEAU = (SELECT MAX(ACR_NIVEAU) from SEP_ACTION_REEL WHERE
ACR_FLR_ID=acr.ACR_FLR_ID) AND acr.ACR_FIN is not null AND "
+" flr.FLR_ID=acr_err.ACR_FLR_ID AND "
+" date_TRUNC('day',flr.FLR_ARRIVEE) BETWEEN ? AND ? AND flt.FLT_TFL_CODE
ilike ? AND "
+" acr.ACR_ATT_CODE ilike ? AND ('OK') ilike ? AND "
+" trim(to_char(DET_PER_ID,'999999999999999999999999999')) ilike ? AND "
+" trim(to_char(DET_UTI_ID,'999999999999999999999999999')) ilike ? AND "
+" DET_PER_ID = flt.FLT_PER_ID AND "
+" PER_ID = DET_PER_ID AND "
+" ( PER_SUPP != 1 OR PER_SUPP IS NULL ) AND "
+" trim(to_char(ech.ECH_ID,'999999999999999999999999999')) ilike ? AND
FLT_ECH_ID = ECH_ID AND "
+" ( trim(to_char(parm.PAR_GRP_ID,'9999999999999999999999999')) ilike ? OR
trim(to_char(para.PAR_GRP_ID,'9999999999999999999999')) ilike ? ) AND trim
(to_char(flt.FLT_ID,'99999999999999999999999')) ilike ? AND "
+" INI_CODE ilike ? AND INI_CODE = FLT_INI_CODE AND "
+" ( trim(to_char(parm.PAR_ID,'9999999999999999999999999999999')) ilike ?
OR trim(to_char(para.PAR_ID,'9999999999999999999999999')) ilike ? ) AND
PEI_CODE ilike ? AND PEI_CODE = FLT_PEI_CODE AND "
+" trim(to_char(flt.FLT_ECHEANCE,'9999999999999999999999999999999'))
ilike ? AND "
+" date_TRUNC('day',flr.FLR_ARRIVEE)<= "
+" case when flr.FLR_PEI_CODE = 'MEN' then date(trim(to_char
(flr.FLR_ARRIVEE,'99999999999999999999')) || cast('/' as varchar) ||date_part
('month',flr.FLR_ARRIVEE)|| '/' ||date_part('year',flr.FLR_ARRIVEE)) else "
+" case when flr.FLR_PEI_CODE ='TRI' then date(trim(to_char
(flr.FLR_ARRIVEE,'99999999999999999999')) || cast('/' as varchar) ||date_part
('quarter',flr.FLR_ARRIVEE)*3-2|| '/' ||date_part('year',flr.FLR_ARRIVEE) )
else "
+" case when flr.FLR_PEI_CODE ='SEM' then date(trim
(to_char(flr.FLR_ARRIVEE,'99999999999999999999')) || cast('/' as varchar) ||
case when date_part('month',flr.FLR_ARRIVEE)>=6 then '06' else '01' end|| '/'
||date_part('year',flr.FLR_ARRIVEE)) else "
+" case when flr.FLR_PEI_CODE ='ANN' then date(trim
(to_char(flr.FLR_ARRIVEE,'99999999999999999999')) || cast('/12' as varchar)
||date_part('year',flr.FLR_ARRIVEE)) else "
+" now() "
+" end "
+" end "
+" end "
+" end "
+" GROUP BY "
+" FLT.FLT_ID, FLR.FLR_ID, ech.ECH_LIBELLE, flt.FLT_NOM, flr.FLR_NOMREEL,
parm.PAR_NOM, para.PAR_NOM, flr.FLR_ARRIVEE, "
+" acr.ACR_ATT_CODE, apm.APM_VALEUR, flr.FLR_ECHEANCE, flr.FLR_PEI_CODE,
flr.FLR_MODIFIER, flt.FLT_TFL_CODE, PER_ID, PER_LIBELLE "
+" HAVING case when COUNT((SELECT ACR_ETA_CODE FROM SEP_ACTION_REEL WHERE
ACR_ETA_CODE='ERR' AND ACR_FLR_ID=acr.ACR_FLR_ID GROUP BY ACR_ETA_CODE)) = 0
then 0 else 1 end +case when COUNT(aer.AER_ERR_CODE) = 0 then 0 else 2 end
ilike ? OR case when COUNT((SELECT ACR_ETA_CODE FROM SEP_ACTION_REEL WHERE
ACR_ETA_CODE='ERR' AND ACR_FLR_ID=acr.ACR_FLR_ID GROUP BY ACR_ETA_CODE)) = 0
then 0 else 1 end +case when COUNT(aer.AER_ERR_CODE) = 0 then 0 else 2 end
ilike ? OR case when COUNT((SELECT ACR_ETA_CODE FROM SEP_ACTION_REEL WHERE
ACR_ETA_CODE='ERR' AND ACR_FLR_ID=acr.ACR_FLR_ID GROUP BY ACR_ETA_CODE)) = 0
then 0 else 1 end +case when COUNT(aer.AER_ERR_CODE) = 0 then 0 else 2 end
ilike ? OR case when COUNT((SELECT ACR_ETA_CODE FROM SEP_ACTION_REEL WHERE
ACR_ETA_CODE='REJ' AND ACR_FLR_ID=acr.ACR_FLR_ID GROUP BY ACR_ETA_CODE)) = 0
then 0 else 3 end ilike ? "
+" AND ( case when COUNT((SELECT ACR_ETA_CODE FROM SEP_ACTION_REEL WHERE
ACR_ETA_CODE='ERR' AND ACR_FLR_ID=acr.ACR_FLR_ID GROUP BY ACR_ETA_CODE)) = 0
then 0 else 1 end + case when COUNT(aer.AER_ERR_CODE) = 0 then 0 else 2 end +
case when COUNT((SELECT ACR_ETA_CODE FROM SEP_ACTION_REEL WHERE
ACR_ETA_CODE='REJ' AND ACR_FLR_ID=acr.ACR_FLR_ID GROUP BY ACR_ETA_CODE)) =0
then 0 else 3 end + case when COUNT((SELECT ACR_ETA_CODE FROM SEP_ACTION_REEL
WHERE ACR_ETA_CODE='REJU' AND ACR_FLR_ID=acr.ACR_FLR_ID GROUP BY ACR_ETA_CODE))
=0 then 0 else 3 end ) LIKE ? "
+" UNION SELECT FLT.FLT_ID, PER_ID, PER_LIBELLE, FLR.FLR_ID, UPPER
(ech.ECH_LIBELLE) as ECH_LIBELLE, UPPER(flt.FLT_NOM) as FLT_NOM, "
+" UPPER(flr.FLR_NOMREEL) as FLR_NOMREEL, UPPER(parm.PAR_NOM) as parm_nom,
UPPER(para.PAR_NOM) as parv_nom, flr.FLR_ARRIVEE, "
+" acr.ACR_ATT_CODE, apm.APM_VALEUR, ('PAS_OK') as DELAI,
flr.FLR_ECHEANCE, flr.FLR_PEI_CODE, flr.FLR_MODIFIER, "
+" case when COUNT((SELECT ACR_ETA_CODE FROM SEP_ACTION_REEL WHERE
ACR_ETA_CODE='ERR' AND ACR_FLR_ID=acr.ACR_FLR_ID GROUP BY ACR_ETA_CODE)) = 0
then 0 else 1 end + case when COUNT(aer.AER_ERR_CODE) = 0 then 0 else 2 end as
ERREUR, flt.FLT_TFL_CODE, case when COUNT((SELECT ACR_ETA_CODE FROM
SEP_ACTION_REEL WHERE ACR_ETA_CODE='REJ' AND ACR_FLR_ID=acr.ACR_FLR_ID GROUP BY
ACR_ETA_CODE))= 0 then '0' else '1' end as REJETE, 'REELLE' as TYPEFLUX "
+" FROM SEP_FLUX_REEL flr, "
+" (((SEP_FLUX_TYPE as flt left outer join SEP_PARTENAIR_AMONT as pam on
flt.FLT_ID=pam.PAM_FLT_ID) "
+" left outer join SEP_REF_PARTENAIRE as parm on
pam.PAM_PAR_ID=parm.PAR_ID) "
+" left outer join SEP_PARTENAIR_AVAL as pav on
flt.FLT_ID=pav.PAV_FLT_ID) "
+" left outer join SEP_REF_PARTENAIRE as para on
pav.PAV_PAR_ID=para.PAR_ID, "
+" SEP_ACTION_REEL as acr left outer join SEP_ACTION_PARAM_RL as apm on
acr.ACR_ID=apm.APM_ACR_ID AND apm.APM_TYP_CODE='VAL', "
+" SEP_ACTION_REEL as acr_err left outer join SEP_AVOIR_ERREUR as aer on
acr_err.ACR_ID=aer.AER_ACR_ID, "
+" SEP_REF_PERIMETRE, "
+" SEP_DETENIR left outer join SEP_SELECTION_FLUX on
DET_UTI_ID=SLF_UTI_ID, "
+" SEP_REF_PERIODICITE, SEP_REF_GROUPEMENT,SEP_REF_ECHANGE ech,
SEP_REF_INITIATIVE "
+" WHERE "
+" acr.ACR_ETA_CODE ilike ? AND "
+" flr.FLR_ID=acr.ACR_FLR_ID AND "
+" flr.FLR_FLT_ID=flt.FLT_ID AND flt.FLT_ECH_ID=ech.ECH_ID AND "
+" acr.ACR_NIVEAU = (SELECT MAX(ACR_NIVEAU) from SEP_ACTION_REEL WHERE
ACR_FLR_ID=acr.ACR_FLR_ID) AND acr.ACR_FIN is not null AND "
+" flr.FLR_ID=acr_err.ACR_FLR_ID AND "
+" date_TRUNC('day',flr.FLR_ARRIVEE) BETWEEN ? AND ? AND acr.ACR_ATT_CODE
ilike ? AND "
+" ('PAS_OK') ilike ? AND "
+" trim(to_char(DET_PER_ID,'99999999999999999999999999')) ilike ? AND "
+" trim(to_char(DET_UTI_ID,'99999999999999999999999999')) ilike ? AND "
+" DET_PER_ID = flt.FLT_PER_ID AND "
+" PER_ID = DET_PER_ID AND "
+" ( PER_SUPP != 1 OR PER_SUPP IS NULL ) AND "
+" trim(to_char(ech.ECH_ID,'999999999999999999999999999')) ilike ? AND
FLT_ECH_ID = ECH_ID AND "
+" ( trim(to_char(parm.PAR_GRP_ID,'99999999999999999999999')) ilike ? OR
trim(to_char(para.PAR_GRP_ID,'99999999999999999999')) ilike ? ) AND trim(to_char
(flt.FLT_ID,'9999999999999999999999999')) ilike ? AND "
+" INI_CODE ilike ? AND INI_CODE = FLT_INI_CODE AND ( trim(to_char
(parm.PAR_ID,'9999999999999999999999999')) ilike ? OR trim(to_char
(para.PAR_ID,'99999999999999999999')) ilike ? ) AND "
+" PEI_CODE ilike ? AND PEI_CODE = FLT_PEI_CODE AND trim(to_char
(flt.FLT_ECHEANCE,'9999999999999999999999999')) ilike ? AND "
+" date_TRUNC('day',flr.FLR_ARRIVEE)> "
+" case when flr.FLR_PEI_CODE = 'MEN' then date(trim(to_char
(flr.FLR_ARRIVEE,'99999999999999999999')) || cast('/' as varchar) ||date_part
('month',flr.FLR_ARRIVEE)|| '/' ||date_part('year',flr.FLR_ARRIVEE)) else "
+" case when flr.FLR_PEI_CODE ='TRI' then date(trim(to_char
(flr.FLR_ARRIVEE,'99999999999999999999')) || cast('/' as varchar) ||date_part
('quarter',flr.FLR_ARRIVEE)*3-2|| '/' ||date_part('year',flr.FLR_ARRIVEE) )
else "
+" case when flr.FLR_PEI_CODE ='SEM' then date(trim
(to_char(flr.FLR_ARRIVEE,'99999999999999999999')) || cast('/' as varchar) ||
case when date_part('month',flr.FLR_ARRIVEE)>=6 then '06' else '01' end|| '/'
||date_part('year',flr.FLR_ARRIVEE)) else "
+" case when flr.FLR_PEI_CODE ='ANN' then date(trim
(to_char(flr.FLR_ARRIVEE,'99999999999999999999')) || cast('/12' as varchar)
||date_part('year',flr.FLR_ARRIVEE)) else "
+" now() "
+" end "
+" end "
+" end "
+" end "
+" GROUP BY "
+" FLT.FLT_ID, FLR.FLR_ID, ech.ECH_LIBELLE, flt.FLT_NOM, flr.FLR_NOMREEL,
parm.PAR_NOM, para.PAR_NOM, flr.FLR_ARRIVEE, "
+" acr.ACR_ATT_CODE, apm.APM_VALEUR, flr.FLR_ECHEANCE, flr.FLR_PEI_CODE,
flr.FLR_MODIFIER, flt.FLT_TFL_CODE, PER_ID, PER_LIBELLE "
+" HAVING "
+" case when COUNT((SELECT ACR_ETA_CODE FROM SEP_ACTION_REEL WHERE
ACR_ETA_CODE='ERR' AND ACR_FLR_ID=acr.ACR_FLR_ID GROUP BY ACR_ETA_CODE)) = 0
then 0 else 1 end + case when COUNT(aer.AER_ERR_CODE)=0 then 0 else 2 end
ilike ? OR case when COUNT((SELECT ACR_ETA_CODE FROM SEP_ACTION_REEL WHERE
ACR_ETA_CODE='ERR' AND ACR_FLR_ID=acr.ACR_FLR_ID GROUP BY ACR_ETA_CODE)) = 0
then 0 else 1 end + case when COUNT(aer.AER_ERR_CODE) = 0 then 0 else 2 end
ilike ? OR case when COUNT((SELECT ACR_ETA_CODE FROM SEP_ACTION_REEL WHERE
ACR_ETA_CODE='ERR' AND ACR_FLR_ID=acr.ACR_FLR_ID GROUP BY ACR_ETA_CODE))=0 then
0 else 1 end +case when COUNT(aer.AER_ERR_CODE) = 0 then 0 else 2 end ilike ?
OR case when COUNT((SELECT ACR_ETA_CODE FROM SEP_ACTION_REEL WHERE
ACR_ETA_CODE='REJ' AND ACR_FLR_ID=acr.ACR_FLR_ID GROUP BY ACR_ETA_CODE)) = 0
then 0 else 3 end ilike ? "
+" AND (case when COUNT((SELECT ACR_ETA_CODE FROM SEP_ACTION_REEL WHERE
ACR_ETA_CODE='ERR' AND ACR_FLR_ID=acr.ACR_FLR_ID GROUP BY ACR_ETA_CODE)) = 0
then 0 else 1 end + case when COUNT(aer.AER_ERR_CODE)=0 then 0 else 2 end
+ case when COUNT((SELECT ACR_ETA_CODE FROM SEP_ACTION_REEL WHERE
ACR_ETA_CODE='REJ' AND ACR_FLR_ID=acr.ACR_FLR_ID GROUP BY ACR_ETA_CODE)) = 0
then 0 else 3 end + case when COUNT((SELECT ACR_ETA_CODE FROM SEP_ACTION_REEL
WHERE ACR_ETA_CODE='REJU' AND ACR_FLR_ID=acr.ACR_FLR_ID GROUP BY ACR_ETA_CODE))
= 0 then 0 else 3 end ) LIKE ? " ;
To create the table :
BEGIN TRANSACTION;
CREATE TABLE "sep_ref_periodicite" (
"pei_code" varchar(4) NOT NULL,
"pei_libelle" varchar(255) NOT NULL,
"pei_ident" varchar(9) NOT NULL,
"pei_maj" datetime NOT NULL,
"pei_supp" numeric(1),
PRIMARY KEY ("pei_code")
);
CREATE TABLE "sep_ref_type_parcours" (
"tpa_code" varchar(4) NOT NULL,
"tpa_libelle" varchar(255) NOT NULL,
"tpa_ident" varchar(9) NOT NULL,
"tpa_supp" numeric(1),
"tpa_maj" datetime NOT NULL,
PRIMARY KEY ("tpa_code")
);
CREATE TABLE "sep_ref_role" (
"rol_code" varchar(4) NOT NULL,
"rol_libelle" varchar(30) NOT NULL,
"rol_supp" numeric(1),
"rol_maj" datetime NOT NULL,
"rol_ident" varchar(9) NOT NULL,
PRIMARY KEY ("rol_code")
);
CREATE TABLE "sep_maj_extrac" (
"maj_id" numeric(6) NOT NULL,
"maj_organisme" numeric(3) NOT NULL,
"maj_maj" datetime NOT NULL,
PRIMARY KEY ("maj_id")
);
CREATE TABLE "sep_champ_mega" (
"chm_id" numeric(6) NOT NULL,
"chm_table" varchar(50),
"chm_nom" varchar(50) NOT NULL,
"chm_type" varchar(50),
"chm_longueur" numeric(4),
"chm_information" varchar(255),
"chm_maj" datetime NOT NULL,
PRIMARY KEY ("chm_id")
);
CREATE TABLE "sep_ref_nature" (
"nat_id" numeric(6) NOT NULL,
"nat_libelle" varchar(255) NOT NULL,
"nat_maj" datetime NOT NULL,
"nat_supp" numeric(1),
"nat_ident" varchar(9) NOT NULL,
PRIMARY KEY ("nat_id")
);
CREATE TABLE "sep_ref_system_info" (
"sys_id" numeric(6) NOT NULL,
"sys_libelle" varchar(30) NOT NULL,
"sys_supp" numeric(1),
"sys_maj" datetime NOT NULL,
"sys_ident" varchar(9) NOT NULL,
PRIMARY KEY ("sys_id")
);
CREATE TABLE "sep_ref_echange" (
"ech_id" numeric(6) NOT NULL,
"ech_libelle" varchar(255) NOT NULL,
"ech_ident" varchar(9) NOT NULL,
"ech_maj" datetime NOT NULL,
"ech_supp" numeric(1),
PRIMARY KEY ("ech_id")
);
CREATE TABLE "sep_ref_origine" (
"ori_id" numeric(6) NOT NULL,
"ori_libelle" varchar(255) NOT NULL,
"ori_ident" varchar(9) NOT NULL,
"ori_maj" datetime NOT NULL,
"ori_supp" numeric(1),
PRIMARY KEY ("ori_id")
);
CREATE TABLE "sep_ref_format" (
"for_id" numeric(6) NOT NULL,
"for_valeur" varchar(255) NOT NULL,
"for_maj" datetime NOT NULL,
"for_supp" numeric(1),
"for_ident" varchar(9) NOT NULL,
PRIMARY KEY ("for_id")
);
CREATE TABLE "sep_type_param" (
"typ_code" varchar(4) NOT NULL,
"typ_libelle" varchar(255) NOT NULL,
"typ_maj" datetime NOT NULL,
"typ_ident" varchar(9) NOT NULL,
PRIMARY KEY ("typ_code")
);
CREATE TABLE "sep_ctrl_type" (
"ctt_id" numeric(6) NOT NULL,
"ctt_libelle" varchar(255) NOT NULL,
"ctt_nomclasse" varchar(255) NOT NULL,
"ctt_bloque" numeric(1) NOT NULL,
"ctt_description" varchar(2000),
"ctt_flag" numeric(1) NOT NULL,
"ctt_maj" datetime NOT NULL,
"ctt_ident" varchar(9) NOT NULL,
PRIMARY KEY ("ctt_id")
);
CREATE TABLE "sep_grappe" (
"gra_id" numeric(6) NOT NULL,
"gra_flt_id" numeric(6),
"gra_gra_id" numeric(6) NOT NULL,
"gra_nb_max" numeric(6),
"gra_find_char" varchar(255),
"gra_find_separateur" varchar(4),
"gra_pasarticlesuivant" numeric(1),
"gra_find" numeric(1),
"gra_find_pos" numeric(5),
"gra_numero_ordre" numeric(6),
"gra_art_id_parent" numeric(6),
"gra_art_id_fille" numeric(6),
PRIMARY KEY ("gra_id","gra_gra_id")
);
CREATE TABLE "sep_ref_type_erreur" (
"ter_code" varchar(4) NOT NULL,
"ter_libelle" varchar(255) NOT NULL,
"ter_ident" varchar(9) NOT NULL,
"ter_maj" datetime NOT NULL,
PRIMARY KEY ("ter_code")
);
CREATE TABLE "sep_ref_groupement" (
"grp_id" numeric(6) NOT NULL,
"grp_libelle" varchar(255) NOT NULL,
"grp_supp" numeric(1),
"grp_maj" datetime NOT NULL,
"grp_ident" varchar(9) NOT NULL,
PRIMARY KEY ("grp_id")
);
CREATE TABLE "sep_ref_type_champ" (
"tch_id" numeric(6) NOT NULL,
"tch_libelle" varchar(255) NOT NULL,
"tch_ident" varchar(9) NOT NULL,
"tch_maj" datetime NOT NULL,
PRIMARY KEY ("tch_id")
);
CREATE TABLE "sep_ref_initiative" (
"ini_code" varchar(4) NOT NULL,
"ini_libelle" varchar(255) NOT NULL,
"ini_supp" numeric(1),
"ini_ident" varchar(9) NOT NULL,
"ini_maj" datetime NOT NULL,
PRIMARY KEY ("ini_code")
);
CREATE TABLE "sep_utilisateur" (
"uti_id" numeric(6) NOT NULL,
"uti_urssaf" varchar(3) NOT NULL,
"uti_nagent" varchar(6) NOT NULL,
"uti_nom" varchar(30) NOT NULL,
"uti_prenom" varchar(20),
"uti_nomurssaf" varchar(30) NOT NULL,
"uti_passe" varchar(10) NOT NULL,
"uti_supp" numeric(1),
"uti_maj" datetime NOT NULL,
"uti_ident" varchar(9) NOT NULL,
PRIMARY KEY ("uti_id")
);
CREATE TABLE "sep_partparam" (
"ppr_id" numeric(6) NOT NULL,
"ppr_libelle" varchar(255) NOT NULL,
"ppr_supp" numeric(1),
"ppr_maj" datetime NOT NULL,
"ppr_ident" varchar(9) NOT NULL,
PRIMARY KEY ("ppr_id")
);
CREATE TABLE "sep_ref_autoriser" (
"aut_code" varchar(4) NOT NULL,
"aut_ident" varchar(9) NOT NULL,
"aut_maj" datetime NOT NULL,
"aut_supp" numeric(1),
"aut_libelle" varchar(30) NOT NULL,
PRIMARY KEY ("aut_code")
);
CREATE TABLE "sep_ref_action_type" (
"att_code" varchar(4) NOT NULL,
"att_ordre" numeric(2) NOT NULL,
"att_libelle" varchar(255) NOT NULL,
"att_affichage" numeric(1) NOT NULL,
"att_obligatoire" numeric(1) NOT NULL,
"att_nomclasse" varchar(255) NOT NULL,
"att_maj" datetime NOT NULL,
"att_ident" varchar(9) NOT NULL,
PRIMARY KEY ("att_code")
);
CREATE TABLE "sep_ref_module" (
"mod_code" varchar(4) NOT NULL,
"mod_libelle" varchar(30) NOT NULL,
"mod_supp" numeric(1),
"mod_maj" datetime NOT NULL,
"mod_ident" varchar(9) NOT NULL,
PRIMARY KEY ("mod_code")
);
CREATE TABLE "sep_ref_type_contact" (
"tco_code" varchar(4) NOT NULL,
"tco_libelle" varchar(255) NOT NULL,
"tco_supp" numeric(1),
"tco_maj" datetime NOT NULL,
"tco_ident" varchar(9) NOT NULL,
PRIMARY KEY ("tco_code")
);
CREATE TABLE "sep_ref_type_flux" (
"tfl_code" varchar(4) NOT NULL,
"tfl_libelle" varchar(255) NOT NULL,
"tfl_ident" varchar(9) NOT NULL,
"tfl_maj" datetime NOT NULL,
"tfl_supp" numeric(1),
PRIMARY KEY ("tfl_code")
);
CREATE TABLE "sep_ref_etat" (
"eta_code" varchar(4) NOT NULL,
"eta_libelle" varchar(255) NOT NULL,
"eta_maj" datetime NOT NULL,
"eta_ident" varchar(9) NOT NULL,
PRIMARY KEY ("eta_code")
);
CREATE TABLE "sep_ref_support" (
"sup_id" numeric(6) NOT NULL,
"sup_libelle" varchar(255) NOT NULL,
"sup_ident" varchar(9) NOT NULL,
"sup_maj" datetime NOT NULL,
"sup_supp" numeric(1),
PRIMARY KEY ("sup_id")
);
CREATE TABLE "sep_ref_parametre" (
"pag_valeur" varchar(255) NOT NULL,
"pag_ident" varchar(9) NOT NULL,
"pag_maj" datetime NOT NULL,
"pag_code" varchar(4) NOT NULL,
"pag_uti_urssaf" varchar(3)
);
CREATE TABLE "sep_ref_droit" (
"dro_code" varchar(4) NOT NULL,
"dro_libelle" varchar(255) NOT NULL,
"dro_supp" numeric(1),
"dro_maj" datetime NOT NULL,
"dro_ident" varchar(9) NOT NULL,
PRIMARY KEY ("dro_code")
);
CREATE TABLE "sep_nat_cotisant" (
"nb1_organisme" numeric(3) NOT NULL,
"nb1_cotisant" numeric(15) NOT NULL,
"nb1_maj" datetime NOT NULL
);
CREATE UNIQUE INDEX "nb1_index" ON "sep_nat_cotisant"
("nb1_cotisant","nb1_organisme");
CREATE TABLE "sep_sas" (
"sas_code" varchar(4) NOT NULL,
"sas_libelle" varchar(255) NOT NULL,
"sas_ident" varchar(9) NOT NULL,
"sas_maj" datetime NOT NULL,
"sas_supp" numeric(1),
PRIMARY KEY ("sas_code")
);
CREATE TABLE "sep_autoriser" (
"aur_rol_code" varchar(4) NOT NULL,
"aur_mod_code" varchar(4) NOT NULL,
"aur_aut_code" varchar(4) NOT NULL,
PRIMARY KEY ("aur_rol_code","aur_mod_code","aur_aut_code") --,
--CONSTRAINT fk_sep_autoriser_aur_aut_code FOREIGN KEY (aur_aut_code)
REFERENCES () ON DELETE NO ACTION NOT DEFERRABLE INITIALLY IMMEDIATE,
--CONSTRAINT fk_sep_autoriser_aur_mod_code FOREIGN KEY () REFERENCES
() ON DELETE NO ACTION NOT DEFERRABLE INITIALLY IMMEDIATE,
--CONSTRAINT fk_sep_autoriser_aur_rol_code FOREIGN KEY () REFERENCES
() ON DELETE NO ACTION NOT DEFERRABLE INITIALLY IMMEDIATE
);
CREATE INDEX "idx_sep_autoriser_1" ON "sep_autoriser" ("aur_rol_code");
CREATE INDEX "idx_sep_autoriser_2" ON "sep_autoriser" ("aur_mod_code");
CREATE INDEX "idx_sep_autoriser_3" ON "sep_autoriser" ("aur_aut_code");
CREATE TABLE "sep_controle_reel" (
"ctr_acr_id" numeric(6) NOT NULL,
"ctr_ctt_id" numeric(6) NOT NULL,
PRIMARY KEY ("ctr_acr_id","ctr_ctt_id") --,
--CONSTRAINT fk_sep_controle_rl_ctr_acr_id FOREIGN KEY (ctr_acr_id)
REFERENCES () ON DELETE NO ACTION NOT DEFERRABLE INITIALLY IMMEDIATE,
--CONSTRAINT fk_sep_controle_rl_ctr_ctt_id FOREIGN KEY () REFERENCES
() ON DELETE NO ACTION NOT DEFERRABLE INITIALLY IMMEDIATE
);
CREATE INDEX "idx_sep_controle_reel_1" ON "sep_controle_reel" ("ctr_acr_id");
CREATE INDEX "idx_sep_controle_reel_2" ON "sep_controle_reel" ("ctr_ctt_id");
CREATE TABLE "sep_entrer" (
"enr_prt_id" numeric(6) NOT NULL,
"enr_flt_id" numeric(6) NOT NULL,
PRIMARY KEY ("enr_prt_id","enr_flt_id")--,
--CONSTRAINT fk_sep_entrer_enr_flt_id FOREIGN KEY (enr_flt_id)
REFERENCES () ON DELETE NO ACTION NOT DEFERRABLE INITIALLY IMMEDIATE,
--CONSTRAINT fk_sep_entrer_enr_prt_id FOREIGN KEY () REFERENCES () ON
DELETE NO ACTION NOT DEFERRABLE INITIALLY IMMEDIATE
);
CREATE INDEX "idx_sep_entrer_1" ON "sep_entrer" ("enr_prt_id");
CREATE INDEX "idx_sep_entrer_2" ON "sep_entrer" ("enr_flt_id");
CREATE TABLE "sep_entree" (
"ene_trs_id" numeric(6) NOT NULL,
"ene_flt_id" numeric(6) NOT NULL,
PRIMARY KEY ("ene_trs_id","ene_flt_id")--,
--CONSTRAINT fk_sep_entree_ene_flt_id FOREIGN KEY (ene_flt_id)
REFERENCES (sep_entrer) ON DELETE NO ACTION NOT DEFERRABLE INITIALLY
IMMEDIATE --,
--CONSTRAINT fk_sep_entree_ene_trs_id FOREIGN KEY () REFERENCES () ON
DELETE NO ACTION NOT DEFERRABLE INITIALLY IMMEDIATE
);
CREATE INDEX "idx_sep_entree_1" ON "sep_entree" ("ene_trs_id");
CREATE INDEX "idx_sep_entree_2" ON "sep_entree" ("ene_flt_id");
CREATE TABLE "sep_possible_ctr" (
"pos_ctt_id" numeric(6) NOT NULL,
"pos_chm_id" numeric(6) NOT NULL,
PRIMARY KEY ("pos_ctt_id","pos_chm_id")--,
--CONSTRAINT fk_sep_possible_ctr_pos_chm_id FOREIGN KEY (pos_chm_id)
REFERENCES () ON DELETE NO ACTION NOT DEFERRABLE INITIALLY IMMEDIATE,
--CONSTRAINT fk_sep_possible_ctr_pos_ctt_id FOREIGN KEY () REFERENCES
() ON DELETE NO ACTION NOT DEFERRABLE INITIALLY IMMEDIATE
);
CREATE INDEX "idx_sep_possible_ctr_1" ON "sep_possible_ctr" ("pos_ctt_id");
CREATE INDEX "idx_sep_possible_ctr_2" ON "sep_possible_ctr" ("pos_chm_id");
CREATE TABLE "sep_transformation" (
"trs_id" numeric(6) NOT NULL,
PRIMARY KEY ("trs_id")
);
CREATE TABLE "sep_sortie" (
"sor_trs_id" numeric(6) NOT NULL,
"sor_flt_id" numeric(6) NOT NULL,
PRIMARY KEY ("sor_trs_id","sor_flt_id") --,
--CONSTRAINT fk_sep_sortie_sor_flt_id FOREIGN KEY (sor_flt_id)
REFERENCES () ON DELETE NO ACTION NOT DEFERRABLE INITIALLY IMMEDIATE,
--CONSTRAINT fk_sep_sortie_sor_trs_id FOREIGN KEY () REFERENCES () ON
DELETE NO ACTION NOT DEFERRABLE INITIALLY IMMEDIATE
);
CREATE INDEX "idx_sep_sortie_1" ON "sep_sortie" ("sor_trs_id");
CREATE INDEX "idx_sep_sortie_2" ON "sep_sortie" ("sor_flt_id");
CREATE TABLE "sep_appartenir" (
"apa_cha_id" numeric(6) NOT NULL,
"apa_art_id" numeric(6) NOT NULL,
PRIMARY KEY ("apa_cha_id","apa_art_id")--,
--CONSTRAINT fk_sep_appartenir FOREIGN KEY (apa_art_id) REFERENCES ()
ON DELETE NO ACTION NOT DEFERRABLE INITIALLY IMMEDIATE,
--CONSTRAINT fk_sep_appartenir_apa_cha_id FOREIGN KEY () REFERENCES ()
ON DELETE NO ACTION NOT DEFERRABLE INITIALLY IMMEDIATE
);
CREATE INDEX "idx_sep_appartenir_2" ON "sep_appartenir" ("apa_cha_id");
CREATE TABLE "sep_partenair_aval" (
"pav_sas_code" varchar(4) NOT NULL,
"pav_par_id" numeric(6) NOT NULL,
"pav_flt_id" numeric(6) NOT NULL,
"pav_serveur" varchar(255),
"pav_repertoire" varchar(255),
"pav_nomphysique" varchar(255),
PRIMARY KEY ("pav_sas_code","pav_par_id","pav_flt_id")--,
--CONSTRAINT fk_partenair_aval_pav_flt_id FOREIGN KEY (pav_flt_id)
REFERENCES () ON DELETE NO ACTION NOT DEFERRABLE INITIALLY IMMEDIATE,
--CONSTRAINT fk_partenair_aval_pav_par_id FOREIGN KEY () REFERENCES ()
ON DELETE NO ACTION NOT DEFERRABLE INITIALLY IMMEDIATE,
--CONSTRAINT fk_partenair_aval_pav_sas_code FOREIGN KEY () REFERENCES
() ON DELETE NO ACTION NOT DEFERRABLE INITIALLY IMMEDIATE
);
CREATE INDEX "idx_sep_partenair_aval_2" ON "sep_partenair_aval" ("pav_par_id");
CREATE INDEX "idx_sep_partenair_aval_3" ON "sep_partenair_aval" ("pav_flt_id");
CREATE INDEX "idx_sep_partenair_aval_1" ON "sep_partenair_aval"
("pav_sas_code");
CREATE TABLE "sep_action_param" (
"acp_etp_id" numeric(6) NOT NULL,
"acp_typ_code" varchar(4) NOT NULL,
"acp_valeur" varchar(2000),
PRIMARY KEY ("acp_etp_id","acp_typ_code")--,
--CONSTRAINT fk_action_param_acp_typ_code FOREIGN KEY (acp_typ_code)
REFERENCES () ON DELETE NO ACTION NOT DEFERRABLE INITIALLY IMMEDIATE,
--CONSTRAINT fk_sep_action_param_acp_etp_id FOREIGN KEY () REFERENCES
() ON DELETE NO ACTION NOT DEFERRABLE INITIALLY IMMEDIATE
);
CREATE INDEX "idx_sep_action_param_1" ON "sep_action_param" ("acp_etp_id");
CREATE INDEX "idx_sep_action_param_2" ON "sep_action_param" ("acp_typ_code");
CREATE TABLE "sep_ref_partenaire" (
"par_id" numeric(6) NOT NULL,
"par_sys_id" numeric(6) NOT NULL,
"par_grp_id" numeric(6) NOT NULL,
"par_ppr_id" numeric(6),
"par_nom" varchar(30) NOT NULL,
"par_email" varchar(255),
"par_supp" numeric(1),
"par_maj" datetime NOT NULL,
"par_ident" varchar(9) NOT NULL,
PRIMARY KEY ("par_id")--,
--CONSTRAINT fk_avoir_partparam_par_ppr_id FOREIGN KEY (par_ppr_id)
REFERENCES () ON DELETE NO ACTION NOT DEFERRABLE INITIALLY IMMEDIATE,
--CONSTRAINT fk_avoir_si_par_sys_id FOREIGN KEY () REFERENCES () ON
DELETE NO ACTION NOT DEFERRABLE INITIALLY IMMEDIATE,
--CONSTRAINT fk_avoir_tutelle_par_grp_id FOREIGN KEY () REFERENCES ()
ON DELETE NO ACTION NOT DEFERRABLE INITIALLY IMMEDIATE
);
CREATE INDEX "idx_avoir_si" ON "sep_ref_partenaire" ("par_sys_id");
CREATE INDEX "idx_avoir_tutelle" ON "sep_ref_partenaire" ("par_grp_id");
CREATE INDEX "idx_avoir_partparam" ON "sep_ref_partenaire" ("par_ppr_id");
CREATE TABLE "sep_flux_reel" (
"flr_id" numeric(10) NOT NULL,
"flr_pei_code" varchar(4) NOT NULL,
"flr_flt_id" numeric(6) NOT NULL,
"flr_nomreel" varchar(255),
"flr_nomxml" varchar(255),
"flr_pseudoxml" varchar(255),
"flr_taille" numeric(10),
"flr_nbrenreg" numeric(7),
"flr_arrivee" datetime NOT NULL,
"flr_echeance" numeric(3),
"flr_modifier" numeric(1),
PRIMARY KEY ("flr_id")--,
--CONSTRAINT fk_perodicite_rl_flr_pei_code FOREIGN KEY (flr_pei_code)
REFERENCES () ON DELETE NO ACTION NOT DEFERRABLE INITIALLY IMMEDIATE,
--CONSTRAINT fk_referencer_flr_flt_id FOREIGN KEY () REFERENCES () ON
DELETE NO ACTION NOT DEFERRABLE INITIALLY IMMEDIATE
);
CREATE INDEX "idx_referencer" ON "sep_flux_reel" ("flr_flt_id");
CREATE INDEX "idx_perodicite_reelle" ON "sep_flux_reel" ("flr_pei_code");
CREATE TABLE "sep_contact_info" (
"cif_con_id" numeric(6) NOT NULL,
"cif_flt_id" numeric(6) NOT NULL,
"cif_ar" numeric(1),
"cif_info" numeric(1),
"cif_rejet" numeric(1),
"cif_echeance" numeric(1),
"cif_message_ech" varchar(2000),
"cif_message_rej" varchar(2000),
PRIMARY KEY ("cif_con_id","cif_flt_id")--,
--CONSTRAINT fk_sep_contact_info_cif_con_id FOREIGN KEY (cif_con_id)
REFERENCES () ON DELETE NO ACTION NOT DEFERRABLE INITIALLY IMMEDIATE,
--CONSTRAINT fk_sep_contact_info_cif_flt_id FOREIGN KEY () REFERENCES
() ON DELETE NO ACTION NOT DEFERRABLE INITIALLY IMMEDIATE
);
CREATE INDEX "idx_sep_contact_info_2" ON "sep_contact_info" ("cif_flt_id");
CREATE INDEX "idx_sep_contact_info_1" ON "sep_contact_info" ("cif_con_id");
CREATE TABLE "sep_partenair_amont" (
"pam_sas_code" varchar(4) NOT NULL,
"pam_par_id" numeric(6) NOT NULL,
"pam_flt_id" numeric(6) NOT NULL,
"pam_serveur" varchar(255),
"pam_repertoire" varchar(255),
"pam_repertoirerejet" varchar(255),
"pam_nomphysiquerejet" varchar(255),
"pam_nomphysique" varchar(255),
"pam_serveurrejet" varchar(255),
PRIMARY KEY ("pam_sas_code","pam_par_id","pam_flt_id")--,
--CONSTRAINT fk_partenair_amont_pam_flt_id FOREIGN KEY (pam_flt_id)
REFERENCES () ON DELETE NO ACTION NOT DEFERRABLE INITIALLY IMMEDIATE,
--CONSTRAINT fk_partenair_amont_pam_par_id FOREIGN KEY () REFERENCES
() ON DELETE NO ACTION NOT DEFERRABLE INITIALLY IMMEDIATE,
--CONSTRAINT fk_partenair_amont_pam_sas_cod FOREIGN KEY () REFERENCES
() ON DELETE NO ACTION NOT DEFERRABLE INITIALLY IMMEDIATE
);
CREATE INDEX "idx_sep_partenair_amont_1" ON "sep_partenair_amont"
("pam_sas_code");
CREATE INDEX "idx_sep_partenair_amont_2" ON "sep_partenair_amont"
("pam_par_id");
CREATE INDEX "idx_sep_partenair_amont_3" ON "sep_partenair_amont"
("pam_flt_id");
CREATE TABLE "sep_ref_contact" (
"con_id" numeric(6) NOT NULL,
"con_tco_code" varchar(4),
"con_nom" varchar(30) NOT NULL,
"con_prenom" varchar(20),
"con_organisme" varchar(25),
"con_telephone" varchar(14),
"con_email" varchar(255),
"con_supp" numeric(1),
"con_maj" datetime NOT NULL,
"con_externe" numeric(1),
"con_ident" varchar(9) NOT NULL,
"con_uti_id" numeric(6),
PRIMARY KEY ("con_id")--,
--CONSTRAINT fk_avoir_contact_con_tco_code FOREIGN KEY (con_tco_code)
REFERENCES () ON DELETE NO ACTION NOT DEFERRABLE INITIALLY IMMEDIATE
);
CREATE INDEX "idx_avoir_contact" ON "sep_ref_contact" ("con_tco_code");
CREATE TABLE "sep_cotisant" (
"cot_organisme" numeric(3) NOT NULL,
"cot_compte" numeric(15) NOT NULL,
"cot_maj" datetime
);
CREATE UNIQUE INDEX "pk_sep_cotisant" ON "sep_cotisant"
("cot_compte","cot_organisme");
CREATE TABLE "sep_ref_perimetre" (
"per_id" numeric(6) NOT NULL,
"per_libelle" varchar(255) NOT NULL,
"per_maj" datetime NOT NULL,
"per_supp" numeric(1),
"per_ident" varchar(9) NOT NULL,
PRIMARY KEY ("per_id")
);
CREATE TABLE "sep_erreur" (
"err_code" varchar(4) NOT NULL,
"err_ter_code" varchar(4),
"err_libelle" varchar(255) NOT NULL,
"err_maj" datetime NOT NULL,
PRIMARY KEY ("err_code")--,
--CONSTRAINT fk_avoir_typerr_err_ter_code FOREIGN KEY (err_ter_code)
REFERENCES () ON DELETE NO ACTION NOT DEFERRABLE INITIALLY IMMEDIATE
);
CREATE INDEX "idx_avoir_typerreur" ON "sep_erreur" ("err_ter_code");
CREATE TABLE "sep_avoir_erreur" (
"aer_err_code" varchar(4) NOT NULL,
"aer_acr_id" numeric(6) NOT NULL,
"aer_commentaire" varchar(2000),
"aer_champ" varchar(100),
"aer_id" numeric(6) NOT NULL,
PRIMARY KEY ("aer_err_code","aer_acr_id","aer_id")--,
--CONSTRAINT fk_sep_avoir_erreur_aer_acr_id FOREIGN KEY (aer_acr_id)
REFERENCES () ON DELETE NO ACTION NOT DEFERRABLE INITIALLY IMMEDIATE,
--CONSTRAINT fk_sep_avoir_err_aer_err_code FOREIGN KEY () REFERENCES
() ON DELETE NO ACTION NOT DEFERRABLE INITIALLY IMMEDIATE
);
CREATE INDEX "idx_sep_avoir_erreur_1" ON "sep_avoir_erreur" ("aer_err_code");
CREATE INDEX "idx_sep_avoir_erreur_2" ON "sep_avoir_erreur" ("aer_acr_id");
CREATE TABLE "sep_appliquer_reel" (
"apr_acr_id" numeric(6) NOT NULL,
"apr_cha_id" numeric(6) NOT NULL,
"apr_ctt_id" numeric(6) NOT NULL,
"apr_valeur" varchar(255),
"apr_for_id" numeric(6),
PRIMARY KEY ("apr_acr_id","apr_cha_id","apr_ctt_id")--,
--CONSTRAINT fk_sep_appliquer_rl_apr_acr_id FOREIGN KEY (apr_acr_id)
REFERENCES () ON DELETE NO ACTION NOT DEFERRABLE INITIALLY IMMEDIATE,
--CONSTRAINT fk_sep_appliquer_rl_apr_cha_id FOREIGN KEY () REFERENCES
() ON DELETE NO ACTION NOT DEFERRABLE INITIALLY IMMEDIATE,
--CONSTRAINT fk_sep_appliquer_rl_apr_ctt_id FOREIGN KEY () REFERENCES
() ON DELETE NO ACTION NOT DEFERRABLE INITIALLY IMMEDIATE,
--CONSTRAINT fk_sep_appliquer_rl_apr_for_id FOREIGN KEY () REFERENCES
() ON DELETE NO ACTION NOT DEFERRABLE INITIALLY IMMEDIATE
);
CREATE INDEX "idx_sep_appliquer_reel_1" ON "sep_appliquer_reel" ("apr_acr_id");
CREATE INDEX "idx_sep_appliquer_reel_2" ON "sep_appliquer_reel" ("apr_cha_id");
CREATE INDEX "idx_sep_appliquer_reel_3" ON "sep_appliquer_reel" ("apr_ctt_id");
CREATE TABLE "sep_structurer" (
"stu_flt_id" numeric(6) NOT NULL,
"stu_ordre" numeric(3),
"stu_art_id" numeric(6) NOT NULL,
PRIMARY KEY ("stu_flt_id","stu_art_id")--,
--CONSTRAINT fk_sep_structurer_stu_art_id FOREIGN KEY (stu_art_id)
REFERENCES () ON DELETE NO ACTION NOT DEFERRABLE INITIALLY IMMEDIATE,
--CONSTRAINT fk_sep_structurer_stu_flt_id FOREIGN KEY () REFERENCES ()
ON DELETE NO ACTION NOT DEFERRABLE INITIALLY IMMEDIATE
);
CREATE INDEX "idx_sep_structurer_2" ON "sep_structurer" ("stu_flt_id");
CREATE TABLE "sep_parcours_type" (
"prt_id" numeric(6) NOT NULL,
"prt_trs_id" numeric(6) NOT NULL,
"prt_tpa_code" varchar(4) NOT NULL,
"prt_libelle" varchar(255) NOT NULL,
"prt_demarrer" numeric(1) NOT NULL,
"prt_actif" numeric(1) NOT NULL,
"prt_ident" varchar(9) NOT NULL,
"prt_maj" datetime NOT NULL,
PRIMARY KEY ("prt_id")--,
--CONSTRAINT fk_avoir_typparcou_prt_tpa_cod FOREIGN KEY (prt_tpa_code)
REFERENCES () ON DELETE NO ACTION NOT DEFERRABLE INITIALLY IMMEDIATE,
--CONSTRAINT fk_composer_prt_trs_id FOREIGN KEY () REFERENCES () ON
DELETE NO ACTION NOT DEFERRABLE INITIALLY IMMEDIATE
);
CREATE INDEX "idx_composer" ON "sep_parcours_type" ("prt_trs_id");
CREATE INDEX "idx_avoir_typparcours" ON "sep_parcours_type" ("prt_tpa_code");
CREATE TABLE "sep_flux_type" (
"flt_id" numeric(6) NOT NULL,
"flt_nat_id" numeric(6),
"flt_per_id" numeric(6),
"flt_dro_code" varchar(4),
"flt_ech_id" numeric(6),
"flt_sup_id" numeric(6),
"flt_tfl_code" varchar(4),
"flt_ini_code" varchar(4),
"flt_pei_code" varchar(4),
"flt_nom" varchar(255) NOT NULL,
"flt_fsenreg" varchar(255),
"flt_fsflux" varchar(255),
"flt_shema" varchar(255),
"flt_echeance" numeric(3),
"flt_fsbrut" varchar(255),
"flt_incomplet" numeric(1),
"flt_supp" numeric(1),
"flt_ident" varchar(9) NOT NULL,
"flt_maj" datetime NOT NULL,
PRIMARY KEY ("flt_id")--,
--CONSTRAINT fk_avoir_droit_flt_dro_code FOREIGN KEY (flt_dro_code)
REFERENCES () ON DELETE NO ACTION NOT DEFERRABLE INITIALLY IMMEDIATE,
--CONSTRAINT fk_avoir_initiati_flt_ini_code FOREIGN KEY () REFERENCES
() ON DELETE NO ACTION NOT DEFERRABLE INITIALLY IMMEDIATE,
--CONSTRAINT fk_avoir_nature_flt_nat_id FOREIGN KEY () REFERENCES ()
ON DELETE NO ACTION NOT DEFERRABLE INITIALLY IMMEDIATE,
--CONSTRAINT fk_avoir_perimetre_flt_per_id FOREIGN KEY () REFERENCES
() ON DELETE NO ACTION NOT DEFERRABLE INITIALLY IMMEDIATE,
--CONSTRAINT fk_avoir_perio_flt_pei_code FOREIGN KEY () REFERENCES ()
ON DELETE NO ACTION NOT DEFERRABLE INITIALLY IMMEDIATE,
--CONSTRAINT fk_avoir_typfux_flt_tfl_code FOREIGN KEY () REFERENCES ()
ON DELETE NO ACTION NOT DEFERRABLE INITIALLY IMMEDIATE,
--CONSTRAINT fk_avoir_typsupport_flt_sup_id FOREIGN KEY () REFERENCES
() ON DELETE NO ACTION NOT DEFERRABLE INITIALLY IMMEDIATE,
--CONSTRAINT fk_constituer_flt_ech_id FOREIGN KEY () REFERENCES () ON
DELETE NO ACTION NOT DEFERRABLE INITIALLY IMMEDIATE
);
CREATE INDEX "idx_avoir_typsupport" ON "sep_flux_type" ("flt_sup_id");
CREATE INDEX "idx_avoir_initiative" ON "sep_flux_type" ("flt_ini_code");
CREATE INDEX "idx_constituer" ON "sep_flux_type" ("flt_ech_id");
CREATE INDEX "idx_avoir_nature" ON "sep_flux_type" ("flt_nat_id");
CREATE INDEX "idx_avoir_perio" ON "sep_flux_type" ("flt_pei_code");
CREATE INDEX "idx_avoir_droit" ON "sep_flux_type" ("flt_dro_code");
CREATE INDEX "idx_avoir_perimetre" ON "sep_flux_type" ("flt_per_id");
CREATE INDEX "idx_avoir_typfux" ON "sep_flux_type" ("flt_tfl_code");
CREATE TABLE "sep_selection_flux" (
"slf_uti_id" numeric(6) NOT NULL,
"slf_flt_id" numeric(6) NOT NULL,
PRIMARY KEY ("slf_uti_id","slf_flt_id")--,
--CONSTRAINT fk_selection_flux_slf_flt_id FOREIGN KEY (slf_flt_id)
REFERENCES () ON DELETE NO ACTION NOT DEFERRABLE INITIALLY IMMEDIATE,
--CONSTRAINT fk_selection_flux_slf_uti_id FOREIGN KEY () REFERENCES ()
ON DELETE NO ACTION NOT DEFERRABLE INITIALLY IMMEDIATE
);
CREATE INDEX "idx_sep_selection_flux_1" ON "sep_selection_flux" ("slf_uti_id");
CREATE INDEX "idx_sep_selection_flux_2" ON "sep_selection_flux" ("slf_flt_id");
CREATE TABLE "sep_detenir" (
"det_per_id" numeric(6) NOT NULL,
"det_rol_code" varchar(4) NOT NULL,
"det_uti_id" numeric(6) NOT NULL,
PRIMARY KEY ("det_per_id","det_rol_code","det_uti_id")--,
--CONSTRAINT fk_sep_detenir_det_per_id FOREIGN KEY (det_per_id)
REFERENCES () ON DELETE NO ACTION NOT DEFERRABLE INITIALLY IMMEDIATE,
--CONSTRAINT fk_sep_detenir_det_rol_code FOREIGN KEY () REFERENCES ()
ON DELETE NO ACTION NOT DEFERRABLE INITIALLY IMMEDIATE,
--CONSTRAINT fk_sep_detenir_det_uti_id FOREIGN KEY () REFERENCES () ON
DELETE NO ACTION NOT DEFERRABLE INITIALLY IMMEDIATE
);
CREATE INDEX "idx_sep_detenir_1" ON "sep_detenir" ("det_per_id");
CREATE INDEX "idx_sep_detenir_2" ON "sep_detenir" ("det_rol_code");
CREATE INDEX "idx_sep_detenir_3" ON "sep_detenir" ("det_uti_id");
CREATE TABLE "sep_article" (
"art_id" numeric(6) NOT NULL,
"art_flt_id" numeric(6),
"art_nom" varchar(255) NOT NULL,
"art_nb_max" varchar(6),
"art_editable" varchar(4),
"art_separateur" varchar(4),
"art_find" numeric(1) NOT NULL,
"art_find_pos" varchar(5),
"art_find_char" varchar(255),
"art_find_separateur" varchar(4),
"art_pasarticlesuivant" numeric(1) NOT NULL,
"art_gra_id" numeric(6),
"art_numero_ordre" varchar(6),
PRIMARY KEY ("art_id")
);
CREATE TABLE "sep_action_reel" (
"acr_id" numeric(6) NOT NULL,
"acr_eta_code" varchar(4),
"acr_flr_id" numeric(10) NOT NULL,
"acr_att_code" varchar(4) NOT NULL,
"acr_debut" datetime,
"acr_fin" datetime,
"acr_niveau" numeric(4) NOT NULL,
"acr_maj" datetime NOT NULL,
"acr_ident" varchar(9) NOT NULL,
PRIMARY KEY ("acr_id")--,
--CONSTRAINT fk_avoiractiontyp_acr_att_code FOREIGN KEY (acr_att_code)
REFERENCES () ON DELETE NO ACTION NOT DEFERRABLE INITIALLY IMMEDIATE,
--CONSTRAINT fk_avoir_etat_acr_eta_code FOREIGN KEY () REFERENCES ()
ON DELETE NO ACTION NOT DEFERRABLE INITIALLY IMMEDIATE,
--CONSTRAINT fk_parcours_reel_acr_flr_id FOREIGN KEY () REFERENCES ()
ON DELETE NO ACTION NOT DEFERRABLE INITIALLY IMMEDIATE
);
CREATE INDEX "idx_avoir_actiontype" ON "sep_action_reel" ("acr_att_code");
CREATE INDEX "idx_parcours_reel" ON "sep_action_reel" ("acr_flr_id");
CREATE INDEX "idx_avoir_etat" ON "sep_action_reel" ("acr_eta_code");
CREATE TABLE "sep_action_param_rl" (
"apm_acr_id" numeric(6) NOT NULL,
"apm_typ_code" varchar(4) NOT NULL,
"apm_valeur" varchar(2000),
PRIMARY KEY ("apm_acr_id","apm_typ_code")--,
--CONSTRAINT fk_action_param_rl_apm_acr_id FOREIGN KEY (apm_acr_id)
REFERENCES () ON DELETE NO ACTION NOT DEFERRABLE INITIALLY IMMEDIATE,
--CONSTRAINT fk_action_param_rl_apm_typ_cod FOREIGN KEY () REFERENCES
() ON DELETE NO ACTION NOT DEFERRABLE INITIALLY IMMEDIATE
);
CREATE INDEX "idx_sep_action_param_rl_1" ON "sep_action_param_rl"
("apm_acr_id");
CREATE INDEX "idx_sep_action_param_rl_2" ON "sep_action_param_rl"
("apm_typ_code");
CREATE TABLE "sep_parcours" (
"prc_prt_id" numeric(6) NOT NULL,
"prc_flr_id" numeric(10) NOT NULL,
PRIMARY KEY ("prc_prt_id","prc_flr_id")--,
--CONSTRAINT fk_sep_parcours_prc_flr_id FOREIGN KEY (prc_flr_id)
REFERENCES () ON DELETE NO ACTION NOT DEFERRABLE INITIALLY IMMEDIATE,
--CONSTRAINT fk_sep_parcours_prc_prt_id FOREIGN KEY () REFERENCES ()
ON DELETE NO ACTION NOT DEFERRABLE INITIALLY IMMEDIATE
);
CREATE INDEX "idx_sep_parcours_1" ON "sep_parcours" ("prc_prt_id");
CREATE INDEX "idx_sep_parcours_2" ON "sep_parcours" ("prc_flr_id");
CREATE TABLE "sep_champ" (
"cha_id" numeric(6) NOT NULL,
"cha_ori_id" numeric(6),
"cha_tch_id" numeric(6) NOT NULL,
"cha_numero" varchar(4) NOT NULL,
"cha_nom" varchar(50) NOT NULL,
"cha_debut" numeric(5) NOT NULL,
"cha_fin" numeric(5) NOT NULL,
"cha_modifiable" numeric(1) NOT NULL,
"cha_maj" datetime NOT NULL,
"cha_ident" varchar(9) NOT NULL,
"cha_chm_id" numeric(6),
"cha_art_id" numeric(6),
"cha_libelle" varchar(255),
"cha_taille" varchar(6),
"cha_type" varchar(20),
"cha_numordre" varchar(6),
PRIMARY KEY ("cha_id")--,
--CONSTRAINT fk_avoir_typchp_cha_tch_id FOREIGN KEY (cha_tch_id)
REFERENCES () ON DELETE NO ACTION NOT DEFERRABLE INITIALLY IMMEDIATE,
--CONSTRAINT fk_sep_champ_cha_art_id FOREIGN KEY () REFERENCES () ON
DELETE NO ACTION NOT DEFERRABLE INITIALLY IMMEDIATE,
--CONSTRAINT fk_sep_provenir_cha_ori_id FOREIGN KEY () REFERENCES ()
ON DELETE NO ACTION NOT DEFERRABLE INITIALLY IMMEDIATE
);
CREATE INDEX "idx_sep_champ_mega" ON "sep_champ" ("cha_chm_id");
CREATE INDEX "idx_sep_provenir" ON "sep_champ" ("cha_ori_id");
CREATE INDEX "idx_avoir_typchp" ON "sep_champ" ("cha_tch_id");
CREATE TABLE "sep_controle" (
"ctl_etp_id" numeric(6) NOT NULL,
"ctl_ctt_id" numeric(6) NOT NULL,
PRIMARY KEY ("ctl_etp_id","ctl_ctt_id")--,
--CONSTRAINT fk_sep_controle_ctl_ctt_id FOREIGN KEY (ctl_ctt_id)
REFERENCES () ON DELETE NO ACTION NOT DEFERRABLE INITIALLY IMMEDIATE,
--CONSTRAINT fk_sep_controle_ctl_etp_id FOREIGN KEY () REFERENCES ()
ON DELETE NO ACTION NOT DEFERRABLE INITIALLY IMMEDIATE
);
CREATE INDEX "idx_sep_controle_1" ON "sep_controle" ("ctl_etp_id");
CREATE INDEX "idx_sep_controle_2" ON "sep_controle" ("ctl_ctt_id");
CREATE TABLE "sep_appliquer" (
"app_etp_id" numeric(6) NOT NULL,
"app_cha_id" numeric(6) NOT NULL,
"app_ctt_id" numeric(6) NOT NULL,
"app_valeur" varchar(255),
"app_for_id" numeric(6),
PRIMARY KEY ("app_etp_id","app_cha_id","app_ctt_id")--,
--CONSTRAINT fk_sep_appliquer_app_cha_id FOREIGN KEY (app_cha_id)
REFERENCES () ON DELETE NO ACTION NOT DEFERRABLE INITIALLY IMMEDIATE,
--CONSTRAINT fk_sep_appliquer_app_ctt_id FOREIGN KEY () REFERENCES ()
ON DELETE NO ACTION NOT DEFERRABLE INITIALLY IMMEDIATE,
--CONSTRAINT fk_sep_appliquer_app_etp_id FOREIGN KEY () REFERENCES ()
ON DELETE NO ACTION NOT DEFERRABLE INITIALLY IMMEDIATE,
--CONSTRAINT fk_sep_appliquer_app_for_id FOREIGN KEY () REFERENCES ()
ON DELETE NO ACTION NOT DEFERRABLE INITIALLY IMMEDIATE
);
CREATE INDEX "idx_sep_appliquer_1" ON "sep_appliquer" ("app_etp_id");
CREATE INDEX "idx_sep_appliquer_2" ON "sep_appliquer" ("app_cha_id");
CREATE INDEX "idx_sep_appliquer_3" ON "sep_appliquer" ("app_ctt_id");
CREATE TABLE "sep_etape" (
"etp_id" numeric(6) NOT NULL,
"etp_att_code" varchar(4) NOT NULL,
"etp_prt_id" numeric(6) NOT NULL,
"etp_niveau" numeric(4) NOT NULL,
PRIMARY KEY ("etp_id")--,
--CONSTRAINT fk_avoir_action_etp_prt_id FOREIGN KEY (etp_prt_id)
REFERENCES () ON DELETE NO ACTION NOT DEFERRABLE INITIALLY IMMEDIATE,
--CONSTRAINT fk_est_etp_att_code FOREIGN KEY () REFERENCES () ON
DELETE NO ACTION NOT DEFERRABLE INITIALLY IMMEDIATE
);
CREATE INDEX "idx_avoir_action" ON "sep_etape" ("etp_prt_id");
CREATE INDEX "idx_est" ON "sep_etape" ("etp_att_code");
END TRANSACTION;
Yann
Surlignage Dave Cramer <Dave@micro-automation.net>:
> How do you set the parameters in pg explorer?
>
> Can you send me the query and the table definitions?
>
> Dave
>
> On Tue, 2002-10-29 at 11:31, ygloriau@siliage.fr wrote:
> > The query is working fine under pg explorer.
> > Yann
> >
> > Surlignage Dave Cramer <Dave@micro-automation.net>:
> >
> > > It doesn't even get to postgres at that point. So it must be something
> > > else. Is the query correct?
> > >
> > > Dave
> > > On Tue, 2002-10-29 at 11:23, ygloriau@siliage.fr wrote:
> > > > Hi,
> > > > I'm using this driver: pgjdbc2.jar
> > > > And i'm sure that the problem is from the number of parameter. I tryed
> to
> > >
> > > > insert other parameters before before the 51th, and it didn't worked.
> > > > I also had a look at the source code. I've seen that it wasn't limited.
>
> > > > So, if it's not the driver, may it be postgres ?
> > > > Yann
> > > >
> > > >
> > > > Surlignage Dave Cramer <Dave@micro-automation.net>:
> > > >
> > > > > Yann
> > > > >
> > > > > Which version of the driver are you using.
> > > > >
> > > > > Looking at the latest code, the statement is parsed and the number
> of
> > > > > parameters are set based on the number of ? marks in the statement.
> Are
> > > > > you sure it is the number of parameters or actually the 51 st
> parameter
> > > > > that is causing the problem.
> > > > >
> > > > > Dave
> > > > > On Tue, 2002-10-29 at 10:35, ygloriau@siliage.fr wrote:
> > > > > >
> > > > > >
> > > > > > Hi all,
> > > > > > I'm dealing with the hugest query i've ever seen : 93 parameters !
>
> > > > > >
> > > > > > The problem is that i got an exception when i try to add the 51th
> > > > > parameter.
> > > > > > No way to have mor ethan 50 parameters.
> > > > > > Is there a solution to fix up this problem ?
> > > > > > (I won't rewrite the query !)
> > > > > >
> > > > > > thanks
> > > > > >
> > > > > > Yann
> > > > > >
> > > > > > ---------------------------(end of
> > > broadcast)---------------------------
> > > > > > TIP 4: Don't 'kill -9' the postmaster
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > > >
> > > > >
> > > >
> > > >
> > > >
> > > > ---------------------------(end of
> broadcast)---------------------------
> > > > TIP 6: Have you searched our list archives?
> > > >
> > > > http://archives.postgresql.org
> > > >
> > > >
> > >
> > >
> > >
> > >
> >
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 2: you can get off all lists at once with the unregister command
> > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
> >
> >
>
>
>
>
pgsql-jdbc by date: