Multiple File Join Download Using IBM i Access Client Solutions Data Transfer

How can I download three or more files through IBM i Access Client Solutions data transfer and return records with missing fields?

Occasionally, you might want to transfer multiple files to a PC file format using data transfer and the Join By option when clicking on the Data Options button and bringing up the Change Data Options pane. In cases where you want to join more than two files and do not want to receive records with missing fields, this method works. In cases where you want to join only two files with or without missing fields, this method also works. However, in cases where you must join three or more files on a download and wish to return records with missing fields, this method does not work. There are no error messages, and the transfer completes normally. However, you do not receive all the records you expect. Data transfer runs as though you had not selected the option to return records with missing fields.

Note: This document is offered as an example of how to perform a multiple file left outer join. Assistance with designing custom transfer requests and SQL select statements is the responsibility of the user.

Because IBM i Access Client Solutions uses the optimized database server, it is based on SQL and follows the same syntax rules. If Return records with missing fields is not selected, an Inner Join is performed by Data Transfer. However, if Return records with missing fields is selected, a Left Outer Join is performed. Data Transfer is not currently able to perform the level of parsing required for such a download of three or more files and, therefore, performs an Inner Join. This has been a permanent restriction since the V3R1M0 version of Client Access for Windows 95/NT. To perform such a download, you must either create a logical file that handles the join or use run the data transfer using native SQL.
 

To use native SQL processing, select the Properties button, click the SQL tab and select the radio button labelled, 'Process SELECT as native SQL'.  Then click the Data Options button and enter your SQL statement to perform a left outer join in the Change SQL Statement window.

Following is an example of an SQL statement that uses LEFT OUTER JOIN to return missing records when joining three files.

SELECT * FROM lib1/file1 as T1 LEFT OUTER JOIN lib2/file2 as T2 on T1.fieldnam=T2.fieldnam LEFT OUTER JOIN lib3/file3 as T3 ON T2.fieldnam=T3.fieldnam

where lib1, lib2, and lib3 are the libraries and file1, file2, and file3 are the files that you wish to download. Additional options are supported in conjunction with the above select statement, for example, WHERE, ORDER BY, and GROUP BY. Refer to the SQL Programming Guide and the SQL Reference for additional information regarding implementation of native SQL.