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.
Hi all! This is my first post on this forum and I am very new to WebFocus, so please go easy
I am trying to join two AS400 files, but the field definitions are different. Below is an example with field size and data example:
FILE A Field Name = Parcel(A12) = ' 123456'
FILE B Field Name = Parcel (A7) = ' 123456'
I have tried different combinations of TRIM and SUBSTR but they do not seem to work and I am not sure if one is allowed to use SUBSTR in a Join statement with WebFocus. So something like this:
JOIN SUBSTR(12, FILEA.PARCEL, 6, 12, 7, 'A7') IN FILEA TO FILEB.PARCEL IN FILEB AS J1 END
But as this is not working, I have temporarily fixed the problem by building a Hold file over my selection from File A, creating a new field that is an extract of the last 7 digits from Parcel. I then join this Hold file to File B using this new 'sub-stringed' field.
However I am hoping that there is a more efficient way!!
Many thanks in advance!
WebFocus 765. iSeries v5r4
Posts: 175 | Location: England | Registered: April 11, 2006
the concept you want to look up in your manuals is DEFINE-BASED JOINS. you'll define a field in the host that matches the field in the guest file and then issue the JOIN using WITH to hook the defined field to a key field in the host.
In Focus since 1979///7706m/5 ;wintel 2008/64;OAM security; Oracle db, ///MRE/BID
Posts: 3811 | Location: Manhattan | Registered: October 28, 2003
What you can do is use a virtual field by using DEFINE
JOIN FILEA.D_FIELD WITH FILEA.FILEA.PARCEL IN FILEA TO FILEB.FILEB.PARCEL IN FILEB AS J0 DEFINE FILE FILEA D_FIELD/A7=SUBSTR(12,PARCEL,6,12,7,D_FIELD); END
D: WF 7.6.2 P. : WF 7.6.2 on W2003 ------------------------------------------------------------------ I see myself as an intelligent, sensitive human, with the soul of a clown which forces me to blow it at the most important moments.
However I am sorry to report that I have not been able to get it to work! I have read through lots of the online help and tried many variations of Pete's example but still no luck.
Here is Pete's example exactly how it translates in my real world database:
JOIN F5518.D_FIELD WITH F5518.DNB_DESPATCH_NUMBER IN F5518 TO F565C.ADN_ADVICE_NUMBER IN F565C AS JO DEFINE FILE F5518 D_FIELD/A7 = SUBSTR(12, F5518.DNB_DESPATCH_NUMBER, 6, 12, 7, D_FIELD); END
From this I get the error:
0 ERROR AT OR NEAR LINE 7 IN PROCEDURE ADHOCRQ FOCEXEC * (FOC376) SYNTAX ERROR OR MISSING ELEMENT IN JOIN/COMBINE COMMAND: (FOC1517) UNRECOGNIZED COMMAND D_FIELD/A7 = SUBSTR(12, F5518.DNB_DESPATCH_NUMBER, 6, 12, 7, D_FIELD); 0NO DEFINED FIELDS
Can you see what is wrong with the above code?
Also.....In my searching of the online help I found a command ‘? DEFINE‘ to see what definitions have been created. I removed the join from the code to test the Define and got the following: