FD, FDWOL, or FDR - Find to recordset: Difference between revisions
m (→List of primitive tests: add link) |
m (→Retrieval conditions syntax: minor formatting) |
||
(11 intermediate revisions by the same user not shown) | |||
Line 30: | Line 30: | ||
<span class="literal">End Find</span> | <span class="literal">End Find</span> | ||
</p> | </p> | ||
===Syntax terms=== | ===Syntax terms=== | ||
<table | <table> | ||
<tr><th><var>In</var> filespec</th> | <tr><th><var>In</var> filespec</th> | ||
<td>This optional clause is used to establish the file or group context from which the record set is drawn. | <td>This optional clause is used to establish the file or group context from which the record set is drawn. | ||
<ul> | <ul> | ||
<li>If either of the <var>In< | <li>If either of the <var>In <i>inLabel</i></var> or <var>On <i>list</i></var> clauses is specified, | ||
<var>On< | the <var>In <i>filespec</i></var> clause is not allowed, and the file context is the same context | ||
the <var>In< | |||
as that of <var class="term">inLable</var> or <var class="term">list</var>. | as that of <var class="term">inLable</var> or <var class="term">list</var>. | ||
<li>Otherwise, if neither of those clauses is specified: | <li>Otherwise, if neither of those clauses is specified: | ||
<ul> | <ul> | ||
<li>The <var>In< | <li>The <var>In <i>filespec</i></var> clause may be used to specify the file context. If <var>In <i>inRecordSet</i></var> is specified, its file or group context must be the same. | ||
<li>If the <var>In< | <li>If the <var>In <i>filespec</i></var> clause is not specified, there must be a default file or group context for the <var class="product">SOUL</var> request, and that default context is used for the result record set. If <var>In <i>inRecordSet</i></var> is specified, its file context overrides the default file or group context. | ||
context for the <var class="product"> | |||
</ul> | </ul> | ||
</ul> | </ul> | ||
</td></tr> | </td></tr> | ||
<tr><th>...</th> | <tr><th>...</th> | ||
<td>...</td></tr> | <td>...</td></tr> | ||
<tr><th>retrievalConditions</th> | <tr><th>retrievalConditions</th> | ||
<td>This is a specification of the records in the input record set | <td>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 <i>true</i> or <i>false</i> 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 <var class="term">[[#Retrieval conditions syntax|retrievalConditions</var>]] clause. | Each line in the block can contain a <var class="term">[[#Retrieval conditions syntax|retrievalConditions</var>]] clause. | ||
<div id="multi2"></div> | <div id="multi2"></div> | ||
If the form is used with [[#multi|multiple lines]] containing | If the form is used with [[#multi|multiple lines]] containing | ||
<var class="term">retrievalConditions</var>, the result record set contains the conjunction (<var>And</var>) of the | <var class="term">retrievalConditions</var>, the result record set contains the conjunction (<var>And</var>) of the tests specified in each line. | ||
tests specified in each line. | For example: | ||
For example | <p class="code">FD to %mySet RECTYPE='CUST' or RECTYPE='PARTNER' | ||
<p class="code">FD to %mySet RECTYPE = 'CUST' or RECTYPE = 'PARTNER' | STATUS='ACTIVE' | ||
STATUS = 'ACTIVE' | |||
end find | end find | ||
</p> | </p> | ||
The above | The above <var>Find</var> record set is equivalent to: | ||
<p class="code">FD to %mySet (RECTYPE = 'CUST' or RECTYPE = 'PARTNER') | <p class="code">FD to %mySet (RECTYPE='CUST' or RECTYPE='PARTNER') - | ||
And STATUS='ACTIVE' | |||
End find | |||
</p> | </p> | ||
A missing <var class="term">retrievalConditions</var> clause specifies all records in | A missing <var class="term">retrievalConditions</var> clause specifies all records in | ||
the input record set | the input record set. Hence blank lines within the recordset <var>Find</var> block have no effect, for example: | ||
example: | |||
<p class="code">in file SHIPMENTS FD to %mySet | <p class="code">in file SHIPMENTS FD to %mySet | ||
end find | end find | ||
Line 75: | Line 74: | ||
all records (except those excluded due to record security processing) in the | all records (except those excluded due to record security processing) in the | ||
file named <code>SHIPMENTS</code>. | file named <code>SHIPMENTS</code>. | ||
<p> | |||
For the syntax of | For the syntax of <var class="term">retrievalConditions</var>, see [[#Retrieval conditions syntax|Retrieval conditions syntax]]. </p> | ||
<var class="term">retrievalConditions</var>, see [[#Retrieval conditions syntax| | |||
</td></tr> | </td></tr> | ||
</table> | </table> | ||
===Find as the first word of record set Find statements=== | ===Find as the first word of record set Find statements=== | ||
The <var>FD</var>, <var>FDWOL</var>, and <var>FDR</var> | The <var>FD</var>, <var>FDWOL</var>, and <var>FDR</var> | ||
Line 95: | Line 94: | ||
==Retrieval conditions syntax== | ==Retrieval conditions syntax== | ||
As mentioned in the syntax terms for the recordset <var>Find</var> statement, | As mentioned in the syntax terms for the recordset <var>Find</var> statement, the <var class="term">retrievalConditions</var> specify | ||
the <var class="term">retrievalConditions</var> specify | the records in the input record set that are retained in the result record set. | ||
the records in the input record set | <p> | ||
The <var class="term">retrievalConditions</var> consist of a series of <var class="term">primitiveTest</var> | The <var class="term">retrievalConditions</var> consist of a series of <var class="term">primitiveTest</var> | ||
conditions | conditions that are combined in the usual way with <var>And</var>, <var>Or</var>, <var>Not</var>, and parentheses (<b>()</b>): | ||
</p> | |||
<p class="syntax"><span class="squareb">[</span><span class="literal">Not</span><span class="squareb">]</span> <span class="squareb">{</span><span class="term">primitiveTest</span> <span class="squareb">|</span> <span class="literal">(</span><span class="term">retrievalConditions</span><span class="literal">)</span><span class="squareb">}</span> - | <p class="syntax"><span class="squareb">[</span><span class="literal">Not</span><span class="squareb">]</span> <span class="squareb">{</span><span class="term">primitiveTest</span> <span class="squareb">|</span> <span class="literal">(</span><span class="term">retrievalConditions</span><span class="literal">)</span><span class="squareb">}</span> - | ||
<span class="squareb">[</span><span class="squareb">{</span><span class="literal">And</span> <span class="squareb">|</span> <span class="literal">Or</span> <span class="squareb">|</span> <span class="literal">Nor</span><span class="squareb">}</span> <span class="squareb">[</span><span class="literal">Not</span><span class="squareb">]</span> <span class="squareb">{</span><span class="term">primitiveTest</span> <span class="squareb">|</span> <span class="literal">(</span><span class="term">retrievalConditions</span><span class="literal">)</span><span class="squareb">}</span><span class="squareb">] ...</span></p> | <span class="squareb">[</span><span class="squareb">{</span><span class="literal">And</span> <span class="squareb">|</span> <span class="literal">Or</span> <span class="squareb">|</span> <span class="literal">Nor</span><span class="squareb">}</span> <span class="squareb">[</span><span class="literal">Not</span><span class="squareb">]</span> <span class="squareb">{</span><span class="term">primitiveTest</span> <span class="squareb">|</span> <span class="literal">(</span><span class="term">retrievalConditions</span><span class="literal">)</span><span class="squareb">}</span><span class="squareb">] ...</span> | ||
</p> | |||
===List of primitive tests=== | ===List of primitive tests=== | ||
These are the <var class="term">primitiveTest</var> alternatives and shortcuts: | |||
<ul> | <ul> | ||
<li><var class="term">fieldname</var> = [<var>Not</var>] <var class="term">value</var> </li> | <li><var class="term">fieldname</var> = [<var>Not</var>] <var class="term">value</var> </li> | ||
Line 119: | Line 116: | ||
<br/> { [<var>Numerically</var> | <var>Alphabetically</var>] | <br/> { [<var>Numerically</var> | <var>Alphabetically</var>] | ||
<br/> [<var>Eq</var> | = | <var>Ne</var> | ^= | <var>Greater Than</var> | <var>Gt</var> | > | <var>Less Than</var> | <var>Lt</var> < | <= | <var>Ge</var> | >= | <var>Before</var> | <var>After</var>] | <br/> [<var>Eq</var> | = | <var>Ne</var> | ^= | <var>Greater Than</var> | <var>Gt</var> | > | <var>Less Than</var> | <var>Lt</var> < | <= | <var>Ge</var> | >= | <var>Before</var> | <var>After</var>] | ||
<br/> <var class="term">value</var> | <br/> <var class="term">value</var> } | ||
<li><var class="term">fieldname</var> <var>Is</var> [<var>Not</var>] | <li><var class="term">fieldname</var> <var>Is</var> [<var>Not</var>] | ||
<br/> { [<var>Numerically</var> | <var>Alphabetically</var>] | <br/> { [<var>Numerically</var> | <var>Alphabetically</var>] | ||
<br/> { <var>In Range</var> [<var>From</var> | <var>After</var>] <var class="term">value1</var> {<var>To</var> | [<var>And</var>] <var>Before</var>} <var class="term">value2</var> | <br/> { <var>In Range</var> [<var>From</var> | <var>After</var>] <var class="term">value1</var> {<var>To</var> | [<var>And</var>] <var>Before</var>} <var class="term">value2</var> | ||
<br/> | | <br/> | | ||
<var>Between</var> <var class="term">value1</var> <var>And</var> <var class="term">value2</var> } </li> | |||
<li><var>File$</var> <var class="term">filename</var> </li> | <li><var>[[Record retrievals#FILE$ condition|File$]]</var> <var class="term">filename</var> </li> | ||
<li><var>Find$</var> <var class="term">label</var> </li> | <li><var>Find$</var> <var class="term">label</var> </li> | ||
Line 142: | Line 137: | ||
<li><var>Sfl$</var> <var class="term">value</var> </li> | <li><var>Sfl$</var> <var class="term">value</var> </li> | ||
</ul> | </ul> | ||
In the above, <var class="term">value</var> | <p> | ||
In the above list, <var class="term">value</var> options are: </p> | |||
<ul> | <ul> | ||
<li>Literal number or string </li> | <li>Literal number or string </li> | ||
<li> | <li><var class="term">%variable</var> </li> | ||
<li><var class="term"> | <li><var>[[Basic SOUL statements and commands#Processing a VALUE IN clause|Value [In]</var>]] <var class="term">label</var> </li> | ||
</ul> | </ul> | ||
===Omitting repeated first words=== | ===Omitting repeated first words=== | ||
If a sequence of <var class="term">primitiveTest</var>s in a particular retrieval condition all have | If a sequence of <var class="term">primitiveTest</var>s in a particular retrieval condition all have | ||
the same first word, that word can be omitted from the latter <var class="term">phrase</var>s. For | the same first word, that word can be omitted from the latter <var class="term">phrase</var>s. For example: | ||
example: | |||
<p class="code">list$ A and not list$ B | <p class="code">list$ A and not list$ B | ||
</p> | </p> | ||
Line 172: | Line 167: | ||
<p class="code">A = 3 or 5 or 40 | <p class="code">A = 3 or 5 or 40 | ||
</p> | </p> | ||
===Use of parentheses=== | ===Use of parentheses=== | ||
As indicated in the [[#Retrieval conditions syntax|Retrieval conditions syntax]], | As indicated in the [[#Retrieval conditions syntax|Retrieval conditions syntax]], parentheses can be placed around any sequence of <var class="term">phrase</var>s to clarify the condition or force the evaluation to occur in a particular order. For example: | ||
parentheses can be placed around any sequence of <var class="term">phrase</var>s to clarify the | |||
condition or force the evaluation to occur in a particular order. For example: | |||
<p class="code">not (A = 2 or list$ Y) | <p class="code">not (A = 2 or list$ Y) | ||
A = 1 and (B = 2 or C = 3) | A = 1 and (B = 2 or C = 3) | ||
</p> | </p> | ||
===Point$=== | ===Point$=== | ||
The <var>Point$</var> condition should be used when ranges of record numbers must | The <var>Point$</var> condition should be used when ranges of record numbers must be retrieved. If individual record numbers need to be retrieved, the <var>[[Record loops#FOR RECORD NUMBER processing|For Record Number]]</var> statement should be used. | ||
be retrieved. If individual record numbers need to be retrieved, the <var>[[Record loops#FOR RECORD NUMBER processing|For Record Number]]</var> statement should be used. | |||
The format of <var>Point$</var> is: | The format of <var>Point$</var> is: | ||
<p class="syntax"><span class="literal">Point$</span> <span class="term">n</span></p> | <p class="syntax"><span class="literal">Point$</span> <span class="term">n</span> | ||
A record satisfies the <var>Point$</var> condition if its record number is greater than or | </p> | ||
For example, the following statement: | A record satisfies the <var>Point$</var> condition if its record number is greater than or equal to <var class="term">n</var>. | ||
For example, the following statement retrieves all records that have internal record numbers between 1500 and 2499, inclusive: | |||
<p class="code">fd to %recSet | <p class="code">fd to %recSet | ||
point$ 1500 and not point$ 2500 | point$ 1500 and not point$ 2500 | ||
end find | end find | ||
</p> | </p> | ||
==Frequently made mistakes== | ==Frequently made mistakes== | ||
Line 201: | Line 194: | ||
<p>Also see the [[Basic SOUL statements and commands#Find statement|introduction to the Find statement]].</p> | <p>Also see the [[Basic SOUL statements and commands#Find statement|introduction to the Find statement]].</p> | ||
[[Category: SOUL]] |
Latest revision as of 17:12, 8 May 2018
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.
|
---|---|
... | ... |
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, 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.