Re: Prepared Statement limit ?

Yann,

Ok, the problem is that you have a missing ' search for
then '0' else '1

you will find it on the second attempt

I wasted a considerable amount of my time debugging this. In the future
please make sure you have read over your code very carefully.

IMHO this is a classic example of why huge selects are ill advised.

Dave

On Tue, 2002-10-29 at 11:43, ygloriau(at)siliage(dot)fr wrote:
> 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(at)micro-automation(dot)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(at)siliage(dot)fr wrote:
> > > The query is working fine under pg explorer.
> > > Yann
> > >
> > > Surlignage Dave Cramer <Dave(at)micro-automation(dot)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(at)siliage(dot)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(at)micro-automation(dot)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(at)siliage(dot)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(at)postgresql(dot)org)
> > >
> > >
> >
> >
> >
> >
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>
>