IN expressions perform a membership test.
IN expressions address use cases where you want to identify a set of interest, and then filter to records with attributes that are in or out of that set. They are useful in conjunction with HAVING and PAGE expressions.
The syntax is as follows:
[Attr1, Attr2, …] IN StatementName
The example below helps answer the questions, "Which products do my highest value customers buy?" and "What is my total spend with suppliers from which I purchase my highest spend commodities?"
DEFINE HighValueCust AS SELECT SUM(SalesAmount) AS Value GROUP BY CustId HAVING Value>10000 ; RETURN Top_HVC_Products AS SELECT COUNT(1) AS NumSales WHERE [CustId] IN HighValueCust GROUP BY ProductName ORDER BY NumSales DESC PAGE(0,10)