The NEXT Command

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.

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:

NEXT command without a MATCH command. The adapter requests the retrieval of all rows in the table sorted by primary key.
MATCH with the primary key or a superset of primary key columns. The MATCH returns the single row that is the starting point for any subsequent NEXT commands.
MATCH on a non-key field or a subset of primary key columns. The RDBMS returns a multi-row answer set in which each row satisfies the MATCH criteria.

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:

Without Case Logic, you can retrieve all parent rows in the table and only the first descendant row of any specified related table.
With Case Logic, you can retrieve all rows for each table defined in a multi-table Master File. To do so, first MATCH on the parent. Then, in another case, use NEXT to loop through the related tables (at the lowest level) until there are no more related instances. On NONEXT, return to the parent case for the next parent instance.
You can trace Case Logic with the FOCUS TRACE facility. To invoke the TRACE facility for the adapter, include the TRACE command on a separate line after the MODIFY FILE command. For complete information about the FOCUS TRACE facility, see your FOCUS documentation. You can also use the adapter trace facilities described in Tracing Adapter Processing.

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.

Reference: Usage Notes for NEXT in Maintain

The syntax of the NEXT command includes optional FOR and WHERE phrases that control the number of rows retrieved into a stack. As in MODIFY, the KEYORDER attribute in the Access File determines whether NEXT returns rows in ascending or descending order of the primary key.
NEXT always starts its retrieval at the current database position. It will not retrieve a row it has already passed in its retrieval path unless you use the REPOSITION command to reset the current position.
Also, as in MODIFY, once you MATCH on a parent segment, a subsequent NEXT on a child segment retrieves descendant rows within the parent established by the MATCH. However, one NEXT command can retrieve all such child instances, without Case Logic.
The UPDATE, DELETE, and INCLUDE commands also incorporate the optional FOR phrase to process multiple rows from a stack. The system variables FOCERROR and FOCERRORROW inform you whether the entire set of rows processed successfully and, if not, which row caused the problem.

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

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.

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: 112847612 LAST NAME SMITH    
EMPLOYEE ID: 117593129 LAST NAME JONES    
EMPLOYEE ID: 119265415 LAST NAME SMITH    
EMPLOYEE ID: 119329144 LAST NAME BANNING  
EMPLOYEE ID: 123764317 LAST NAME IRVING   
EMPLOYEE ID: 126724188 LAST NAME ROMANS   
EMPLOYEE ID: 219984371 LAST NAME MCCOY    
EMPLOYEE ID: 326179357 LAST NAME BLACKWOOD
EMPLOYEE ID: 333121200 LAST NAME ROYCE    
EMPLOYEE ID: 451123478 LAST NAME MCKNIGHT 
EMPLOYEE ID: 455670000 LAST NAME LEE      
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:

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).

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:

The user enters the employee ID for the search, 219984371.
The MATCH command causes the RDBMS to search the table for the entered value. If no such row exists, the transaction is rejected.
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.
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.
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:

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

In a MODIFY request processed by the 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.)

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:

The user enters the last name (LAST_NAME) for the search, SMITH.
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.
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.)
After displaying the row, the procedure goes to the GETSAME case. It uses NEXT to loop through the remaining rows in the answer set.
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.
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: