Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED]Comparing columns in two hold files
Go
New
Search
Notify
Tools
Reply
  
[SOLVED]Comparing columns in two hold files
 Login/Join
 
Gold member
posted
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)
 
Posts: 86 | Location: Oklahoma City, Oklahoma | Registered: July 01, 2010Reply With QuoteReport This Post
Expert
posted Hide Post
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
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Reply With QuoteReport This Post
Gold member
posted Hide Post
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)
 
Posts: 86 | Location: Oklahoma City, Oklahoma | Registered: July 01, 2010Reply With QuoteReport This Post
Gold member
posted Hide Post
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)
 
Posts: 86 | Location: Oklahoma City, Oklahoma | Registered: July 01, 2010Reply With QuoteReport This Post
Expert
posted Hide Post
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.05OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Know The Code

 
Posts: 6069 | Location: Land of the Darug people, Terra Australis Incognita | Registered: October 31, 2006Reply With QuoteReport This Post
Virtuoso
posted Hide Post
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
 
Posts: 1213 | Location: Seattle, Washington - USA | Registered: October 22, 2007Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED]Comparing columns in two hold files

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.