Basic SOUL statements and commands

This chapter discusses in more detail the basic statements that can be used within a request. This chapter also presents the system control commands that you commonly use during a terminal session. There are many more User Language statements and commands than are presented here.

System control commands

Before learning about User Language statements, it is important to understand the role of Model 204 commands that are required for basic request processing.

Use of commands with requests

System control commands are used outside requests to perform system level operations such as opening files and saving requests. Although system control commands are not part of User Language, many commands you might find useful are illustrated in examples throughout this manual.

Complete descriptions of these and other commands are included in commands.

Key commands

The following system control commands are essential to allow User Language requests to function:

Command	Action
BEGIN	Initiates a User Language request.
OPEN	Opens a Model 204 file or group. See OPEN or OPENC statement for a detailed discussion of the OPEN statement. Refer to IN clauses for a discussion of using the IN clause when several files are open.
CLOSE	Closes a Model 204 file or group that has been opened.

The MORE command and END MORE statement are discussed in the section Request continuation and in Large Request Considerations.

FIND statement

You can retrieve sets of records, records with specified characteristics, and individual records.

The basic retrieval statement in User Language is the FIND statement. Several forms of the FIND statement are provided for basic and complex retrievals. Basic retrieval statements are discussed in this chapter. See Record Retrievals for a discussion of more complex retrieval statements.

FIND ALL RECORDS statement

Syntax

The syntax for retrieving records in a Model 204 file is:

label: FIND ALL RECORDS [IN {FILE filename | GROUP groupname}] - FIND [AND RESERVE] [ALL] RECORDS - [IN label | ON [LIST] listname] - FOR WHICH | WITH] retrieval-conditions . . . . . END FIND

Where

• filename specifies the file that has the records you want to retrieve.
• groupname specifies the group of files that has the records you want to retrieve.
• label specifies the label of a preceding FIND statement.
• listname specifies a list from a preceding FIND statement.
• The retrieval-conditions can be:

[fieldname {EQ | NE} VALUE IN value_set]

fieldname identifies the field from which to retrieve values.

The value_set label represents an existing value set. Values of the value set in the EQ VALUE IN value_set clause are treated as boolean OR, for example:

field = value1 OR field = value2 OR....

The NE VALUE IN value_set clause is likewise treated as boolean AND, for example:

field <> value1 AND field <> value2 AND....

Example

A basic FIND statement is coded as follows:

FIND.RECS: FIND ALL RECORDS END FIND

Ending a FIND statement

Except where noted in this manual, you must end a FIND statement with either an END FIND statement or a label. A FIND statement cannot be ended by an END BLOCK statement.

Syntax

The format of the END FIND statement is:

END FIND [label]

In addition, the retrieval conditions of a FIND statement must not be labeled, even if they begin new lines.

Large Object data support

The FIND statement supports Large Object data in the following format:

FIND lob-fieldname IS [NOT] PRESENT

Understanding retrieval conditions

Processing a VALUE IN clause

You can incorporate a VALUE IN clause in the following User Language statements:

• FIND ALL RECORDS: see retrieval-conditions in FIND ALL RECORDS statement
• FIND ALL VALUES: see FIND ALL VALUES statement
• FOR EACH RECORD WHERE: see Restricting FOR EACH RECORD processing

The VALUE IN clause is valid for fields that include the ORDERED attribute. The interprocessing between fields works between fields of the same ORDERED type: ORDERED CHARACTER or ORDERED NUMERIC. If processing between fields of differing types, you will likely not find records.

Rewriting applications to take advantage of VALUE IN clause processing

If your User Language applications now use nested loops to perform join processing, you could rewrite your applications to take advantage of the VALUE IN clause.

You are likely to see performance improvements, which result in a reduction of overall wall clock time due to:

• A decrease in disk I/O
• Decreases in the number of FINDs and BXFINDs, because the processing has moved. You will see an increase in BXNEXTs

The following code example illustrates the use of a VALUE IN clause:

OPEN CLAIMS90 FILEMGR REDEFINE FIELD POLICY NO (ORD CHAR) OPEN CLIENTS FILEMGR REDEFINE FIELD POLICY NO (ORD CHAR) BEGIN /? FIND ALL CLIENTS WHO MADE CLAIMS ?/ CLAIM.POLICYS: IN CLAIMS90 FDV POLICY NO CLIENT.FD: IN CLIENTS FD POLICY NO EQ VALUE IN CLAIM.POLICYS END FIND FR CLIENT.FD PRINT POLICY NO AND FULLNAME END FOR END

FIND ALL RECORDS FOR WHICH/WITH statement

The basic statement for retrieving data based on specific retrieval conditions is:

label: FIND ALL RECORDS {FOR WHICH | WITH} {fieldname=value | fieldname=value1 OR value2 | fieldname NOT=value | fieldname=value1 AND NOT=value2 | fieldname NOT=value1 NOR value2 | fieldname IS LIKE pattern} | fieldname {EQ | NE} VALUE IN value-set

Example

This FIND ALL RECORDS FOR WHICH statement directs Model 204 to look for records of blue Fords:

FIND.RECS: FIND ALL RECORDS FOR WHICH MAKE = FORD COLOR = BLUE END FIND

This statement contains an AND implied by the fact that COLOR = BLUE appears on a separate line. Only the records containing both fields, MAKE = FORD and COLOR = BLUE, are retrieved.

Using expressions for value retrieval

You can use expressions for value retrieval in the following FIND statements:

• FIND - POINT$ condition
• FIND - SFL$ and SFGE$ conditions
• FIND ALL VALUES

For syntax examples, see "Retrieval condition syntax". For more information on expression types that you can use to supply retrieval values, see Using expressions for value retrieval.

Using Boolean operators with retrieval statements

You can use the Boolean operators AND, OR, NOT, and NOR to create complex retrieval conditions. In the previous example, the AND could be stated implicitly. Thus this statement:

FIND.RECS: FIND ALL RECORDS FOR WHICH MAKE = FORD AND COLOR = BLUE END FIND

retrieves the same data as the example in the section above.

However, the statement:

FIND.RECS: FIND ALL RECORDS FOR WHICH MAKE = FORD OR COLOR = BLUE END FIND

finds records for which either the make is Ford, the color is blue, or both.

The following statement:

FIND.RECS: FIND ALL RECORDS FOR WHICH MAKE = NOT FORD NOR COLOR = BLUE END FIND

finds all records other than blue Fords. NOR is the same as AND NOT.

Understanding NOT operator processing

Creating the complimentary foundset

Although a FIND statement with the NOT operator ultimately returns a foundset that excludes records, Model 204 FIND processing starts by creating a foundset based on the retrieval without the NOT operator. For the following code example, Model 204 locates all the records with MAKE=FORD, then returns to you the remainders or complimentary foundset: a foundset of records where MAKE=NOT FORD.

FIND.RECS: FIND ALL RECORDS FOR WHICH MAKE = NOT FORD END FIND

Note: Records that do not have the MAKE field are not included in either foundset.

Creating a foundset based on the position of the NOT operator

In the following abstract examples, notice the placement of the NOT operator.

Example A

label1: FIND ALL RECORDS FOR WHICH NOT fieldname IS LIKE*

In Example A, Model 204 locates a found set of the records with fieldname=*: that is, any value. Then Model 204 applies the NOT operator to fieldname and returns a found set of records that does not include fieldname. This mimics the behavior of IS NOT PRESENT; see IS PRESENT condition.

Example B

label2: FIND ALL RECORDS FOR WHICH fieldname IS NOT LIKE*

In Example B, Model 204 locates a foundset of the records with fieldname=*: that is, any value. Then Model 204 applies the NOT operator to LIKE* and returns a foundset of no records, because all values for fieldname have been excluded from the foundset.

Use the following example data set with three records of defined fields ABC and XYZ to illustrate the differing found sets from the placement of the NOT operator.

Record number	Field ABC	Field XYZ
1	14	11
2	25
3	7

Repeating Example A

LABEL1: FIND ALL RECORDS FOR WHICH NOT XYZ IS LIKE*

The count returned to you is 2: Records 2 and 3.

Repeating Example B

LABEL1: FIND ALL RECORDS FOR WHICH XYZ NOT IS LIKE*

The count returned to you is 0.

Loop statements

Once a set of records or values has been located, the found set can be referred to by using the FOR statement.

The FOR statement introduces a loop. The complete set of statements within the loop is performed once for each record or value located. If no record or values are found, the statements in the loop are skipped.

When a FIND statement refers back to a previous found set and the found set is empty, then the loop for the FIND statement is skipped. Performance for the FIND processing is enhanced because no retrieval is performed.

FOR EACH RECORD statement

Use the FOR EACH RECORD statement to refer to each record within a set of records located by a FIND statement.

Syntax

The form of the FOR EACH RECORD statement is:

FOR EACH RECORD IN label

where label is the label of a preceding FIND statement.

Example

FIND.RECS: FIND ALL RECORDS FOR WHICH MAKE = FORD END FIND PRINT.MODEL: FOR EACH RECORD IN FIND.RECS . . . END FOR

Notes:

• The FOR EACH RECORD statement also can be used to combine retrieval and looping when a particular set of records is processed only once in a request.
• Model 204 also supports a prefetch (look-ahead read) feature, which can provide performance improvements for applications containing FOR EACH RECORD statements. See Record Loops for more information on the FOR EACH RECORD statement and the prefetch feature.

Ending a FOR statement

The FOR statement must be ended by using an END FOR or an END BLOCK statement.

Syntax

The format of the END FOR statement is:

END FOR [label]

where label is the label of the line containing the FOR statement.

Nested loops

Any loop in User Language can be nested: embedded in another loop or series of statements. The END FOR statement returns the nesting level to the same level as the FOR statement.

IF statement

The IF statement tests a condition and then executes one or more statements if the condition is true.

The IF statement is discussed in detail in Flow of Control in User Language.

Syntax

The format of the IF statement is:

IF condition THEN statements

where condition follows the syntax rules described in "Expression syntax".

Ending an IF statement

The IF statement must be ended by an END IF statement or an END BLOCK statement.

Syntax

The format of the END IF statement is:

END IF [label]

Example

FOR EACH RECORD IN FIND.RECS IF TOTAL PREMIUM = 300 THEN PRINT POLICY NO END IF END FOR . . .

Counting records

In addition to retrieving specified records and printing fields from them, you might wish to know the number of records that satisfy a certain condition. Use either of these statements:

• COUNT RECORDS IN/ON (or CT)
• FIND AND PRINT COUNT (or FPC)

COUNT RECORDS IN/ON statement

The COUNT RECORDS statement takes two forms:

• COUNT RECORDS IN is used for counting records in a set of records retrieved with the FIND ALL RECORDS statement.
• COUNT RECORDS ON is used for counting records on a list. See Lists for more about lists.

The COUNT RECORDS IN/ON statement is supported in scattered group context. See Files, Groups, and Reference Context for more information on the reference context.

Syntax

The format for the COUNT RECORDS IN statement is:

label.b: COUNT RECORDS IN label.a

where label.a is the label of a previous FIND statement.

The format for the COUNT RECORDS ON statement is:

label.b: COUNT RECORDS ON list

where list is the name of a list on which you have previously placed found records.

For both forms of the COUNT RECORDS statement, you can print the count using the statement:

PRINT COUNT IN label.b

Example

This COUNT RECORDS IN request:

BEGIN DRIVER: FIND ALL RECORDS FOR WHICH RECTYPE = DRIVER POLICY NO = 100340 END FIND CT: COUNT RECORDS IN DRIVER PRINT COUNT IN CT END

causes Model 204 to note that two records were found by the FIND statement. Model 204 then prints the number 2.

FIND AND PRINT COUNT statement

The FIND AND PRINT COUNT statement is used to combine record retrieval with the COUNT and associated PRINT statements.

Example 1

This statement:

DRIVER: FIND AND PRINT COUNT RECTYPE = DRIVER POLICY NO = 100340 END FIND

is similar to:

DRIVER: FIND ALL RECORDS FOR WHICH RECTYPE = DRIVER POLICY NO = 100340 END FIND CT: COUNT RECORDS IN DRIVER PRINT COUNT IN CT

The label for the FIND AND PRINT COUNT statement can be referred to in the same way in which a label for a FIND ALL RECORDS FOR WHICH statement can be used.

Example 2

The count found by a FIND AND PRINT COUNT statement cannot be positioned to a specified column. The position defaults to column 1 or the next print location, if preceded by an ellipsis (...).

For example, this request:

BEGIN DRIVER: FIND AND PRINT COUNT RECTYPE = DRIVER POLICY NO = 100340 END FIND FOR EACH RECORD IN DRIVER PRINT FULLNAME END FOR END

results in this output:

2 ABBOTT, FRANKLIN G ABBOTT, GAIL H

Output statements

Print statements are often used in loops to display information from each record located by a FIND statement.

The two statements described in this section, PRINT ALL INFORMATION and PRINT fieldname, are basic output statements. PRINT ALL INFORMATION and PRINT fieldname can be used only in a record loop.

The PAI INTO statement, an extension of the PAI statement, allows you to store the field names and values associated with a record in an array. See PAI INTO statement for more information.

More complex statements are used for variable format output. These are discussed in Report Generation.

PRINT ALL INFORMATION (or PAI) statement

Purpose

The PRINT ALL INFORMATION (or PAI) statement causes output of whole records.

Syntax

The form of the PAI statement is:

PRINT ALL INFORMATION option

where option can be one of the following and specifies how Large Object fields are handled.

• LOB_FLOD, the default, which means output in a format that FLOD can read and reload Large Object fields. The output includes the descriptor in binary format, plus the full field value.
• LOB_DATA specifies whether to display the field value. The PAI statement displays the Large Object descriptor in printable format.
• LOB_SHORT_DATA specifies to display the first 255 bytes of the field value. The PAI statement displays the Large Object descriptor in printable format.
• LOB_NO_DATA specifies not to display the value. However, the PAI statement displays the Large Object descriptor in printable format.
• LOB_NONE, which specifies not to display any Large Object fields.

Example

This request:

BEGIN FIND.RECS: FIND ALL RECORDS FOR WHICH RECTYPE = DRIVER FULLNAME = ABBOTT, GAIL H END FIND FOR EACH RECORD IN FIND.RECS PRINT ALL INFORMATION END FOR END

results in output with the format:

FULLNAME = ABBOTT, GAIL H POLICY NO = 100340 DRIVER ID = 100817 MARITAL STATUS = SINGLE SEX = F STATE = CALIFORNIA INCIDENT = T1 INCIDENT DATE = 19890820 INCIDENT = T3 INCIDENT DATE = 19900921 DATE OF BIRTH = 19390305

INVISIBLE fields not printed

In the example on the previous page, the fieldname = value pair, RECTYPE = DRIVER, is not printed in the output because the RECTYPE field has the INVISIBLE attribute.

For more information about the INVISIBLE attribute, refer to Field descriptions and attributes.

PRINT fieldname statement

Syntax

To display the value of a certain field in a record, use this form of the PRINT statement:

PRINT fieldname [AND fieldname2]

Example

Here is a sample PRINT fieldname statement:

BEGIN DRIVER: FIND ALL RECORDS FOR WHICH RECTYPE = DRIVER POLICY NO = 100340 END FIND FOR EACH RECORD IN DRIVER PRINT FULLNAME END FOR END

In response to the above request, Model 204 prints:

ABBOTT, FRANKLIN G ABBOTT, GAIL H

Each value of the FULLNAME field is displayed on a new line. A record that has no FULLNAME field produces a blank line of output.

Printing several fields

The values of more than one field can be printed on the same output line by using the keywords AND and WITH. AND causes a space to be printed between values, as in the following example:

BEGIN DRIVER: FIND ALL RECORDS FOR WHICH RECTYPE = DRIVER POLICY NO = 100340 END FIND FOR EACH RECORD IN DRIVER PRINT SEX AND DATE OF BIRTH END FOR END

Running the request above results in this output:

M 19480204 F 19390305

If a retrieved record has no SEX field, the value of DATE OF BIRTH is printed on a line beginning with a space, as specified by the keyword AND, for example:

19480204

If a record has neither field, it produces a blank line of output.

The keyword WITH works like AND except that WITH does not cause a space to be printed between values.

Controlling spacing between output lines

The SKIP LINE(S) and NEW PAGE statements enable you to control spacing between lines.

SKIP LINE(S)

The SKIP LINE(S) statement directs Model 204 to skip a specified number of lines before printing the next line of output. If the number of lines remaining on a page is fewer than the number of lines specified, Model 204 skips the remainder of the lines on the page, starts a new page, and then continues to skip lines until n is reached.

The syntax of the SKIP LINE(S) statement is as follows:

SKIP {n | %variable} LINE[S]

Where

• n is a positive integer less than or equal to 100.
• %variable is a positive integer from 0 through 100 supplied by a %variable or a :% variable. Field names or %% variables are invalid.

SKIP %variable LINE(S) bypasses the processing and skips no lines if the value of the %variable is zero. However, if n is zero, SKIP n LINES is invalid.

The SKIP %variable LINE(S) option does not apply to the full-screen SKIP LINE(S) statement, as described on SKIP statement for menus.

NEW PAGE

The NEW PAGE statement directs Model 204 to start a new page before printing further output.

For example, in response to:

FOR EACH RECORD IN DRIVER NEW PAGE PRINT ALL INFORMATION END FOR

Model 204 starts a new page before printing each record.

Loading Large Object data

A Model 204 file that contains Large Object data can be reorganized using the standard PAI and FLOD procedure, which employs the User Language PAI (PRINT ALL INFORMATION) statement to unload the data from a file, and a standard FLOD program to reload the data. This is identical to previous releases of Model 204, and no change to your existing unload or load code is required.

IN clauses

Many User Language statements can be preceded by an IN clause. An IN indicates explicitly the file or group to which a statement applies when several files or groups are open.

When you do not use the IN clause, Model 204 acts on the default file as established by the most recently executed OPEN or DEFAULT command.

In addition to specifying an individual file or group name, the IN clause has more detailed information about the reference context of the statement it precedes. For example, when you are working with individual remote files, you can specify the location of the file; and when you are working in group context, you can restrict the context of certain statements to one member file in the group by using the IN GROUP MEMBER form of the IN clause.

Refer to Files, Groups, and Reference Context for more information on the reference context and ways to use the IN clause.

Syntax

The simplest form of the IN clause is:

IN {filename | groupname} [AT location]

Example

This request prints the policy number, body, and make of a policy in the VEHICLES file, and the full name, marital status, and sex of records for that policy number in the CLIENTS file:

OPEN CLIENTS OPEN VEHICLES BEGIN VEHICLE.RECS: IN VEHICLES AT BOSTON FIND ALL RECORDS FOR WHICH OWNER POLICY = 100340 VIN = DAT7100707 END FIND FOR EACH RECORD IN VEHICLE.RECS PRINT OWNER POLICY AND BODY AND MAKE END FOR DRIVER: IN CLIENTS FIND ALL RECORDS FOR WHICH RECTYPE = DRIVER POLICY NO = 100340 END FIND FOR EACH RECORD IN DRIVER PRINT FULLNAME AND MARITAL STATUS AND SEX END FOR END

The output from this request would be:

100340 4DR DATSUN ABBOTT, FRANKLIN G SINGLE M ABBOTT, GAIL H SINGLE F

Request continuation

Lengthy requests can be handled by using the END MORE statement and the MORE command. See Large Request Considerations for more information on request continuation, including a discussion of the MORE command and the END MORE statement and its USE option.

Example

The sample request below tells you how many records exist in a given file for female drivers registered in Florida:

BEGIN GET.DRIVER: FIND AND PRINT COUNT RECTYPE = DRIVER STATE = FLORIDA SEX = F END FIND END MORE

After printing its output, this request returns you to command level. Using the MORE command, you can continue the request, adding further statements as well as referring to the results of statements in the original request. If, for example, you wanted to perform further processing of the records found in the original request without repeating the FIND operation, you could enter the following lines, referencing the label GET.DRIVER:

MORE FOR EACH RECORD IN GET.DRIVER PRINT FULLNAME AND DATE OF BIRTH END FOR END