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!

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.

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

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

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.

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

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