LANSA and SQL Tip : Auto-Join

Date:Archived
Product/Release:LANSA - All Platforms
Abstract:SQL can be used to select records from a file based on comparing values of records within the same file.
Submitted By:LANSA Technical Support

Sometimes records need to be selected from a file based on comparing values of records within the same file.

Here is an example which will display all customers who have a higher discount percentage than the company Superdoe (in Veenendaal).

Attention: The table KLANT will be joined with.....ITSELF.

The information consists of a customer file containing the following data:

Example

The SQL for the 'Auto-Join is:

SELECT_SQL
    FIELDS((#KLTNAM 'K2.KLTNAM'))
    FROM_FILES((KLANT K1) (KLANT K2))
    WHERE('K1.KLTNAM=''Superdoe'' AND
        K2.KLTKRT > K1.KLTKRT')
ENDSELECT

The SELECT_SQL command above will result in the selection of the companies Bouwman & Zn. and Gebr.Bever.