WHERE

Contents


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

   SYNTAX 1
   ...
Where expr_l ...

   SYNTAX 2
   ...
Where key id_key [ '(' exp_ind ')' ] = list_val ...

 

Parameters

Element

Description

Restrictions

expr_l

logic_expression. relating to :
- any variables
- zones in the file.


None.
Depending on operators or functions.

operators

comparison =, <, >, <=, >=, <>
arithmetic +, -, *, /, ^
logic and, or, not, xor.

None.
Except "-" on chain
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.

 

Examples

   # 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)

 

Description and comments

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.

 

Comments

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.

 

Associated errors

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).

 

Associated key words

FILE - LINK - FILTER - FOR - CHOOSE - DELETE - UPDATE - ORDER


CONTENTS


Copyright © Sage 1999 - 2007