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 |
Description:
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:
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.
