Hello WF world,
could someone be able to explain why the following fex was fine in 5.2.8, while in 7.6.8 all the fields from the target table (the fields with T1 tag) are missing in the report while they obviously exist ?
Here is the fex
TABLE FILE OA_EDPER
SUM
EDP_FACT_SOLDE_DU AS 'FACTURE_TOTALE'
BY
EDP_CORPORATION_ORIGINE
BY
EDP_NO_COMPTE
WHERE ( EDP_NO_COMPTE GE '&COMPTE_DU.No de compte de.' );
WHERE ( EDP_NO_COMPTE LE '&COMPTE_AU.à.' );
ON TABLE NOTOTAL
ON TABLE HOLD FORMAT FOCUS
END
JOIN
EDP_NO_COMPTE IN HOLD TAG T0 TO MULTIPLE EDP_NO_COMPTE
IN OA_EDPER TAG T1 AS J0
END
TABLE FILE HOLD
PRINT
T1.EDP_FACT_SOLDE_DU/P8.2C AS 'Solde dû'
T1.EDP_CODE_TSUS_2 AS 'T2'
T1.EDP_CODE_TSUS_5 AS 'T5'
T1.EDP_CODE_TSUS_6 AS 'T6'
T1.EDP_FACT_DATE_1ER_VERS AS 'Date 1er vers'
T1.EDP_FACT_DATE_2EM_VERS AS 'Date 2e vers'
T1.EDP_NOM1_OCCUPANT/A17 AS 'Occupant'
T1.ADRESSE/A25 AS 'Adresse'
BY
HIGHEST T0.FACTURE_TOTALE AS 'Solde dû,total'
BY
T0.EDP_NO_COMPTE AS 'No compte'
BY
T1.EDP_ANNEE_EXERCICE AS 'Exercice'
END
Thanks

(DB is Oracle)

Think this is prob down to your ORACHAR=VAR or fixed setting. Try search on ORACHAR on forum for more detail.

John is correct especially if you regenerated your Oracle master. Put this in your edasprof.prf:

ENGING SQLORA SET VARCHAR OFF

and re-gen your master.

Also if OA_EDPER is an Oracle table, joining a hold file to it is VERY inefficient. All the data will be brought back to WebFOCUS so that WF can do the join.

Put these trace commands in your program to see:

SET TRACEOFF = ALL
SET TRACEON = SQLAGGR//CLIENT
SET TRACEON = STMTRACE//CLIENT
SET TRACEON = STMTRACE/2/CLIENT
SET TRACESTAMP = OFF
SET TRACEWRAP = 78
SET TRACEUSER = ON
SET XRETRIEVAL=OFF  

One solution you can use is the WHERE IN FILE syntax to pass the keys from the hold file in a WHERE statement against the Oracle table. I can provide more detail or email you a relational efficiencies PowerPoint I just did for an internal user group.

Thank you both, in effect playing with ORACHAR solved the problem. I kind of understood that the problem was with the HOLD file. By the way the masters had been migrated and not refreshed and the fex is working with ORACHAR VAR.
And yes, I understand that with two holdfiles it would be more performant, but we have quite a few fexs that were written with these technique and for now this is better than losing time to modify them all.
Thanks again