Print non-matching records only (Outer Join)

Date:Archived
Product/Release:LANSA Client
Abstract:Only the records in a file that do not have matching records in another file can be printed
Submitted By:LANSA Technical Support

Situations may occur where only records from one file that do not have matching records in another file should be printed, for example:

  1. An insurance company wants a list of all customers that have building insurance, but not household insurance.
  2. A sales rep. wants to list all customers that did not make a purchase yet this year.
  3. An IT department wants to clean up its Product Category file and list all product groups that do not have products defined against them.

All of the above examples are a variation of what is called an "outer join" in computer terminology. (Please read the help text of Crystal Reports for LANSA Client for more information about this topic.) Crystal Reports does an outer join by default, meaning that it will print the records of the first file even if there are no matching records in the second file. But to print only the records that do not have match, its necessary to use a formula in the selection criteria.

For example, if the primary file is file A and the objective is to print only the customers in file A that do not have a matching record in file B, use the following formula:

Select Record

The above 3 reporting examples, could be achieved as follows:

  1. The insurance company would
    • Run a LANSA Client query to select all Building policies and save this to fileA.
      Make sure that the AS/400 file being retrieved from has the Customer Id field as the primary key, and that Customer Id is selected in the query.*
    • Run a LANSA Client query to select all Household policies and save this to fileB.
      Make sure that the AS/400 file being retrieving from has the Customer Id field as the primary key and that Customer Id is selected in your query.*
    • Define a link between the 2 files in Crystal's visual file linker based on Customer Id.
      To do this, Crystal's Visual File linker requires that at least one of the files must have the Customer ID as its key.**
    • Define selection criteria as above: IsNull[{fileb.customer}]
  2. The salesman would
    • Run LANSA Client to select all customers (master data) and save this to fileA.
    • Run LANSA Client to select all sales transactions of this year and save this to fileB.
    • Define a link between the 2 files in Crystal's visual file linker based on Customer Id.
    • Define selection criteria as above: IsNull[{fileb.customer}]
  3. The IT department would
    • Run LANSA Client to select all Product groups and save this to fileA.
    • Run LANSA Client to select all Products and save this to fileB.
    • Define a link between the 2 files in Crystal's visual file linker based on Product Group.
    • Define selection criteria as above: IsNull{{fileb.productgrp}]

The problems might seem unrelated, but the solutions are similar.

(By the way, to print matching records only, use the formula: not IsNull[{fileb.customer}]

* LANSA Client will automatically create key fields (an index) for the PC database file it creates, based on the key fields it finds in the AS/400 file you are retrieving from. In order to get the required key field(s) in your PC database file, select the AS/400 key field(s) in the LANSA Client query. If a physical or logical file with the required key field(s) cannot be found on the AS/400, then create keys for the resulting PC file with some special utilities (for example MS Query and Dbase2 utilities). Key fields, file linking and related utilities are not discussed in this document.

** Crystal's Visual File Linker (located under the Database menu bar option) allows the definition of a link between two files (for example file A and B) by simply drawing a line from a field in fileA (e.g. the Customer Id) to the linking field in file B (e.g. the Customer Id ). The field which is being used to link the two files (in our example the Customer Id in file B) has to be a key field. See above note about how to create key fields.