Hi
I inherited from an awful SQL statement which I have to analyse.
When I launch this one in the Explain Plan, I get a result, when i add the hint /*+ ORDERED */ it gives error "Invalid floating point operation" in SQL Nav 7.3 32 bit :
Regards
Martin
Hi martin，
Thanks for your report.
if you can provider your awful SQL statement to us, that would be helpful to reproduce this issue.
Thanks,
Michael
Hi Michael
For example :
SELECT /*+ ordered */
fcmx1.dpce AS fcmx1_dpce
, TO_DATE(fcmx1.dpce, ‘YYYYMMDD’) AS fcmx1_dpce_d
, fctx1.lcx_1 || fctx1.lcx_2 AS nomtiers
, fctx1.clg AS fctx1_clg
, fcmx1.cent AS fcmx1_cent
, fcmx1.ccx AS fcmx1_ccx
, fcmx1.cdev AS fcmx1_cdev
, fcmx1.cjnl AS fcmx1_cjnl
, fcmx1.dech AS fcmx1_dech
, TO_DATE(fcmx1.dech, ‘YYYYMMDD’) AS fcmx1_dech_d
, fcmx1.mmvt_ml1 AS fcmx1_mmvt_ml1
, fcmx1.mmvt AS fcmx1_mmvt
, fcmx1.mreste_ml1 AS fcmx1_mreste_ml1
, fcmx1.mreste AS fcmx1_mreste
, fcmx1.lref AS fcmx1_lref
, fcmx1.key_p AS fcmx1_key_p
, fcmx1.nex_gen_82 AS fcmx1_nex_gen_82
, fcmx1.npce AS fcmx1_npce
, fcmx1.csens AS fcmx1_csens
, fcmx1.key_c AS fcmx1_key_c
, fcti3.ladr AS fcti3_ladr
, fcti3.lrue AS fcti3_lrue
, fcti3.ccode_post AS fcti3_ccode_post
, fcti3.lloc AS fcti3_lloc
, fcti3.cid AS fcti3_cid
, fcti3.cpays AS fcti3_cpays
, fcti3.ltel AS fcti3_ltel
, fc011.lent AS fc011_lent
, fc023.ladr AS fc023_ladr
, fc023.lrue AS fc023_lrue
, fc023.ccode_post AS fc023_ccode_post
, fc023.lloc AS fc023_lloc
, fc023.cpays AS fc023_cpays
, fc023.ltel AS fc023_ltel
, fc023.lfax AS fc023_lfax
, fc041.ldev AS fc041_ldev
, fc651.lpays AS fc651_lpays
, fc902.cel_ass AS fc902_cel_ass
, fcmx1.ccg AS fcmx1_ccg
, fcmx1.nper_gen AS fcmx1_nper_gen
, fcmx1.cpartit_gen AS fcmx1_cpartit_gen
, fcmx1.cpartit_aux AS fcmx1_cpartit_aux
, fcmx1.cetat_pce AS fcmx1_cetat_pce
, fcmx1.cmode_pmt AS fcmx1_cmode_pmt
, fcmx1.ctrans AS fcmx1_ctrans
, fcmx1.ctrans_ext AS fcmx1_ctrans_ext
, fcmx1.dtrans_ext AS fcmx1_dtrans_ext
, fcmx1.djour AS fcmx1_djour
, fcmx1.canalyse1 AS fcmx1_canalyse1
, fcmx1.canalyse2 AS fcmx1_canalyse2
, fcmx1.lpointage AS fcmx1_lpointage
, fc902a.cel_ass AS nsoc
, fctx1.ccg_central AS fctx1_ccg_central
, (SELECT fcti1.cimmatr_fisc
FROM TABLE(zac_f_param2(hierar.entite, ‘FCTI1’, ‘’)) param_fcti1, fcti1
WHERE param_fcti1.ctab LIKE fc281.ctab_signal
AND fcti1.key_p LIKE RPAD(REPLACE(param_fcti1.key_p, ‘%’), 12) || fctx1.cid)
AS fcti1_cimmatr_fisc
, (SELECT fcti1.lid
FROM TABLE(zac_f_param2(hierar.entite, ‘FCTI1’, ‘’)) param_fcti1, fcti1
WHERE param_fcti1.ctab LIKE fc281.ctab_signal
AND fcti1.key_p LIKE RPAD(REPLACE(param_fcti1.key_p, ‘%’), 12) || fctx1.cid)
AS fcti1_lid
FROM (SELECT * FROM TABLE(zac_f_hierarchie(‘BEZP’))) hierar
, ((SELECT RPAD(NVL(REPLACE(’%’, ‘%’), ‘’), 15, '’) AS ccx FROM DUAL)) selec_ccx
, fcmx1
, fc281
, fctx1
, fcti3
, fc011
, fc011 fc011_b
, fc023
, fc041
, fc651
, fc902
, fc902 fc902a
WHERE fcmx1.key_c LIKE ‘CMX11’ || RPAD(hierar.entite, 4) || RPAD(‘AUXD’, 4) || ‘CLIB’ || RPAD(selec_ccx.ccx, 15) || ‘___%’
AND fctx1.key_p LIKE ‘CTX1ZPBECLIB’ || fcmx1.ccx
AND fc281.key_p LIKE 'C281ZPBEC28 ’ || fcmx1.cpartit_aux
AND fcti3.key_p LIKE ‘CTI3ZPBEIDTB’ || RPAD(fctx1.cid, 15) || fctx1.cadr
AND fc011.key_p LIKE ‘C011’ || RPAD(‘BEZP’, 4) || ‘C01%’
AND fc011_b.key_p LIKE ‘C011’ || RPAD(fcmx1.cent, 4) || ‘C01%’
AND fc023.key_p LIKE ‘C023’ || RPAD(fcmx1.cent, 4) || 'C02 ’ || RPAD(fc011_b.cid, 15) || fc011_b.cadr
AND fc041.key_p LIKE 'C041ZPBEC04 ’ || fcmx1.cdev
AND fc651.key_p LIKE 'C651ZPBEC65 ’ || fcti3.cpays
AND fc902.key_p LIKE ‘C902’ || RPAD(fcmx1.cent, 4) || ‘C01 ’ || RPAD(’ ', 25) || ‘GRC’
AND fc902a.key_p LIKE ‘C902’ || RPAD(fcmx1.cent, 4) || ‘C01 ’ || RPAD(’ ', 25) || ‘NSOC’;
Regards
Martin
