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 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: |
NEXT field
ON NEXT action_1
ON NONEXT action_2
where:
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:
You can trace Case Logic with the FOCUS TRACE facility. To invoke the TRACE facility for the data 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 data adapter trace facilities described in Tracing Data 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.
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.
For complete details, see your FOCUS documentation on maintaining databases.
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: |
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
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:
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: |
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.
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 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.
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:
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: |
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.
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 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.
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 |