SEQUEL Design View - AutoJoin

AutoJoin is a feature in SEQUEL ViewPoint that automatically includes JOIN criteria into a new SEQUEL view. It's designed for end users who may not be familiar with database structure - who would benefit from a little assistance specifying file JOIN criteria in their SEQUEL views. Seasoned SEQUEL users will also benefit from AutoJoin as it quickly constructs JOIN paths for often-used files.

In the AutoJoin example below, we'll first take a look at how the AutoJoin function appears to an end user, and then review how AutoJoin should first be set up by an 'administrator' or 'power user'.

What the End User Sees - Enabling AutoJoin

Versions of SEQUEL Viewpoint that allow AutoJoin will contain a checkbox for this function in the Design options dialog window. From any Viewpoint window select Options > ViewPoint Options, then click the Design tab to display the Design options. Make sure the Enable auto joining checkbox is checked (below) to enable this function.

What the End User Sees - Designing a View

After the AutoJoin function is enabled at the workstation, start a ViewPoint Design View session. The first step is to enter the desired file names in the FROM clause. Next, click on the Format SQL Statement button (below) or click on the Selected Files & Fields tab (below the Format SQL button).

You're done - that's all there is to it! ViewPoint automatically includes the JOIN criteria for the selected files (below). From there, you can continue the view design by selecting fields and any additional specifications

Within the Selected Files and Fields tab the JOIN relationships are shown graphically with a line from one join field to the next (center portion of image below).

Preliminary Setup

AutoJoin relies on information stored in the SQLJOINDB file in the SEQUEL library. When SEQUEL software is shipped this file contains no records. A SEQUEL command called LOADJOINDB analyzes any existing SEQUEL views at your site to determine JOIN criteria and populates the SQLJOINDB file with this information.

Alternately, you may first choose to create a Join Template Master view similar to the example shown below (using your own file and field names). Include as many files as you think necessary. You may then name this view explicitly in the LOADJOINDB command parameters. This is the preferred approach, as an administrator can very specifically state the preferred JOIN criteria for each set of files. All this is transparent to the end user - they can simply rely on AutoJoin to correctly link each file.

SELECT "Join Template Master" 
FROM custmast, invmast, ordhead, ordline,
     partmast, saleshist, salmast, vendmast, 
     phonelog 
JOIN salno.01=salno.07 AND cusno.01=cusno.02 
     AND cusno.01=cusno.06 AND cusno.01=cusno.09 
     AND cusno.01=cusno.03 AND invno.02=invno.03 
     AND cusno.02=cusno.03 AND cusno.02=cusno.06 
     AND cusno.02=cusno.09 AND cusno.03=cusno.06 
     AND ordno.03=ordno.04 AND cusno.03=cusno.09 
     AND prdno.04=prdno.06 AND prdno.04=prdno.05 
     AND prdno.05=prdno.06 AND cusno.06=cusno.09 
     AND sgvnd.05=vendno.08