The MODIFY COMBINE Facility

In this section:

How to:

Some applications require that you use a single input transaction to update several tables in the same MODIFY procedure. If the tables are not defined in the same Master File, you can use the COMBINE facility to modify them as if they are one.

Note: In Maintain, you do not issue a COMBINE command to modify unrelated tables. Instead, you reference multiple tables in the MAINTAIN FILE command. For example:

MAINTAIN FILES EMPINFO AND COURSE

You can maintain up to 63 tables in a single MODIFY procedure that operates on a COMBINE structure. The COMBINE limit is 16 Master Files. However, each Master File can describe more than one table, for a total of 64 per procedure, minus one for the virtual root segment created by the COMBINE command.

The COMBINE facility links multiple tables and assigns a new name to them so FOCUS can treat the tables as a single structure. Tables in a COMBINE structure can have different SUFFIX attributes, but you cannot combine a FOCUS data source with anything except other FOCUS data sources.

Note: In Maintain, you can modify FOCUS data sources and RDBMS tables in the same procedure.

When you issue a COMBINE command, the COMBINE structure remains in effect for the duration of the FOCUS session or until you enter another COMBINE command. Only one COMBINE structure can exist at a time, so each subsequent COMBINE command replaces the existing structure.

Do not confuse COMBINE with the dynamic JOIN command. You use JOIN to report from multiple tables or for LOOKUP functions. With the COMBINE facility, you can MODIFY multiple tables. COMBINE is part of the MODIFY command. Only the MODIFY and CHECK FILE commands process COMBINE structures. The FIND function also works in conjunction with COMBINE (see The FIND Function).

Note that COMBINE considers the component structures to be unrelated. Although RDBMS referential integrity is enforced, FOCUS referential integrity does not apply to a COMBINE of single-table Master Files. Your procedure should check for and enforce referential integrity, if necessary.


Top of page

x
Syntax: How to Create a COMBINE Structure
COMBINE FILES file1 [PREFIX pref1|TAG tag1] [AND]
   .
   .
   .
               filen [PREFIX prefn|TAG tagn] AS asname 

where:

file1 - filen

Are the Master File names of the tables you want to modify. You can specify up to 16 Master Files.

pref1 - prefn

Are prefix strings for each file, up to four characters. They provide uniqueness for field names. You cannot mix TAG and PREFIX in a COMBINE structure. Refer to your FOCUS documentation for additional information.

tag1 - tagn

Are aliases for the table names, up to eight characters. FOCUS uses the tag name as the table name qualifier for fields that refer to that table in the combined structure. You cannot mix TAG and PREFIX in a COMBINE.

AND

Is an optional word to enhance readability.

asname

Is the required name of the combined structure to use in MODIFY procedures and CHECK FILE commands.

Once you enter the COMBINE command, you can modify the combined structure.


Top of page

x
How FOCUS Creates a COMBINE Structure

The EMPINFO table contains employee number, last name, first name, hire date, department code, current job code, current salary, number of education hours, and bonus plan information. A second table, PAYINFO, is a historical record of the employee's pay history. It contains the employee number, date of increase, percent of increase, new salary, and job code. (File Descriptions and Tables provides the Master and Access Files for these two tables.)

Each time a salary changes, both the EMPINFO and PAYINFO tables must reflect the change. Since both tables need to share data entered for employee number, salary and job code, this application is appropriate for the COMBINE facility. You can update both tables at the same time without having to define multi-table Master and Access Files.

The following figures represent the tables as separate entities.

EMPINFO table          PAYINFO table
           EMPINFO                PAYINFO
01         S0          01         S0
**************         **************
*EMP_ID      **        *PAYEID      **
*LAST_NAME   **        *DAT_INC     **
*FIRST_NAME  **        *PCT_INC     **
*HIRE_DATE   **        *SALARY      **
*            **        *            **
***************        ***************
**************         **************
          EMPINFO                PAYINFO

To modify the tables simultaneously, issue the following sequence of commands at the FOCUS command level or in a FOCEXEC:

COMBINE FILES EMPINFO PAYINFO AS EMPSPAY 
MODIFY FILE EMPSPAY
    .
    .
    .

In the following picture, generated by the CHECK FILE command, FOCUS defines a new segment, identified as SYSTEM99, to be the root segment of the combined structure. SYSTEM99 acts as the traffic controller for this structure. It is a virtual (artificial) segment. It counts as one segment towards the total of 64 segments allowed in the COMBINE structure.

check file empspay pict
  NUMBER OF ERRORS=     0
  NUMBER OF SEGMENTS=   3  ( REAL=    3  VIRTUAL=   0 )
  NUMBER OF FIELDS=    15  INDEXES=   0  FILES=     3
  TOTAL LENGTH OF ALL FIELDS=   95
 SECTION 01
          STRUCTURE OF SQLDS    FILE EMPINFO  ON 07/22/93 AT 09.54.27
          SYSTEM99
  01      S0
 **************
 *            **
 *            **
 *            **
 *            **
 *            **
 ***************
  **************
        I
        +-----------------+
        I                 I
        I EMPINFO         I PAYINFO
  02    I S0        03    I S0
 **************    **************
 *EMP_ID      **   *PAYEID      **
 *LAST_NAME   **   *DAT_INC     **
 *FIRST_NAME  **   *PCT_INC     **
 *HIRE_DATE   **   *SALARY      **
 *            **   *            **
 ***************   ***************
  **************    **************
          EMPINFO           PAYINFO

The COMBINE facility makes it easy to modify many files with the same transaction. For additional information regarding the COMBINE facility of MODIFY, refer to your FOCUS documentation.



x
SET INCLUDE SUBTREE

In early releases, the adapter used segment activation logic in MODIFY write operations that differed from that of standard FOCUS against FOCUS files.

In a multi-path file structure involving FOCUS files (including structures generated by the COMBINE command), INCLUDE or DELETE actions operate only on active segments in the subtree of the segment specified in the previous MATCH or NEXT command.

However, with external DBMS files, INCLUDE or DELETE actions would operate not only on active segments in the subtree of the segment specified in the MATCH or NEXT command, but also on any active segments to the right of that segment in the hierarchy (as displayed by the CHECK FILE PICTURE command).

Currently, the default adapter behavior is consistent with the standard FOCUS behavior.

Note: You can control whether the adapter uses the FOCUS standard behavior for external DBMS files using the SET INCLUDE SUBTREE subcommand. Place the subcommand on the line immediately following the MODIFY command:

SQL SET INCLUDE {SUBTREE|LATERAL}

where:

SUBTREE

Institutes the standard FOCUS behavior. INCLUDE or DELETE actions operate only on active segments in the subtree of the segment specified in the previous MATCH or NEXT command. This is the default value

LATERAL

INCLUDE or DELETE actions operate not only on active segments in the subtree of the segment specified in the MATCH or NEXT command, but also on any active segments to the right of that segment in the hierarchy (as displayed by the CHECK FILE PICTURE command).


Information Builders