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.

IN expressions are supported where the target statement is referring to a named state, with the rule that there must be exactly one expression inside the square brackets, which is matched against the target state's primary key. Note that the expression will be evaluated against the filtered record set of the state, not the unfiltered one.

IN syntax

The syntax where the target is a statement is as follows:

[expr1, expr2, …] IN StatementName

The syntax where the target is a state is as follows:

[expr] IN StatementName

The reason for this state syntax is that states, like the collections they filter, always have a single key attribute, and thus IN expressions that refer to them must have exactly one expression inside the square brackets.

Note that sets are supported by IN expressions. If one of the named statement’s group keys is a set, then the corresponding expression in the square brackets must be a set of the same type.

IN example

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
FROM SaleState
GROUP BY CustId
HAVING Value>10000 ;

RETURN Top_HVC_Products AS SELECT
  COUNT(1) AS NumSales
FROM SaleState
WHERE [CustId] IN HighValueCust
GROUP BY ProductName
ORDER BY NumSales DESC
PAGE(0,10)