Focal Point
[SOLVED]Comparing columns in two hold files

This topic can be found at:
http://forums.informationbuilders.com/eve/forums/a/tpc/f/7971057331/m/617102255

October 15, 2010, 09:48 AM
SeyedG
[SOLVED]Comparing columns in two hold files
All,
I need to compare a column called user_div in a hold file with another column called div_cd in another hold file. If I there are no matches, then I would like to display an error. How can I accomplish that? Here is what I have for now:

 

TABLE FILE TDIV

PRINT *

IF RECORDLIMIT EQ 10;
ON TABLE HOLD AS TAB1
END
?FF TAB1

TABLE FILE TAB1
PRINT *
END

TABLE FILE VTBLUSER
PRINT DST.USER_DIV
ON TABLE HOLD AS TAB2
END

?FF TAB2

JOIN USER_DIV IN TAB2 TO ALL DIV_CD IN TAB1 AS TAB3

TABLE FILE TAB3
PRINT USER_DIV
      DIV_CD
IF RECORDLIMIT EQ 10;
END


Thank you for your help,


Seyed

This message has been edited. Last edited by: SeyedG,


WebFOCUS 8.0.09
App Studio 8009
Linux Kernel-2.6
DBMS: Oracle 11g
all output (Excel, HTML, AHTML, PDF)
October 15, 2010, 09:55 AM
Francis Mariani
Check the documentation for MATCH FILE.


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
October 15, 2010, 10:06 AM
SeyedG
Francis,
I appreciate your input. I believe, it will solve my problem.

Thanks again,

Seyed


WebFOCUS 8.0.09
App Studio 8009
Linux Kernel-2.6
DBMS: Oracle 11g
all output (Excel, HTML, AHTML, PDF)
October 15, 2010, 11:23 AM
SeyedG
Hello Francis,
All of the following merge phrases return the same result set. I am certain that there are 3 entries that are in the old and not new and 6 entries in the new and not old. Here is the script that I wrote to compare the two files. All commented merge phrases have been tested separately.

 
MATCH FILE TDIV

SUM DIV_NM
BY DIV_CD
END
FILE VTBLUSER
SUM DST.USER_UNIT
BY USER_DIV
-*AFTER MATCH HOLD OLD-NOR-NEW
-*AFTER MATCH HOLD NEW-NOT-OLD
-*AFTER MATCH HOLD OLD-AND-NEW
-*AFTER MATCH HOLD OLD-NOT-NEW
AFTER MATCH HOLD NEW-NOT-OLD
END

TABLE FILE HOLD
PRINT *
END



Thank you,


Seyed


WebFOCUS 8.0.09
App Studio 8009
Linux Kernel-2.6
DBMS: Oracle 11g
all output (Excel, HTML, AHTML, PDF)
October 17, 2010, 04:43 PM
Waz
If you have a unique field from each side of the match and use OLD-OR-NEW, you can then check which records came from either side.


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.06OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Know The Code

October 17, 2010, 05:47 PM
Dan Satchell
If you want only records not present in both files (exclude the intersection), OLD-NOR-NEW is the correct option. But it looks like you have an error in your syntax. RUN should follow the first query, not END. Also, I seem to remember that the DST. prefix may not be supported in MATCH FILE logic.

MATCH FILE TDIV
SUM DIV_NM
BY DIV_CD
RUN

FILE VTBLUSER
SUM DST.USER_UNIT
BY USER_DIV
AFTER MATCH HOLD OLD-NOR-NEW
END

TABLE FILE HOLD
PRINT *
END



WebFOCUS 7.7.05