IN

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)