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     [SOLVED] Match then Join

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] Match then Join
 Login/Join
 
Daniel G
Gold member
posted
I have two synonyms that contain agency information. I need information from both synonyms so I match both on agency_id. I take the hold file, that is created, and I need to join this hold file to a fact table so that I can get additional. After I join to the fact table all the browser does is sits and spins and eventually times out. Here is the code I am using. Any thoughts?

MATCH FILE DIM_AGENCY
  PRINT DIM_AGENCY.DIM_AGENCY.DIM_AGENCY_KEY DIM_AGENCY.DIM_AGENCY.AGENCY_NAME DIM_AGENCY.DIM_AGENCY.AGENCY_PHYSICAL_ADDRESS_1 DIM_AGENCY.DIM_AGENCY.AGENCY_PHYSICAL_ADDRESS_2 DIM_AGENCY.DIM_AGENCY.AGENCY_STATE DIM_AGENCY.DIM_AGENCY.AGENCY_STATE_NAME 
  BY DIM_AGENCY.DIM_AGENCY.AGENCY_ID 
RUN
FILE COMM
  PRINT COMM.COMM.SUBDIVISION
  BY COMM.COMM.AGENCY_ID NOPRINT
AFTER MATCH HOLD AS AGENCY_COMM OLD-OR-NEW
END
-RUN

JOIN
CARDS_DATAMART_INTAKE.FACT_CARDS_INTAKE.DIM_AGENCY_KEY IN CARDS_DATAMART_INTAKE
TO UNIQUE AGENCY_COMM.AGENCY_C.DIM_AGENCY_KEY IN AGENCY_COMM TAG J0 AS J0
END


Thanks,

Daniel

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


In Focus since 2012
WebFOCUS 8.0.07
Windows, All Outputs
 
Posts: 59 | Registered: November 15, 2012Report This Post
Danny-SRL
Virtuoso
posted Hide Post
Daniel,

Your MATCH command created a sequential file.
What type of file is CARDS_DATAMART_INTAKE?
If it is also sequential then both file MUST BE SORTED by the JOIN field.
However, I can assume that it is not sequential.
So, either you join the AGENCY_COMM file to CARDS_DATAMART_INTAKE or you need another step to load the data of AGENCY_COMM into a FOCUS file with DIM_AGENCY_KEY declared as an INDEX. Or, if CARDS_DATAMART_INTAKE is an SQL table, load AGENCY_COMM into another SQL table.

Good luck!


Daniel
In Focus since 1982
wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF

 
Posts: 1980 | Location: Tel Aviv, Israel | Registered: March 23, 2006Report This Post
Lori B McClellan
Member
posted Hide Post
The match created a sequential hold file in agency_id order and the join is on dim_agency_key. It is unclear if these are the same value.

I'm unsure of the file format of the host file in the join. Is it the same order as the sequential hold file you just created?

Lori


WebFOCUS, FOCUS, DB2 WebQuery
 
Posts: 21 | Location: Greater Chicago area | Registered: May 29, 2003Report This Post
Daniel G
Gold member
posted Hide Post
Danny, the CARDS_DATAMART_INTAKE is a resutset from a MSSQL table.

Lori, I matched on the agency_id because that is the common filed in both synonym. Both synonyms are sorted by the agency_id. One of the sysnonym that is used in the match contains the dim_agency_key field so when I join I have the dim_agency_key in the match file to join the the main fact synonym. The agency_id and dim_agency_key are not the same id. All host files come from a MSSQL result set.

Daniel


In Focus since 2012
WebFOCUS 8.0.07
Windows, All Outputs
 
Posts: 59 | Registered: November 15, 2012Report This Post
George Patton
Master
posted Hide Post
Just take your HOLD file and re-sort it again with:

ON TABLE HOLD AS xyz FORMAT FOCUS INDEX AGENCY_ID

If you do that it won't matter if your HOLD file isn't in the same sequence as the host file.


WebFOCUS 7.7.05 Windows, Linux, DB2, IBM Lotus Notes, Firebird, Lotus Symphony/OpenOffice. Outputs PDF, Excel 2007 (for OpenOffice integration), WP
 
Posts: 674 | Location: Guelph, Ontario, Canada ... In Focus since 1985 | Registered: September 28, 2010Report This Post
Lori B McClellan
Member
posted Hide Post
Either the host and the target have to be sorted in the same order or the target joined has to be a keyed file.

As other have indicated. Take the hold file from the match and hold it as a FOCUS file indexing the field you need.

George have tyou the syntax on the HOLD.

Lori


WebFOCUS, FOCUS, DB2 WebQuery
 
Posts: 21 | Location: Greater Chicago area | Registered: May 29, 2003Report This Post
Joni Campbell - Europa Sports
Silver Member
posted Hide Post
Daniel - Consider holding the CARDS_DATAMART_INTAKE data in FOCUS format with an index, and reversing the JOIN statement to JOIN from AGENCY_COMM to CARDS_DATAMART_INTAKE.


8009
Windows, HTML, AHTML, Excel
In FOCUS since 1983
 
Posts: 41 | Location: Charlotte, NC | Registered: January 06, 2012Report This Post
Daniel G
Gold member
posted Hide Post
That worked thanks.


In Focus since 2012
WebFOCUS 8.0.07
Windows, All Outputs
 
Posts: 59 | Registered: November 15, 2012Report 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     [SOLVED] Match then Join

Copyright © 1996-2020 Information Builders