Filter
Use Filter to select the lines from a table previously opened and to specify an order to get the results.
Filter [CLASS]Filter [CLASS] Where EXPRESSIONFilter [CLASS] Order By ORDERBY_EXPFilter [CLASS] Where EXPRESSION Order By ORDERBY_EXPCLASS is the abbreviation of the table in which the filter is set.EXPRESSION is a filtering expression. For more information, see the Where documentation.ORDERBY_EXP is an ordering expression syntax. For more information, see the Order By documentation. # Let's read the sales order lines in the order of its index ORDLIN (ordernumber,orderline)# Only for the orders having a type equal to 1 between 2 datesLocal File SALESORDER [SORDER] Where ORDTYP=1Filter [SORDER] Where ORDDATE>= START_DATE and ORDDATE<=END_DATEFor [SORDER]ORDLIN(1)For [SORDER]ORDLIN(2)...NextNext# Now let's start again for all the orders having a type equal to 1Filter [SORDER]For [SORDER]ORDLIN(1)For [SORDER]ORDLIN(2)...NextNext# Let's delete all the order for a given customerFilter [SORDER] Where CUSTOMER=[L]CUSTOMER_TO_DELETEDelete [SORDER]ORDNUM >= "" : # All the orders that fulfills the previous filter are concernedFilter [SORDER] : # Cancels the selectionFilter, in association with the Where clause, is used to select records from a table previously opened. This speeds up access to the data in this table.
The access key can also be changed, using the Order By clause. This new key can be either an existing key or a new one defined in the Order By clause.
If the table was opened with a Where clause, Filter combines the two selection expressions with an and.
If a Filter instruction follows another Filter, it cancels and replaces the first instruction. To cancel a filtering expression set by a Filter instruction, use the syntax Filter [abv] without the Where clause.
If a table opened in a script is used without a new declaration in a subroutine called by Call, a filter that is set on the table during the execution of the Call remains active after the end of the call. If it is redeclared (with the syntax File TABLENAME and not with File [ABBREVIATION]), the Filter condition is local and is not returned.
The following example illustrates this:
Local FILE CUSTOMER [CUST]Filter [CUST] Where CATEGORY="A"Call NEW_FILTFor [CUST]# Here, the filter has been changed by the NEW_FILT Subprog and is CATEGORY="B"...NextFilter [CUST] Where CATEGORY="A"Call LOCAL_FILTFor [CUST]# Here, the filter has not been changed by the LOCAL_FILT Subprog and is CATEGORY="A"...NextEndSubprog NEW_FILTLocal File [CUST]: # This is optional (it just checks that the table is opened)Filter [CUST] Where CATEGORY="B"For [CUST]# Here, the filter has been changed and is CATEGORY="B"..NextEndSubprog LOCAL_FILTLocal File CUSTOMER [CUST] : # Here the table is reopened, the filter that follows will last only for the SubprogFilter [CUST] Where CATEGORY="B"For [CUST]# Here, the filter has been changed and is CATEGORY="B"..NextEndFilter, the variable currind in the [G] class associated with the table is set to 1.In terms of performance, the selection can be made on opening the table, or by Filter. This latter syntax gives more flexibility to writing routines since it can be changed or cancelled at any time.
General restrictions relating to Where and Order By clauses apply when they are used with Filter.
| Error code | Description |
|---|---|
| 7 | The abbreviation does not correspond to an opened table. |
Where, Order By, Filter, For, Trbegin, Link, Read, Delete, Update, currind.