B Soft Join
This appendix contains these topics:
B.1 Standard File Join Process
When two or more files are used to create a report using World Writer, the files must be joined by at least one field from each file that contains identical data. World Writer provides two ways to do this:
-
V82102 File Relations - Match Fields. Type matching SEQ numbers to relate the fields.
-
V82103 File Relations - Enter Fields. Type the related field names directly on this screen.
Choose F8 to toggle from V82102 to V82103.
In order to use either of these methods, the fields from each file must be the same data type as defined in Data Dictionary for the data item. The data type is a characteristic of the data that the field contains. Common types are alphanumeric and numeric. You can join alphanumeric to alphanumeric and numeric to numeric using the standard file join methods.
B.2 What is a Soft Join?
In some cases the data in the two fields is identical, but the data types are different. For example, the subledger field in F0911 can contain an address book number. The field GLSBL in F0911 is an alphanumeric field and the ABAN8 in F0101 is a signed numeric field. If you attempt to join these two fields with either of the above methods, the error message 1500, Field Types Not the Same, is received.
A soft join provides a method to join fields of like data but defined as different data types in Data Dictionary. The procedure is to bypass the join screen (unless other joins are required), create a calculated field that converts the numeric field to alphanumeric and set up the actual join between these fields in Data Selection.
Note:
World Writer does not provide a way to convert alphanumeric fields to numeric.B.2.1 Example One
In this example, the desired join between F0911 and F0101 is GLSBL EQ ABAN8. The order of the fields typed into Data Selection does not matter.
If the data selection is done through an OR statement, this join will need to be placed in each part of the OR statement.
As with all file joins, World Writer only retrieves records where the data matches. In this example, any F0911 record where the subledger field is not an address book number or is blank, will be eliminated from the report.
-
To bypass the File Relations screen, press Enter, as needed.
-
On the Output Field Specifications screen, choose Opt. 1 on a blank line to create a field that converts the numeric field to an alphanumeric field.
-
Assign this field a Seq No. and Description.
The size should be the same size the field you are converting.
-
Enter N in the PW column. It does not have to be a printing field on the report.
-
Give the field a unique field name in the Output Field column.
Figure B-1 Output Field Specifications screen
Description of "Figure B-1 Output Field Specifications screen"
-
Press Enter. The Query Result Field Definition is displayed.
Use the DIGITS operand to convert the numeric field to alphanumeric.
Figure B-2 Query Result Field Definition screen
Description of "Figure B-2 Query Result Field Definition screen"
-
Press Enter.
-
If no errors are detected, press enter again to return to Output Field Specifications.
-
Proceed to Data Selection. The actual join is done on this screen.
-
Access the detail area (F4).
-
Page down to a blank line, if needed.
-
Enter the field names to be joined, and press Enter.
Figure B-4 Data Selection (Expanded Details) screen
Description of "Figure B-4 Data Selection (Expanded Details) screen"
This completes the soft join process.
B.2.2 Example Two
When a Transfer Order is entered between Branch/Plants, a sales order record is written to the F4211 and a corresponding purchase order record is written to the F4311. The sales order number from F4211 (SDDOCO) is used to populate the related order number field (PDRORN) in F4311. A soft join is required to join SDDOCO to PDRORN. However, more than just this join may be required to get a one-to-one relationship of records between these files. The other fields needed for the join have matching data types and can be entered through the standard file join methods. The following cover page shows an example of a soft join combined with standard joins.
Figure B-5 Query Version Cover Page screen
Description of "Figure B-5 Query Version Cover Page screen"
Figure B-6 Query Version Cover Page (Expanded Details) screen
Description of "Figure B-6 Query Version Cover Page (Expanded Details) screen"