Hi
I need to optimise an awful statement I got :
SELECT /+FIRST_ROWS ordered/
fcmx1.key_p
,fcmx1.cent
,DECODE(fcmx1.dpce, 0, '', TO_CHAR(TO_DATE(LPAD(fcmx1.dpce, 8, '0'), 'YYYYMMDD'), 'DD/MM/YYYY')) AS dpce
,fcmx1.cjnl
,fcmx1.npce AS npce
,DECODE(fcmx1.csens, 'C', -1 * fcmx1.mmvt, fcmx1.mmvt) AS mmvt
,fcmx1.csens
,fcmx1.mreste
,fcmx1.mreste_ml1
,fcmx1.ccx AS ccx_orig
,DECODE(fcmx1.dech, 0, '', TO_CHAR(TO_DATE(fcmx1.dech, 'YYYYMMDD'), 'DD/MM/YYYY')) AS dech
,fcmx1.nseq_jnl AS let_nseq_jnl
,fcmx1.ntrans AS let_ntrans
,fcmx1.nseq_trans AS let_nseq_trans
,fcmx1.nseq_ech AS let_nseq_ech
,fcmx1.ctrans_ext
,fcmx1.cdev
,fcmx1.lref
,DECODE(NVL(z_sele.iden, ' '), '56de7bf06cc87__U49415', 'true', 'false') selec
,DECODE(fcmx1.opmt_encrs, NULL, '#FFFFFF', '#FF8080') AS bgcolor
,DECODE(fcmx1.opmt_encrs, NULL, '', 'logo_warning') AS logo_warning
,DECODE(fcmx1.opmt_encrs, NULL, '', 'Optn paiement en cours') AS warning_value
FROM (SELECT fc083.cent_ass
FROM TABLE(zac_f_param2('282', 'FC083', '3562' || '%')) param_fc083, fc083, fc014
WHERE fc083.key_p LIKE param_fc083.key_p AND fc014.cent = fc083.cent_ass AND fc014.cent_ass = '282'
UNION
SELECT '282' AS cent_ass FROM DUAL) table_cent_ass
,TABLE(zac_f_hierarchie(table_cent_ass.cent_ass)) hierar
,TABLE(zac_f_param2('282', 'FC191', 'FOUD')) param_fc191
,fc191
,fcmx1
,z_sele
,((SELECT DISTINCT fc081.cjnl
FROM TABLE(zac_f_hierarchie('282')) hierar, TABLE(zac_f_param2(hierar.entite, 'FC081', '%')) param_fc081, fc081
WHERE fc081.key_p LIKE param_fc081.key_p)) cjnl_in
WHERE fc191.key_p LIKE param_fc191.key_p
AND fcmx1.key_c LIKE
'CMX11'
|| RPAD(hierar.entite, 4)
|| 'AUX'
|| DECODE(fc191.tmod, '3', 'D', '4', 'C', '_')
|| RPAD('FOUD', 4)
|| RPAD('015093990000', 15)
|| '%'
AND fcmx1.cdev LIKE 'EUR'
AND fcmx1.cjnl LIKE cjnl_in.cjnl
AND (fcmx1.npce BETWEEN 8590257 AND 85902761)
AND fcmx1.key_p = z_sele.key_p
AND z_sele.iden = '56de7bf06cc87__U49415'
ORDER BY fcmx1.cent, fcmx1.dpce, fcmx1.npce
The problem is that when I launch the Explain Plan I directly get the error OCI-22053:overflow error
Regards
Martin