FD, FDWOL, or FDR - Find to recordset

From m204wiki
(Redirected from Find Records statement)
Jump to navigation Jump to search

This page presents the complete syntax and explanation of the Find statement (abbreviated FD) along with the important Find Without Locks (FDWOL) and Find And Reserve (FDR) variants.

Also see the introduction to the Find statement.

Syntax

The FD, FDWOL, or FDR statement introduces a recordset Find block, terminated by End Find. These statements process an input recordset, applying retrieval conditions to create a result recordset. The retrievalConditions can form the bulk of the statement, and can be placed either on the FD, FDWOL, or FDR line, or inside the block, allowing for multiple non-continued lines of retrievalConditions. In order to explain the processing when they are on multiple lines, two syntax tables are provided.

First, if all of the retrieval conditions fit on one line, the block contains two lines:

[outLabel] [In filespec] {FD | FDWOL | FDR} - [To %outRecordSet] - [In {inLabel | inRecordSet} | On [List] list] - [For Which | With] - [retrievalConditions] End Find

(The first line of the block, displayed above with continuations, is everything prior to End Find).

Second, if multiple lines are used for the retrieval conditions, the syntax is:

[outLabel] [In filespec] {FD | FDWOL | FDR} ... as above ... [retrievalConditions] retrievalConditions ... End Find

Syntax terms

In filespec This optional clause is used to establish the file or group context from which the record set is drawn.
  • If either of the In inLabel or On list clauses is specified, the In filespec clause is not allowed, and the file context is the same context as that of inLable or list.
  • Otherwise, if neither of those clauses is specified:
    • The In filespec clause may be used to specify the file context. If In inRecordSet is specified, its file or group context must be the same.
    • If the In filespec clause is not specified, there must be a default file or group context for the SOUL request, and that default context is used for the result record set. If In inRecordSet is specified, its file context overrides the default file or group context.
... ...
retrievalConditions This is a specification of the records in the input record set that are retained in the result record set. Each primitive clause is a test which is applied against each record in the input record set, producing a true or false result. These clauses are combined with Boolean set operations to produce the overall test for the records in the result set.

Each line in the block can contain a retrievalConditions clause.

If the form is used with multiple lines containing retrievalConditions, the result record set contains the conjunction (And) of the tests specified in each line. For example:

FD to %mySet RECTYPE='CUST' or RECTYPE='PARTNER' STATUS='ACTIVE' end find

The above Find record set is equivalent to:

FD to %mySet (RECTYPE='CUST' or RECTYPE='PARTNER') - And STATUS='ACTIVE' End find

A missing retrievalConditions clause specifies all records in the input record set. Hence blank lines within the recordset Find block have no effect, for example:

in file SHIPMENTS FD to %mySet end find

In the above example, %mySet will contain all records (except those excluded due to record security processing) in the file named SHIPMENTS.

For the syntax of retrievalConditions, see Retrieval conditions syntax.

Find as the first word of record set Find statements

The FD, FDWOL, and FDR statements are actually abbreviations and can be specified in a longer form:

FD Find [All Records]
FDWOL Find Without Locks [All Records]
FDR Find And Reserve [All Records]

Just as the block introduced by the statements is called the recordset Find block, these statements are collectively referred to as the recordset Find statement.

Retrieval conditions syntax

As mentioned in the syntax terms for the recordset Find statement, the retrievalConditions specify the records in the input record set that are retained in the result record set.

The retrievalConditions consist of a series of primitiveTest conditions that are combined in the usual way with And, Or, Not, and parentheses (()):

[Not] {primitiveTest | (retrievalConditions)} - [{And | Or | Nor} [Not] {primitiveTest | (retrievalConditions)}] ...

List of primitive tests

These are the primitiveTest alternatives and shortcuts:

  • fieldname = [Not] value
  • fieldname Like pattern
  • fieldname Is [Not] {Present | Like pattern}
  • fieldname Is [Not]
       { [Numerically | Alphabetically]
          [Eq | = | Ne | ^= | Greater Than | Gt | > | Less Than | Lt < | <= | Ge | >= | Before | After]
          value }
  • fieldname Is [Not]
       { [Numerically | Alphabetically]
          { In Range [From | After] value1 {To | [And] Before} value2
             | Between value1 And value2 }
  • File$ filename
  • Find$ label
  • List$ listname
  • Location$ location | =
  • Point$ value
  • Sfge$ value
  • Sfl$ value

In the above list, value options are:

  • Literal number or string
  • %variable
  • Value [In] label

Omitting repeated first words

If a sequence of primitiveTests in a particular retrieval condition all have the same first word, that word can be omitted from the latter phrases. For example:

list$ A and not list$ B

can be written:

list$ A and not B

And:

X is 13 or X is less than 7

can be written:

X is 13 or is less than 7

Also, if omitting the first word, duplicated equal signs can be omitted. For example, the expression:

A = 3 or A = 5 or A = 40

is equivalent to:

A = 3 or 5 or 40

Use of parentheses

As indicated in the Retrieval conditions syntax, parentheses can be placed around any sequence of phrases to clarify the condition or force the evaluation to occur in a particular order. For example:

not (A = 2 or list$ Y) A = 1 and (B = 2 or C = 3)

Point$

The Point$ condition should be used when ranges of record numbers must be retrieved. If individual record numbers need to be retrieved, the For Record Number statement should be used. The format of Point$ is:

Point$ n

A record satisfies the Point$ condition if its record number is greater than or equal to n. For example, the following statement retrieves all records that have internal record numbers between 1500 and 2499, inclusive:

fd to %recSet point$ 1500 and not point$ 2500 end find

Frequently made mistakes

No Where keyword The For Each Record statement can contain a Where clause. The equivalent (and optional) keyword in the Find statement is For Which or With.

Also see the introduction to the Find statement.

Retrieved from "https://m204wiki.rocketsoftware.com/index.php?title=FD,_FDWOL,_or_FDR_-_Find_to_recordset&oldid=115168"