KnowledgeBase 00045: The IN and NOT.IN Query Processor Selection Elements

Last updated: 17 Feb 2017 Applies to: All versions

Search   Top level index       Full Index Search Tips Previous article     Next article

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 2004 

Although 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.HOLDERS 

where 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 

Related Articles

None.

Please tell us if this article was helpful

Very     Slightly     Not at all

Comments

Email (optional)