FD, FDWOL, or FDR - Find to recordset: Difference between revisions

From m204wiki
Jump to navigation Jump to search
m (→‎Retrieval conditions syntax: minor formatting)
 
(10 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 class="syntaxTable">
<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</var> <var class="term">inLabel</var> or
<li>If either of the <var>In <i>inLabel</i></var> or <var>On <i>list</i></var> clauses is specified,
<var>On</var> <var class="term">list</var> clauses is specified,
the <var>In <i>filespec</i></var> clause is not allowed, and the file context is the same context
the <var>In</var> <var class="term">filespec</var> clause is not allowed, and the file context is the same context
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</var> <var class="term">filespec</var> clause may be used to specify the file context. If <var>In</var> <var class="term">inRecordSet</var> is specified, its file or group context must be the same.
<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</var> <var class="term">filespec</var> clause is not specified, there must be a default file or group
<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">User Language</var> request, and that default context is used for the result record set.  If <var>In</var> <var class="term">inRecordSet</var> is specified, its file context overrides the default file or group context.
</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 which 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 are combined with Boolean set operations to produce the overall test for the records in the result 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 recordset <var>Find</var> recordset is equivalent to:
The above <var>Find</var> record set is equivalent to:
<p class="code">FD to %mySet (RECTYPE = 'CUST' or RECTYPE = 'PARTNER') and STATUS = 'ACTIVE'
<p class="code">FD to %mySet (RECTYPE='CUST' or RECTYPE='PARTNER') -
end find
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; hence blank lines within the recordset <var>Find</var> block have no effect, and, for
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|"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 which are retained in the result 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, which are then combined in the usual way with <var>And</var>, <var>Or</var>, <var>Not</var>, and parentheses (<code>()</code>).
conditions that are combined in the usual way with <var>And</var>, <var>Or</var>, <var>Not</var>, and parentheses (<b>()</b>):
The syntax of
</p>
<var class="term">retrievalConditions</var>, showing this, is presented here, after which is the list of
[[#List of primitive tests|<var class="term">primitiveTest</var>]] alternatives, and some other shortcuts available in the <var>Find</var> statement:
<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===
The list of <var class="term">primitiveTest</var> alternatives is shown below; each is explained in a separate
These are the <var class="term">primitiveTest</var> alternatives and shortcuts:
sub-section:
<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/>&nbsp;&nbsp;&nbsp;{  [<var>Numerically</var> | <var>Alphabetically</var>]
<br/>&nbsp;&nbsp;&nbsp;{  [<var>Numerically</var> | <var>Alphabetically</var>]
<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;[<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/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;[<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/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<var class="term">value</var>
<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<var class="term">value</var> }
<br/>&nbsp;&nbsp;&nbsp;}
<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/>&nbsp;&nbsp;&nbsp;{  [<var>Numerically</var> | <var>Alphabetically</var>]
<br/>&nbsp;&nbsp;&nbsp;{  [<var>Numerically</var> | <var>Alphabetically</var>]
<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;{  <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/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;{  <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/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;|
<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;|
<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<var>Between</var> <var class="term">value1</var> <var>And</var> <var class="term">value2</var>
<var>Between</var> <var class="term">value1</var> <var>And</var> <var class="term">value2</var> } </li>
<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;} </li>


<li><var>[[Record retrievals#FILE$ condition|File$]]</var> <var class="term">filename</var> </li>
<li><var>[[Record retrievals#FILE$ condition|File$]]</var> <var class="term">filename</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> can be:
<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><var>Value</var> [<var>In</var>] <var class="term">label</var> </li>
<li><var class="term">%variable</var> </li>


<li><var class="term">%variable</var> </li>
<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 equan to <var class="term">n</var>.
</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>
retrieves all records that have internal record numbers between 1500 and
2499, inclusive.


==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.
  • 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.