The NEXT Command

In MODIFY, the NEXT command provides a flexible means of processing multi-row answer sets by moving the current position in the answer set from one row to the next.

In this section:

NEXT Processing Without MATCH

NEXT Processing After MATCH on a Full Key or on a Superset

NEXT Processing After MATCH on a Non-Key Field or Partial Key

How to:

Use the NEXT Command in MODIFY

Reference:

Usage Notes for NEXT in MODIFY

Usage Notes for NEXT in Maintain


Top of page

Syntax: How to Use the NEXT Command in MODIFY

NEXT field 
ON NEXT action_1
ON NONEXT action_2

where:

field
Is any field in the table. It does not have to be a primary key.

action_1
Is the operation to perform when there is a subsequent row in the answer set. May be any of the acceptable actions listed for MATCH in The MATCH Command.

action_2
Is the operation to perform when no more rows exist in the answer set.


Top of page

Reference: Usage Notes for NEXT in MODIFY

The KEYORDER parameter in the Access File controls the sort order (by primary key) for NEXT. It determines whether to retrieve primary key values in low (ascending order) or high (descending order) sequence. Describing Tables to FOCUS, explains how to specify the KEYORDER parameter. The default is to sort by primary key in ascending order.

Your choice of MATCH and NEXT command combinations determines the contents of the answer set. Subsequent sections explain these choices in more detail:

You can also use NEXT commands with multi-table structures (FOCUS views) to modify or display data in either Case Logic or non-Case Logic requests. If your MATCH or NEXT specifies a row from a parent table in a multi-table structure, that row becomes the current position in the parent table. A subsequent NEXT on a field in a descendant of that table retrieves the first descendant row in the related table. In MODIFY:

The following sections illustrate different combinations of MATCH and NEXT commands with annotated examples. The MODIFY requests have been kept simple for purposes of illustration; you can create more sophisticated procedures. Assume KEYORDER=LOW for all of the examples.


Top of page

Reference: Usage Notes for NEXT in Maintain

For complete details, see your FOCUS documentation on maintaining databases.


Top of page

NEXT Processing Without MATCH

If you use a NEXT command without a previous MATCH command in a MODIFY request, the RDBMS returns an answer set consisting of all rows in the table sorted by the primary key. Use the ON NEXT command to view each row in ascending primary key order. In a Maintain request, the FOR and WHERE phrases in the NEXT command determine the number of rows retrieved, sorted by the primary key in KEYORDER sequence.

Example:

Using NEXT Without MATCH in MODIFY

Using NEXT in Maintain


Top of page

Example: Using NEXT Without MATCH in MODIFY

In this MODIFY example, the NEXT command retrieves each row in ascending order of employee ID number (EMP_ID):

MODIFY FILE EMPINFO
NEXT EMP_ID
ON NEXT TYPE "EMPLOYEE ID: <D.EMP_ID LAST NAME <D.LAST_NAME "
ON NONEXT GOTO EXIT
DATA
END

The TYPE commands display the following on the screen:

EMPLOYEE ID: 071382660  LAST NAME STEVENS
.
.
.
.
EMPLOYEE ID: 326179357 LAST NAME BLACKWOOD
EMPLOYEE ID: 451123478 LAST NAME MCKNIGHT
EMPLOYEE ID: 543729165 LAST NAME GREENSPAN
EMPLOYEE ID: 818692173 LAST NAME CROSS

Top of page

Example: Using NEXT in Maintain

The following Maintain procedure named NEXT1 retrieves the same answer set into a stack named INSTACK and displays the retrieved values on a Winform named WIN1 (see your FOCUS documentation on maintaining databases for instructions on creating Winforms):

MAINTAIN FILE EMPINFO
FOR ALL NEXT EMP_ID INTO INSTACK
WINFORM SHOW WIN1
END

Executing the NEXT1 procedure displays a Winform similar to the following:


Top of page

NEXT Processing After MATCH on a Full Key or on a Superset

In MODIFY, NEXT processing is identical for either a MATCH on a full primary key or a MATCH on a superset (full key plus a non-key field).

Example:

Using NEXT After MATCH on a Full Primary Key in MODIFY

Using NEXT on a Full Primary Key in Maintain

When the initial MATCH is successful, the RDBMS retrieves one row. This establishes the logical position in the table. The subsequent NEXT command causes the RDBMS to retrieve all rows following the matched row in key sequence.


Top of page

Example: Using NEXT After MATCH on a Full Primary Key in MODIFY

The following is an example of NEXT processing after a MATCH on a full primary key, the EMP_ID field:

   MODIFY FILE EMPINFO
CRTFORM LINE 1
" PLEASE ENTER VALID EMPLOYEE ID </1"
1. " EMP: <EMP_ID "
2. MATCH EMP_ID
ON NOMATCH REJECT
3. ON MATCH GOTO GETREST
CASE GETREST
4. NEXT EMP_ID
ON NEXT CRTFORM LINE 10
" EMP_ID: <D.EMP_ID LAST_NAME: <D.LAST_NAME "
ON NEXT GOTO GETREST
5. ON NONEXT GOTO EXIT
ENDCASE
DATA
END

The MODIFY procedure processes as follows:

  1. The user enters the employee ID for the search, 219984371.

  1. The MATCH command causes the RDBMS to search the table for the entered value. If no such row exists, the transaction is rejected.

  2. If the specified value matches a value in the EMP_ID column of the table, the procedure branches to the GETREST case; it contains the NEXT command.

  3. The NEXT command retrieves the next logical row in EMP_ID sequence. If such a row exists, the procedure displays the values of the EMP_ID and LAST_NAME fields. It continues to display each row in order of the key field, EMP_ID.

  4. If there are no more rows, the procedure ends.

The output after executing this MODIFY procedure is:

PLEASE ENTER VALID EMPLOYEE ID                          (line 1)
EMP: 219984371                                          (line 3)
EMP_ID:  326179357   LAST_NAME:  BLACKWOOD              (line 10)
EMP_ID:  333121200   LAST_NAME:  ROYCE                  (line 10)
EMP_ID:  451123478   LAST_NAME:  MCKNIGHT               (line 10)
EMP_ID:  455670000   LAST_NAME:  LEE                    (line 10)
EMP_ID:  543729165   LAST_NAME:  GREENSPAN              (line 10)
EMP_ID:  818692173   LAST_NAME:  CROSS                  (line 10)

Because of the NEXT command, all employees after 219984371 display one at a time on the screen. Notice that the rows are retrieved in key sequence.


Top of page

Example: Using NEXT on a Full Primary Key in Maintain

The following Maintain procedure retrieves the same answer set into a stack named EMPSTACK. Assume that when Maintain displays the Winform called WIN1, the user enters the transaction value 219984371 into a stack named TRANS and presses PF5 to invoke the NEXTRECS case:

MAINTAIN FILE EMPINFO
WINFORM SHOW WIN1
CASE NEXTRECS
FOR ALL NEXT EMP_ID INTO EMPSTACK WHERE EMP_ID GT TRANS.EMP_ID
ENDCASE
END

Executing the NEXT2 procedure displays a Winform similar to the following:


Top of page

NEXT Processing After MATCH on a Non-Key Field or Partial Key

In a MODIFY request processed by the data adapter, you do not have to MATCH on the full set of key fields. You can match on a non-key field or partial key. (Maintain always matches on the full primary key, regardless of which fields you specify in the MATCH command.)

Example:

Using MATCH on a Non-Key Field in MODIFY

Using NEXT on a Non-Key Field in Maintain

When you MATCH on a non-key column or subset of key columns, multiple rows may satisfy the MATCH condition. The MATCH operation retrieves the first row of the answer set, and the NEXT command makes the remaining rows in the answer set available to the program in primary key sequence.


Top of page

Example: Using MATCH on a Non-Key Field in MODIFY

This annotated procedure is the previous MODIFY procedure altered to MATCH on the non-key field LAST_NAME. The NEXT operation retrieves the subsequent rows from the answer set:

      MODIFY FILE EMPINFO
CRTFORM LINE 1
" PLEASE ENTER A LAST NAME </1 "
1. " LAST NAME: <LAST_NAME </1"
2. MATCH LAST_NAME
ON NOMATCH REJECT
3. ON MATCH CRTFORM LINE 5
" EMP_ID: <D.EMP_ID LAST_NAME: <D.LAST_NAME "
4. ON MATCH GOTO GETSAME
CASE GETSAME
5. NEXT LAST_NAME
ON NEXT CRTFORM LINE 10
" EMP_ID: <D.EMP_ID LAST_NAME: <D.LAST_NAME "
ON NEXT GOTO GETSAME
6. ON NONEXT GOTO EXIT
ENDCASE
DATA
END

The MODIFY procedure processes as follows:

  1. The user enters the last name (LAST_NAME) for the search, SMITH.

  1. The MATCH command causes the RDBMS to search the table for all rows with the value SMITH and return them in EMP_ID order. If the value SMITH does not exist, the transaction is rejected.

  2. If the incoming value matches a value in the table, the procedure displays the employee ID and last name. (This is the first row of the answer set.)

  3. After displaying the row, the procedure goes to the GETSAME case; it uses NEXT to loop through the remaining rows in the answer set.

  4. Instead of retrieving the next logical row with a higher key value as in the previous example, the procedure retrieves the next row in the answer set (all rows in the answer set have the last name SMITH). If any exist, they display on the screen in order of the key, EMP_ID.

  5. When no more rows exist with the value SMITH, the procedure ends.

The output from this MODIFY procedure follows:

PLEASE ENTER A LAST NAME

LAST_NAME smith

EMP_ID: 112847612 LAST_NAME: SMITH



EMP_ID: 119265415 LAST_NAME: SMITH

A line displays on the screen for each employee with the last name SMITH. Employee ID 112847612 is the result of the MATCH operation; employee ID 119265415 is the result of the NEXT operation.


Top of page

Example: Using NEXT on a Non-Key Field in Maintain

The following Maintain procedure retrieves the answer set into a stack named EMPSTACK. Assume that when Maintain displays the Winform named WINA, the user enters the transaction value (SMITH) into the first row of a stack named TRANS and presses PF5 to invoke the NEXTRECS case:

MAINTAIN FILE EMPINFO
WINFORM SHOW WINA
CASE NEXTRECS
FOR ALL NEXT EMP_ID INTO EMPSTACK WHERE LAST_NAME EQ TRANS.LAST_NAME
ENDCASE
END

Executing the NEXT3 procedure displays a Winform similar to the following:


Information Builders