Robelle Tech: Many To Many Links With Suprlink

Robelle Sponsor Message

March 2003

Get the most out of your Robelle Tools

Many To Many Links With Suprlink

By Neil Armstrong

Over the years, many customers have come to us asking for the ability to do many-to-many links, instead of just one-to-one and many-to-one. François Desrochers, wrote a script to use Suprtool to achieve this, but it involved multiple passes over the same file. While it was an ingenious solution, we still were not happy with the performance.

In the office, Bob Green was not looking very happy. After having spent the past year focused on the management of Robelle and only occasionally getting a chance to program with Qedit, I could tell what Bob needed: some programming time.

I assigned him the task of getting many-to-many working over the Christmas holidays. Bob dug in, and the result is that Suprlink can now join files together that have multiple key value matches in each file.

Suprlink has traditionally been able to link an Input file containing many records with the same key to a Link file that has a single record per key value. The Join command will now link two files with many occurrences of the key value in the input file and many occurrences in the “Linking” file. The syntax of the Join command is exactly the same as for the Link command, so a sample task would look as follows:

+input ordhist
+join orders
+output custord
+xeq

The above task will link multiple records of the file ordhist, to the multiple records of the file in orders. This assumes that the files are sorted by a common key. In SQL terms this is known as an Inner Join.

An Outer Join, one where the keys do not necessarily have a match can be achieved by adding the “optional” keyword to the Join command:

+input ordhist
+join orders optional
+output joined
+xeq

In SQL parlance, once again you can achieve both a Left Outer Join and Right Outer Join by reversing the order of the files, between the input and the join commands. To give you an example of how the Join operation would work consider the following data. First we have an inventory file with multiple records for the same product-no. This data is stored in the file dinv:

50512001 {Rest of inventory data}
50512001 {Rest of inventory data}
50512003 {Rest of inventory data}

The next file will have sales records, once again with multiple key values, this data is stored in the file dsales:

50512001 {Rest of sales data}
50512001 {Rest of sales data}

Assume you did the following task, with both files sorted by the product-no:

+in dinv
+join dsales
+out invsales
+xeq

The resulting file would have four records, with the multiple matching dinv and dsales records. The record layout would have the dinv information first followed by the dsales information.

If you add the optional keyword on the join command the resulting file would have 5 records. The matching 4 records from dinv and dsales as well as the dinv record that did not match with the numeric fields set to zero and the byte fields set to spaces. Only one Join operation is allowed per task.

By default, Suprlink will join files base on the primary sorted key in the self-describing file. You can specify a secondary key for the files to be joined on in a similar manner to how the Link command did:

+in orders
+join dsales by order-no product-no
+out ordsales
+xeq

You can combine the Join command with several Link commands if you like. Remember, the Join operation is done first (to determine how many output records there will be). Then the Link commands are done to fill out additional columns in those records, or to drop the record if the Link is not found (and Optional is not specified).

Our performance measurements have shown that the Join command is a minimum of 36 times faster than the command file solution. This new feature is available as of Suprtool version 4.6.03.