LANSA and SQL Tip : Subselect parameter "ANY"

Date:Archived
Product/Release:LANSA - All Platforms
Abstract:The "ANY" subselect parameter can be used to execute a sub-selection from another file, before carrying out a requested selection
Submitted By:LANSA Technical Support
SOME
ANY
ALL

SOME and ANY work in the same way. They allow the execution of a sub-selection of another file before carrying out a requested selection.

For example, consider the customer and order files for the following values:

Example customer file Example order file

Example:

Select all customers with at least one order :

SELECT_SQL
    FIELDDS((#CUSNUM))
    FROM_FILES((CUST))
    WHERE('CUSNUM = ANY(SELECT CUSNUM FROM ORDHEAD)')
ENDSELECT

The sub-selection defined here will select all customer numbers from file ORDHEAD. Then a condition will be set that the customer number selected from the file CUST matches at least one of the customer numbers from the previously created (sub-selection) table.

The result will be :

Results