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     Problems joining using a defined field

Read-Only Read-Only Topic
Go
Search
Notify
Tools
Problems joining using a defined field
 Login/Join
 
Platinum Member
posted
I am working with Mark and we are trying to resolve an issue with joining two files - we wondered if anyone could help??

I want to join two files F5581 and OBRAPC30 on RA Number. RA Number in F5581 is defined as P7 and RA Number in OBRAPC30 is defined as A14. Therefore I need to create a field from the RA Number in OBRAPC30.

Using OBRAPC30 as the master file is fine. The following code works:

JOIN
OBRAPC30.D_RA_NUMBER WITH OBRAPC30.RA_CUSTOMER_R_A_NUMBER IN OBRAPC30 TO
F5581.RNM_RA_NUMBER IN F5581 AS J0
END
DEFINE FILE OBRAPC30
D_CHAR_TEST/A7 = SUBSTR(14, OBRAPC30.RA_CUSTOMER_R_A_NUMBER, 3, 9, 7, D_CHAR_TEST);
D_RA_NUMBER/P7 = EDIT(D_CHAR_TEST);
END


BUT...I want to use F5581 as the masterfile:

JOIN
F5581.RNM_RA_NUMBER IN F5581 TO
OBRAPC30.D_RA_NUMBER WITH OBRAPC30.RA_CUSTOMER_R_A_NUMBER IN OBRAPC30 AS JO
END
DEFINE FILE OBRAPC30
D_CHAR_TEST/A7 = SUBSTR(14, OBRAPC30.RA_CUSTOMER_R_A_NUMBER, 3, 9, 7, D_CHAR_TEST);
D_RA_NUMBER/P7 = EDIT(D_CHAR_TEST);
END

I get the following error:

0 ERROR AT OR NEAR LINE 72 IN PROCEDURE ADHOCRQ FOCEXEC *
(FOC376) SYNTAX ERROR OR MISSING ELEMENT IN JOIN/COMBINE COMMAND:


Is it possible to do what I am trying to do??

Thanks.


Jane Hendy

This message has been edited. Last edited by: mark66,


WebFocus 765. iSeries v5r4
 
Posts: 175 | Location: England | Registered: April 11, 2006Report This Post
<Special-K>
posted
Are you sure it's the JOIN that's at fault?
Can you show us some of the program near 72 in case it's a fault elsewhere?
 
Report This Post
<Special-K>
posted
Try putting the WITH clause in the DEFINE, see if that works.
 
Report This Post
Platinum Member
posted Hide Post
Thankyou for responding. In fact I'm running a small test program, which just does some SETs before the JOIN, so I think the problem has to be with the JOIN.

I just tried moving the WITH clause to the DEFINE FILE line (i.e. DEFINE FILE OBRAPC30 WITH...) which it didn't like. So I moved it to the SUBSTR line (i.e. ....=SUBSTR(14, OBRAPC30.RA_CUSTOMER_R_A_NUMBER WITH....) which it didn't like either!!

Thankyou anyway.


WebFocus 765. iSeries v5r4
 
Posts: 175 | Location: England | Registered: April 11, 2006Report This Post
<Special-K>
posted
Ah no, I meant try remove the WITH RA_CUSTOMER_R_A_NUMBER clause from your JOIN statement and use WITH in the DEFINE like this

DEFINE FILE OBRAPC30
D_CHAR_TEST/A7 WITH RA_CUSTOMER_R_A_NUMBER= SUBSTR(14, RA_CUSTOMER_R_A_NUMBER, 3, 9, 7, D_CHAR_TEST);
D_RA_NUMBER/P7 WITH RA_CUSTOMER_R_A_NUMBER= EDIT(D_CHAR_TEST);END
 
Report This Post
Platinum Member
posted Hide Post
Thanks, that's great. I've put the code in as you have suggested and have followed it with a PRINT statement and can print any of the fields from F5581, but it doesn't recognise D_RA_NUMBER from OBRAPC30. Also I needed to keep the WITH RA_CUSTOMER_R_A_NUMBER on my JOIN statement.

I don't get any errors from the JOIN statement or PRINT statement, until I try to print D_RA_NUMBER. Maybe it isn't doing the JOIN statement properly??


WebFocus 765. iSeries v5r4
 
Posts: 175 | Location: England | Registered: April 11, 2006Report This Post
Expert
posted Hide Post
Mark, it would be a lot easier for us to help if you put your code in a simplified form , that's why we all use the CAR file, for example.
Your join creates a virtual entity
JOIN HOST TO GUEST
creates a new virtual HOST that is wider, has more fields.
So the field you want to edit using a define is now part of this virtual HOST.
DEFINE FILE HOST
newfield/.. = EDIT (GUEST.OLDFIELD,...)
END
In your sample,
you're DEFINE FILE GUEST
etc
and since the JOIN has already been issued and the virtual HOST has been created, focus is done dealing with the GUEST file, so its saying 'so what?' when you issue a define on this GUEST.
Make sense?




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
Hi Susannah,

This is Mark. My colleague Jane has been using my log on whislt she waits for her login to be authorised. Jane and I have taken over support for WF from our colleagues in Germany and have had very limited training, so please excuse our lack of knowledge and probably some of the basics!!

I will try to describe the problem we have been unable to resolve in simple terms...

We have a master file FileA which we need to join to FileB. Field RA_NUMBER on FILEA needs to join to CUS_RA_NUMBER in FILEB, however the RA_NUMBER is actually the 3rd character onwards in field CUS_RA_Number.

FileA: RA_number = 0123456
FileB: Cus_RA_Number = ZZ0123456

Therefore I think we need to do something like:

JOIN
RA_NUMBER IN FILEA TO
SUBSTR(9,FILEB.CUS_RA_NUMBER,3,9,7, A7) IN FILEB AS JO
END

We need to have FILEA as the master, as we need to report records from FILEA regardless of matching to FILEB.

We have been pointed in the direction of Defining a new field on the Master file, but File A holds no reference to the preceeding characters of CUS_RA_NUMBER (ZZ). Therefore we cannot build this new field before joining.

We have also tried to define a new field, being the substring of CUS_RA_NUMBER in FileB, but this error's with (FOC236) LINKED FILE DOES NOT HAVE A MATCHING KEY FIELD OR SEGMENT.

I have seen others using the CAR file examples and I agree it must be easier for everyone to understand each other if we all quote the same examples. Where can we find these database examples?

Thanks again for everyone's help.


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

Is it possible to change your Master File (Meta data) for File B to have the first two characters as another field? For example, change the CUS_RA_NUMBER into two fields: CUS_12 as A(2) and RA_NUMBER as A(7). I'm not sure if the first two characters are significant if you were using only File B in another report, but it will help in your table join.

Tim J
 
Posts: 57 | Registered: February 24, 2004Report This Post
Expert
posted Hide Post
CAR file and master should have been installed automatically with your focus install
and probably in ibisamp directory.
Joins using a defined field
will work when you use the word WITH in the join
to associate your defined field with some actual exisiting field in the db.
First, edit your focal point profile signature to show us what platform /version you're on, and then tell us what types of databases you are reading?




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
<Mabel>
posted
Hi Mark,

Has this issue been resolved?

If the problem has been resolved, please post in back on the forum so we can share it with other members.

As always, you may contact Information Builders’ Customer Support Services for assistance at any time. The phone number is 1-800-736-6130, or you may access the online system InfoResponse at http://techsupport.ibi.com. Here is a list of information to be ready when you call: http://techsupport.ibi.com/before_you_call.jsp.

Hope this helps.
 
Report 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     Problems joining using a defined field

Copyright © 1996-2020 Information Builders