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,

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?

Try putting the WITH clause in the DEFINE, see if that works.

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.

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

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??

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?

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.

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

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?

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.