|Powered by QM on a Rpi server|
KnowledgeBase 00045: The IN and NOT.IN Query Processor Selection Elements
This article was originally published as a Tip of the Week.
The query processor has a short form in the selection clause where a list of acceptable values can be included in an implied OR relationship. For example
LIST SALES WITH CUST.NO = 1682 4929 9429 2004Although convenient for queries mechanically constructed within application programs, this gets unmanageble when the list is very long.
The IN Operator
In its simplest form, the IN operator can be used to select records in which a named field contains a value that is also in a field mark delimited record in the $SAVEDLISTS file, typically a stored select list.
LIST SALES WITH CUST.NO IN ACCOUNT.HOLDERSwhere ACCOUNT.HOLDERS is the name of the $SAVEDLISTS record.
When used with alphabetic data, the NO.CASE qualifier can be used to make the selection case insensitive.
LIST EMAILS WITH SENDER IN NO.CASE FRIENDS
A variation on the IN operator allows the list of acceptable items to be located in some other file
LIST SALES WITH CUST.NO IN "SALES.GROUPS NORTHERN"where the quoted item is the filename and record id separated by a space.
The NOT.IN Operator
The NOT.IN operator is the inverse of IN, selecting only items that do not match with entries in the specified list.
LIST EMAILS WITH SENDER NOT.IN NO.CASE FRIENDS