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     Joining using a partial field (substring)

Read-Only Read-Only Topic
Go
Search
Notify
Tools
Joining using a partial field (substring)
 Login/Join
 
Platinum Member
posted
Hi all! This is my first post on this forum and I am very new to WebFocus, so please go easy Smiler

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, 2006Report This Post
Expert
posted Hide Post
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, 2003Report This Post
Platinum Member
posted Hide Post
Mark66,


What you can do is use a virtual field by using DEFINE

for example

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

Peter


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.

-Jim Morrison-

 
Posts: 206 | Registered: February 25, 2005Report This Post
Platinum Member
posted Hide Post
Thanks Susannah / Pete,

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:

DEFINE FILE F5518
D_FIELD/A7 = SUBSTR(12, F5518.DNB_DESPATCH_NUMBER, 6, 12, 7, D_FIELD);
END
? DEFINE
-EXIT

0FILE FIELD NAME FORMAT SEGMENT VIEW TYPE
F5518 D_DNB A7 1 MASTER
F5518 D_FIELD A7 1

What is the field D_DNB? Is this a new field that should be referred to in the join?

Thanks again for your help, it is most appreciated Smiler


WebFocus 765. iSeries v5r4
 
Posts: 175 | Location: England | Registered: April 11, 2006Report This Post
Platinum Member
posted Hide Post
Mark,

I am not a regular FOCUS programmer. But from whatever I know the problem seems to be that it is not recognizing the field because it is defined after the join.

It makes sense because of FOCUS being an interpretive language. Try to move the define before the join or add the define field to the MFD.


WF7.1.4 Prod/Test, MRE, self serve, DM
 
Posts: 176 | Location: Desplaines | Registered: August 05, 2004Report This Post
Guru
posted Hide Post
The DEFINE does go after the JOIN... but you are missing and END statement. Whenever a JOIN statement won't fit on one line you need the END.

quote:
JOIN
F5518.D_FIELD WITH F5518.DNB_DESPATCH_NUMBER IN F5518 TO
F565C.ADN_ADVICE_NUMBER IN F565C AS JO
END
DEFINE FILE F5518
D_FIELD/A7 = SUBSTR(12, F5518.DNB_DESPATCH_NUMBER, 6, 12, 7, D_FIELD);
END


ttfn, kp


Access to most releases from R52x, on multiple platforms.
 
Posts: 346 | Location: Melbourne Australia | Registered: April 15, 2003Report This Post
Platinum Member
posted Hide Post
quote:
Originally posted by Piipster:
The DEFINE does go after the JOIN... but you are missing and END statement. Whenever a JOIN statement won't fit on one line you need the END.
Brilliant!! Thank you all very much for your input, got there in the end Smiler


WebFocus 765. iSeries v5r4
 
Posts: 175 | Location: England | Registered: April 11, 2006Report 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     Joining using a partial field (substring)

Copyright © 1996-2020 Information Builders