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 email@example.com for further assistance. Reference the community FAQ to learn more about the community.
I am pretty new to Webfocus and I am hoping someone here can help me with the problem I am having with couple of reports. It seams that when we updated to 7.6.2 that webfocus got more sensitive. Anyways, when I am trying to join to hold files I am getting the above error message (FOC1070). Obviously, there is more to the code, but I am unsure if even this help in determening the right solution for this.
[SET ASNAMES = ON TABLE FILE EVENT SUM STECUC STEHBL STECT# LST.STEPNM AS 'STEPNM' LST.STETXT AS 'STETXT' LST.STESQD AS 'STESQD' LST.STESQT AS 'STESQT' BY STECUC NOPRINT BY STEHBL NOPRINT BY STECT# NOPRINT ON TABLE HOLD AS RAIL_EVENTS -*ON TABLE PCHOLD FORMAT EXL2K END -*-EXIT -********************************************************************************** JOIN CLEAR *
JOIN CNEE AND HBL AND CNTR# IN ACTIVITY TO ALL STECUC AND STEHBL AND STECT# IN RAIL_EVENTS AS J1 END
Thank you all for your responses. I have tried FORMAT FOCUS INDEX STECUC STEHBL STECT#, but i got antoher error message (FOC236) Linked file does not have matching key field or sagment. I am joining customer to customer, bill of lading to bill of lading and container# to container#. Now, there are multiple events for each record in ACTIVITY hold file therefore in RAIL_EVENTS hold file I pick only the last event to match up to recod in ACTIVITY hold file. Do the field names in FORMAT FOCUS INDEX FIELD FILED FIELD have to have the exact same name? Or just the key fields have to match?
If you want only last RAIL_EVENTS, have you considered reversing the join and table file? You've not mentioned how the ACTIVITY file is created, does it have multiple entries and sorted in the same order? Do you want all activity on one rail event?
Posts: 1317 | Location: Council Bluffs, IA | Registered: May 24, 2004
Viva, the way i read your code, your designated key fields are STECUC, STEHBL, AND STECT#. You need to sort your output file BY those fields so that focus knows how your extract is ordered, and your fex is using NOPRINT which means your extract file doesn't actually contain any keys, which explains the FOC236 error SUM LST.STEPNM AS 'STEPNM' LST.STETXT AS 'STETXT' LST.STESQD AS 'STESQD' LST.STESQT AS 'STESQT' BY STECUC BY STEHBL BY STECT# ought to do it for you.
.. of course if in fact STECUC et.al. need to be summed to create the key field values, then do so in a TABLE FILE above this one, a 2step process.
In Focus since 1979///7706m/5 ;wintel 2008/64;OAM security; Oracle db, ///MRE/BID
Posts: 3811 | Location: Manhattan | Registered: October 28, 2003
Natalija, You need not necessarily have to index your hold files for achieving the same. Ensure that all your files are ordered by the key fields with which you are joining. I encountered the same issue and was able to solve it.
You need not necessarily have to index your hold files for achieving the same. Ensure that all your files are ordered by the key fields with which you are joining. I encountered the same issue and was able to solve it.
Which is exactly what I told her to do on the 25th.
You should be able to join on 3 fields, but the format of all 3 have to be identical in both files. I would also rename the fields with # in the name, special character other than underscore can sometimes cause a problem especially in an Index. What is the format of the ACTIVITY file, is it a flat file or a database file? Where are the join fields in the Activity file (are they the first 3 fields, are they in the same segment)? Concatenating the 3 fields in both files and sorting on the concatenated field may be a good suggestion. The fields all need to be alphanumeric or converted to alphanumeric to do this. I would recommend sorting your Activity file BY CNEE BY HBL BY CNTR (DEFINE the name to remove the #).
Pat WF 7.6.8, AIX, AS400, NT AS400 FOCUS, AIX FOCUS, Oracle, DB2, JDE, Lotus Notes
Posts: 755 | Location: TX | Registered: September 25, 2007
Page 662 of Creating Reports WF7.1.3 manual clearly states that when joining two FOCUS files you may not specify more than one field in the cross referenced file -- therefore if you need to join on more than one field in the cross referenced file you must concatenate them together.
WF 7.7.05 HP-UX - Reporting Server, Windows 2008 - Client, MSSQL 2008, FOCUS Databases, Flat Files HTML, Excel, PDF
As linus stated, when joining to a FOCUS file, you can only join to a single field within the file.
Use one of the following options 1. Join to all STECUC and use a where statement to match on the other fields. 2. Create a single field with all of the values concatinated to build a key and use the key for the join (as stated above - note: this will be faster, only if the guest file contains many more records than what you will actually be joining to.) 3. Follow Susanah's advice on building the correct sort order. (note: this will most likely be most efficient since you will not have to spend the extra processing of building a FOCUS file with indexes)
Windows: WF 7.6.2: SQL Server 2008 R2
Posts: 86 | Location: Chicago | Registered: August 03, 2007
Two suggestions, both of which can work for Focus files:
1. "conditonal join" (see syntax at documentation.informationbuilders.com/masterindex/html/html_wf_761/wf761crlang/source/topic141.htm#djoin1010390) -- join on one of the fields, specify additional equality constraints in WHERE.
2. MDI (multi-demensional index)
- Jack Gross WF through 8.1.05
Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005