 |
|||||
|
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
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 WebFOCUS/FOCUS Forum on Focal Point Value for Join 'FROM' Field Out of Sequence
Focal Point Forums WebFOCUS/FOCUS Forum on Focal Point Value for Join 'FROM' Field Out of Sequence Read-Only TopicGo | Search | Notify | Tools |
| Value for Join 'FROM' Field Out of Sequence | Login/Join |
| Member |
Hello, 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 * SET ALL=ON JOIN CNEE AND HBL AND CNTR# IN ACTIVITY TO ALL STECUC AND STEHBL AND STECT# IN RAIL_EVENTS AS J1 END Thanks in advance, Natalija WF- 7.6.2 OS-Windows XP Output - EXL2K | ||
|
| Virtuoso |
Without seeing the original file, try taking the noprint off the BY statements since you are using SUM you don't need to restate the items if you are doing a holdfile. Also, are there multiple records in ACTIVITY for each or is it a one to one relationship? Leah | |||
|
| Virtuoso |
Natalja If you want to join two tables a simple "hold" would not work. Change it to ON TABLE HOLD AS RAIL_EVENTS FORMAT FOCUS INDEX STECUC STEHBL STECT# Or an other format that is the same as the format of your host table. and please update your signature to let us know what version you use.
| |||||||
|
| Expert |
Actually, joining two hold files in fixed format does work although not very efficiently. The target file MUST be sorted in the same manner as the host file. Frank's way is recommended for the target. The host file can remained fixed format. Ginny --------------------------------- Prod: WF 7.7.01 Dev: WF 7.6.9-11 Admin, MRE,self-service; adapters: Teradata, DB2, Oracle, SQL Server, Essbase, ESRI, FlexEnable, Google | |||
|
| Member |
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? Again, thank you all for you help! WF- 7.6.2 OS-Windows XP Output - EXL2K | |||
|
| Virtuoso |
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? Leah | |||
|
| Expert |
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.
| |||||
|
| Virtuoso |
I would also recommend to shorten the name of the hold file. I try to make hold files always as an 8 character file name.
| |||||||
|
| Platinum Member |
In your cross referenced FOCUS file you must concatenate the three fields into one to use them in a join. That's what your new error is telling you. WF 7.7.05 HP-UX - Reporting Server, Windows 2008 - Client, MSSQL 2008, FOCUS Databases, Flat Files HTML, Excel, PDF | |||
|
| Expert |
Which is exactly what I told her to do on the 25th. Ginny --------------------------------- Prod: WF 7.7.01 Dev: WF 7.6.9-11 Admin, MRE,self-service; adapters: Teradata, DB2, Oracle, SQL Server, Essbase, ESRI, FlexEnable, Google | |||
|
| Master |
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 | |||
|
| Platinum Member |
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 | |||
|
| Gold member |
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 | |||
|
| Virtuoso |
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 | |||
|
| Powered by Social Strata |
 | Please Wait. Your request is being processed... |
Read-Only TopicFocal Point Focal Point Forums WebFOCUS/FOCUS Forum on Focal Point Value for Join 'FROM' Field Out of Sequence
Focal Point Forums WebFOCUS/FOCUS Forum on Focal Point Value for Join 'FROM' Field Out of SequenceCopyright © 1996-2020 Information Builders
| View $GS_USERNAME's Public Profile |
| Add $GS_USERNAME to my Ignore List |
| View Recent Posts by $GS_USERNAME |
| Notify me of New Posts by $GS_USERNAME |