Where

Use Where to add a filter:
* In a table declared by File.
* On a join declared by Link.
* On an update triggered by Update or Delete.
* On a read loop executed with For.

Syntax

 SYNTAX 1... Where Key KEY_IDENTIFIER = EXPRESSION_LIST... Where Key KEY_IDENTIFIER(INDEX_EXPRESSION)= EXPRESSION_LISTSYNTAX 2... Where EXPRESSION

When using the syntax 2:

The supported operators and functions are the following:

ElementDescriptionStatus and restrictions
Comparison operators=, <, >, <=, >=, <>.Supported.
Arithmetic operators+,-,*,/,^.Supported, except for '-' on string values.
Logical operatorsand, or, not, xor.Supported.
String functionsleft$, right$, mid$, seg$, len, num$, ctrans, tolower, toupper, val, ascii, chr$, instr, pat, string$, space$, mid$, vireblc.Supported.
Numeric functionsabs, int, ar2, avg, var, prd, pat.Supported. See comments on pat function.
Date functionsNot supported.
Multitype functionsfind, min, max, uni.Supported except for date values. See also comments on find.
Special casesevalue.Supported, but the evaluation is not done as it would be the case in an assignment: the expression is analyzed and transmitted as a 'where' clause. If the parameter transmitted to evalue is an array of strings, a global concatenation is done on all the indexes.

Examples

 # Select only the customer in category ALocal File CUSTOMER Where CATEGORY="A"# On UNIX, select the files (not the directories) present in my home directory that don't belong to meFile (D,L,U,G,T,M,J,A,N) From System "ls -l"-getenv$("HOME")& As [LSL] Where P <> getenv$("LOGNAME") and left$(D,1) = "d"# Join between sales orders and inactive carriers# Then select only the lines concerning a finite customer list given by an arrayLink SALESORDER With [CARR]CARRIER = [ORDER]CARRIER As [LNK] Where [CARR]ACTIVE = 1Filter [LNK] Where find (CUSTOMER,[L]CUST_LIST)<>0# Select a set of sales orders, and then loop on the lines.# [ORDLIN] is the order line table. It has a 2 components key called NUMLIG defined by order;order lineFor [ORDLIN]LINE(1) Where find(ORDNUM,[L]ORDER_LIST)<>0# Here an order is selectedFor [ORDLIN]NUMLIG(2)# Here the lines of the selected order are read in a loopNext : # Loop on the linesNext : # Loop on the orders# Evaluated WhereLocal Char CONDITION(200)(1..5)If ITEM_ACTIVE=2CONDITION(1)="ACTIVE=2 and (SUPPORT_DATE>=date$ or SUPPORT_DATE=[0/0/0])"ElseCONDITION(1)="(1=1)" : # Always trueEndif# Second condition: POLICY=1 if SERIE=2, POLICY=2 if LOT=2, POLICY=3 if SUBLOT=2, POLICY=0 otherwiseCONDITION(2)=" & POLICY="+num$(find(1,SERIE=2,LOT=2,SUBLOT=2))# Third condition is given by an entry pointCall ENTRY_POINT(CONDITION(3))If CONDITION(3)="" : CONDITION(3)="& (1=1)" : EndifFilter [ITEMS] Where evalue(CONDITIONS)

Description

Use Where with File, Link, Filter, and For to filter the records sent, and with Update and Delete to manage all the records fulfilling the conditions.

Several Where clauses added by different instructions on the same table are combined by a logical and. A Filter instruction executed after a previous Filter instruction on the same table will replace the filter.
For example:

 Local File CUSTOMER [CUST] Where ACTIVE=2Filter [CUST] Where COUNTRY="US"Link [CUST] With [CATEG]CATCOD=[CUST]CATEG As [CC] Where [CUST]CATEG>="B"For [CC] Where PAYTERM="CHQ"# Here, the condition that applies are ACTIVE=2 and COUNTRY="US" and CATEG>="B" and PAYTERM="CHQ"NextFor [CUST] Where PAYTERM="CHQ"# Here, the condition that applies are ACTIVE=2 and COUNTRY="US" and PAYTERM="CHQ"NextFor [CUST]# Here, the condition that applies are ACTIVE=2 and COUNTRY="US"NextFilter [CUST]For [CUST]# Here, the condition that applies are ACTIVE=2Next

When nested For loops are executed, a unique clause Where is allowed on the main loop.

The functions not supported are managed by the engine.
For example:

 Local File CUSTOMERS [CUST]# This table has the following columns:# - MANAGER, BUYER, PAYTERM , CATEGORY are string values# - TRESHOLD is a denormalized array of 5 numeric values (0 to 4)# - RANK is an integer value that goes from 1 to 10# - LAST_REMINDER and FIRST_REMINDER are date values# Local variables are used as well:# - PAYMENT_TERM is a character string# - ALLOWED_RANKING is a integer array (10 indexes, every index value is 1 or 2)Filter CUSTOMERS Where& ([CUST]PAYTERM=[L]PAYMENT_TERM or [CUST]MANAGER<>[CUST]BUYER)& and find([CUST]CATEGORY,[L]CATEGORY_LIST)<>0& and sum([CUST]TRESHOLD(1..3))>1000& and [CUST]LAST_REMINDER-[CUST]FIRST_REMINDER>=5& and [L]ALLOWED_RANKING([CUST]]RANK)=2

In the example provided above:

The fifth filter is different. It is preferable to write the following code to implement this kind of filter:
Local Char RANKING_LIST(250)Local Integer IFor I=1 to dim([L]ALLOWED_RANKING)If [L]ALLOWED_RANKING(I)=2RANKING_LIST+=","+num$(I)EndifNext IIf RANKING_LIST<>""RANKING_LIST="find([CUST]RANK"+RANKING_LIST+")<>0"ElseRANKING_LIST="(1=0)"Endif# Now we have a find([CUST]RANK,value1,value2...valueN)<>0 condition that is evaluated as an "in" by databaseFilter [CUST] Where evalue(RANKING_LIST)

Comments

Performance considerations

To obtain the best performance, it is always preferable to send as many filters as possible to the database because a filtering by the engine is always less effective.

Also, using the or operator can lead to performance issues. For example, if RANK is an integer column that can have values between 1 and 10: RANK=4 or RANK=5 or RANK=6 or RANK=7 is less effective than find(RANK,4,5,6,7)<>0, which is less effective than RANK>=4 and RANK<=7. This might depend on the database optimizations.

The pat and the find functions are transmitted to the database only if they are followed by an (equal to 0) or (different from 0) condition:

For example, Where pat(CODE,"*AB*")<>0 is sent to the database as a code like '%AB%' filter, while Where pat(CODE,"*AB*") is managed by the engine and therefore much less effective. All the lines are sent by the database to the engine and filtered.

Find with multi-dimension columns

Take care of the way find works on columns that are arrays. Let's imagine that you have a column MYCOL that has 3 occurrences in the database:

Tables with a big number of columns

If your table has more than 255 columns, take care that the columns with a rank over 255 cannot be used in a Where clause. To count the number of columns, you have to consider the dimensions. For instance, a field MYLIST with a dimension of 3 is considered as 3 columns in the table (MYLIST_0, MYLIST_1, MYLIST_2).

Associated errors

Error codeDescription
4Function not authorized (syntax 2).
10Type of expression incompatible with the key segment type (syntax 1).
21Key does not exist (syntax 1).