Joins inconsistency between WF 5.2.8 and 7.6.2

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)

Webfocus 7.7.3 Oracle
Windows Server 2003
Output: PDF, HTML(A), EXCEL

Posts: 25 | Location: Montreal | Registered: November 27, 2007

hammo1j

Master

posted

Hide Post

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

Server: WF 7.6.2 ( BID/Rcaster) Platform: W2003Server/IIS6/Tomcat/SQL Server repository Adapters: SQL Server 2000/Oracle 9.2
Desktop: Dev Studio 765/XP/Office 2003 Applications: IFS/Jobscope/Maximo

Posts: 888 | Location: Airstrip One | Registered: October 06, 2006

Ignored post by hammo1j posted

Show Post

GinnyJakes

Expert

posted

Hide Post

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.

Ginny
---------------------------------
Prod: WF 7.7.01 Dev: WF 7.6.9-11
Admin, MRE,self-service; adapters: Teradata, DB2, Oracle, SQL Server, Essbase, ESRI, FlexEnable, Google

Posts: 2723 | Location: Ann Arbor, MI | Registered: April 05, 2006

Ignored post by GinnyJakes posted

Show Post

MSIROTTI

Member

posted

Hide Post

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

Webfocus 7.7.3 Oracle
Windows Server 2003
Output: PDF, HTML(A), EXCEL

Posts: 25 | Location: Montreal | Registered: November 27, 2007

Ignored post by MSIROTTI posted

Show Post

Please Wait. Your request is being processed...

Read-Only Topic

Focal Point

Focal Point Forums

WebFOCUS/FOCUS Forum on Focal Point

Joins inconsistency between WF 5.2.8 and 7.6.2

Focal Point | Privacy Statement | Terms of Service