WHERE |
Where is used to select records in a table – together with a command to open the table, to scan a table or a filter.
SYNTAX 1
... Where expr_l ...
SYNTAX 2
... Where key id_key [ '(' exp_ind
')' ] = list_val ...
|
Element |
Description |
Restrictions |
|
expr_l |
logic_expression. relating to
: |
|
|
operators |
comparison =, <, >,
<=, >=, <> |
None. |
|
functions |
Alphanumeric, numerical,etc. |
According to type. |
|
Alphanumeric |
left$, right$, mid$, seg$, len, num$, ctrans, tolower, toupper, val, ascii, chr$, instr, pat, string$, space$, mid$, vireblc. |
None. |
|
numerical |
abs, int, ar2, avg, var, prd. |
None. |
|
date |
no function allowed. |
|
|
multi-type |
find, max, min, sigma, uni. |
Except date type. |
|
id_key |
variable_name indicating a key. |
The key must exist. |
|
exp_ind |
Integer expression of the number of parts of the key used. The whole key is used by default. |
The number of expressions must be not more than the number of sub-keys. |
|
list_val |
list of expressions separated by the character “;”. |
The expressions are the same type as the sub-key. |
# Declaration of the file of customers
billed, reduced to French-speaking clients only.
File CLIFAC Where CODLANG = 1
# Composition of a file listing names of
Unix directories present
# in my opening directory, but not
belonging to me.
File (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"
# Link of clients and carrier order tables
reduced only to those
# passed to carriers for which the
charging rate is managed.
Link COMCLI With [TAR]CODTRA = TRANSP As [LNK] Where [TAR]ATARIF =
2
# Filter adding to the previous selection
for finding the
# next client order stored.
Filter [LNK] Where CODCLIF = [L]CLICUR and NUMCCL >
[L]NUMCUR
# Command line paths.
For [LCL]NUMLIG(1) Where NUMCCL =
[CCL]NUMCCL
For [LCL]NUMLIG(2)
...
# Selections of orders for a Client
supplied/Client billed pair.
Filter [CCL] CLICCL(2) = toupper([M]CODCLIF);toupper([M]CODCLIL)
Where is used together with the instructions File, Link, Filter, For or Choose to restrict records in tables implemented by these instructions, by limiting the selection to those meeting the condition used as a parameter. This also speeds up the instructions such as For, Delete, Update, etc., relating to a set of records.
When
a Where clause is applied to a
table (or a set of tables) to which a Whereclause already applied, the new one will reinforce the
previous one. It acts as if a “logic and” has been applied between the two
conditions.
Exception: a Filter instruction cancels the earlier Filter instruction.
The condition is evaluated when and only when the declaration is made, including a For loop situation. For embedded For loops, there can only be one Where clause.
Variables
from the table must not be used as an index of a dimensioned variable on which
the selection will be based. Even if the syntax is approved.
Example :
Filter [CCL] Where [L]CATEG([F:CCL]TYPCOM) = 1
# returns an execution error.
|
Error |
Description |
|
ERPAFON (4) |
Unauthorised function (1st syntax). |
|
PACLE (21) |
Non-existant key (2nd syntax). |
|
ERMODE (10) |
Type of expression not compatible with that of the part of the key (2nd syntax). |
FILE - LINK - FILTER - FOR - CHOOSE - DELETE - UPDATE - ORDER
|
|