Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] Multiple MATCHes

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[CLOSED] Multiple MATCHes
 Login/Join
 
Gold member
posted
Hello all,

I am currently re-writing a report and am trying to slim down the code a bit. Currently the report run two querries and then matches them together in a single file, like this:

DEFINE FILE VW_FCB_RECVRY_TRK
BLANK/A45 = ' 6b) FIELD COMPLIANCE POST LEGACY NOTICE';
END
TABLE FILE VW_FCB_RECVRY_TRK
SUM
COMPUTE MONEY1/D15.2C!D = MONEY; AS ''
BY PERIOD
BY BLANK
WHERE PERIOD GE '200707';
WHERE MONEY_CREDIT EQ 'L';
ON TABLE HOLD AS FCBPOST
END
DEFINE FILE VW_FCB_RECVRY_TRK
BLANK/A45 = ' 6a) FIELD COMPLIANCE PRE LEGACY NOTICE';
END
TABLE FILE VW_FCB_RECVRY_TRK
SUM
COMPUTE MONEY2/D15.2C!D = MONEY; AS ''
BY PERIOD
BY BLANK
WHERE PERIOD GE '200707';
WHERE MONEY_CREDIT EQ 'A';
ON TABLE HOLD AS FCBPRE
END
MATCH FILE FCBPOST
SUM MONEY1
BY PERIOD
BY BLANK
RUN
FILE FCBPRE
SUM MONEY2
BY PERIOD
BY BLANK
AFTER MATCH HOLD AS FCB0 OLD-OR-NEW
END
TABLE FILE FCB0
SUM
COMPUTE MONEY3/D15.2C!D = MONEY1 + MONEY2; AS ''
BY PERIOD
BY BLANK
ON TABLE HOLD AS FCB1
END


My question is, can I run more than two querries, then match them all together in a single statement, similar to this:

ATCH FILE PT_ONLY_PRE_DISC
SUM MONEY50
BY PERIOD
RUN
FILE BT_ONLY_PRE_DISC
SUM MONEY51
BY PERIOD
RUN
FILE BT_ONLY_NCAMS_PRE_DISC
SUM MONEY52
BY PERIOD
RUN
FILE NEX_ONLY_PREAGE_SPRDSHT
SUM MONEY53
BY PERIOD
AFTER MATCH HOLD AS ALL_ONLY_PRE_NEXDISC OLD-OR-NEW
END

Thanks,

This message has been edited. Last edited by: <Kathryn Henning>,


Kevin
______________________
Production: WebFocus 7.6.11 on Win2K3 Server
Test: WebFocus 7.6.11 on Win2K3 Server
Formats: Excel2K, PDF, HTML
 
Posts: 79 | Registered: February 29, 2008Report This Post
Silver Member
posted Hide Post
Yes, you can do more than two match queries. Here is the code snippet:

  
MATCH FILE PT_ONLY_PRE_DISC
SUM MONEY50
BY PERIOD
RUN
FILE BT_ONLY_PRE_DISC
SUM MONEY51
BY PERIOD
AFTER MATCH HOLD OLD-OR-NEW
RUN
FILE BT_ONLY_NCAMS_PRE_DISC
SUM MONEY52
BY PERIOD
AFTER MATCH HOLD OLD-OR-NEW
RUN
FILE NEX_ONLY_PREAGE_SPRDSHT
SUM MONEY53
BY PERIOD
AFTER MATCH HOLD OLD-OR-NEW
END


Then do TABLE FILE HOLD and PRINT all your fields.

Regards,
Venkat


product release:8203
o/s: windows 10
expected o/p formats: HTML,EXCEL,PDF
 
Posts: 34 | Location: Kearney, Nebraska | Registered: December 14, 2011Report This Post
Virtuoso
posted Hide Post
You can do that, but I do not understand why you do this.
The match is a way to combine data from different databases .
Your data coms from one database so it can be retrieved in obe statement
TABLE FILE XXXX
SUM MONEY
BY PERIOD
BY MONEY_CREDIT
WHERE PERIOD GE '200707';
WHERE MONEY_CREDIT EQ 'L';
ON TABLE HOLD AS FCBPOST
END
DEFINE FILE FCBPOST
BLANK/A45 = DECODE MONEY_CREDIT(C ' 6a) FIELD COMPLIANCE PRE LEGACY NOTICE' 
L  ' 6b) FIELD COMPLIANCE POST LEGACY NOTICE');
END
TABLE FILE FCBPOST 
SUM MONEY
BY PERIOD
BY BLANK
END


I hope this gives you an idea
The computes and defines will be done at the end after collecting all the data.




Frank

prod: WF 7.6.10 platform Windows,
databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7
test: WF 7.6.10 on the same platform and databases,IE7

 
Posts: 2387 | Location: Amsterdam, the Netherlands | Registered: December 03, 2006Report This Post
Virtuoso
posted Hide Post
Also keep in mind that MATCH expects 1 to 0-1 relations between the files.

If you have multiple right-hand-side matches for a single left-hand-side record, you will only get the first right-hand-side match in your results.

I find that rather peculiar behaviour, but that's the way it was designed.


WebFOCUS 8.1.03, Windows 7-64/2008-64, IBM DB2/400, Oracle 11g & RDB, MS SQL-Server 2005, SAP, PostgreSQL 11, Output: HTML, PDF, Excel 2010
: Member of User Group Benelux :
 
Posts: 1669 | Location: Enschede, Netherlands | Registered: August 12, 2010Report This Post
Silver Member
posted Hide Post
This is my reference in regards to using MATCH.

http://ecl.informationbuilders...ource%2Ftopic147.htm

You can match multiple files together. You could have one hold file at the end or intermediate hold files after each file matched with different merge phrases. I also like to note how match is different based on the interesting use of PRINT/SUM.


Year(s) of experience in WebFOCUS: 5+. Using WebFOCUS 7.7.03 on Windows platform with Oracle/SQL Server.
 
Posts: 41 | Registered: September 08, 2008Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] Multiple MATCHes

Copyright © 1996-2020 Information Builders