JOIN Utilities

In this section:

You can check and control the status of join structures with three commands, CHECK FILE, ? JOIN query, and JOIN CLEAR.


Top of page

x
CHECK FILE

The CHECK FILE command produces a diagram of host and cross-referenced relationships and retrieval paths. CHECK FILE also reloads the Master File and checks the Master File syntax.

To display the structure, at the FOCUS command level type

CHECK FILE name PICT[URE] [RETRIEVE] [HOLD] [JOIN]

where:

name

Is the name of the host structure (for a dynamic join) or of a multi-table Master File.

PICT[URE]

Displays a diagram of the structure.

RETRIEVE

Is optional. Modifies the diagram to show retrieval paths, especially how a unique table is treated as an extension of its host. See your FOCUS documentation for more information.

HOLD

Creates a temporary HOLD file containing detailed information about the fields and segments in the structure.

JOIN

Loads and parses the Access File, checking for syntax errors. It does not check for the existence of the tables or databases named. It checks for the existence of the fields used as the KEYFLD and IXFLD pairs that implement an equijoin, but not their data types.

Used in conjunction with the HOLD option, JOIN adds a field named INDX to the HOLD file. The value 1 in this field indicates that you can join to the field. This option is useful when joining to a data source that requires the target of a join to be a key or index field.

On the diagram, labels for non-unique (KM) or unique (KU) next to the cross-referenced structure indicate whether the dynamic JOIN is non-unique or unique. Only the first four fieldnames of each structure display.

The letter K next to a field indicates that it represents the foreign key column or the first column of a composite foreign key. If the cross-referenced structure has descendants, the descendants are labeled as either KL (keyed through linkage) or KLU (keyed through linkage unique), depending on how their relationship was declared to FOCUS.



Example: Displaying a Structure With CHECK FILE
JOIN EMP_ID IN EMPINFO TAG FILE1 TO ALL WHO IN COURSE TAG FILE2 AS J1
 
CHECK FILE EMPINFO PICT
 
  NUMBER OF ERRORS=     0
  NUMBER OF SEGMENTS=   2  ( REAL=    1  VIRTUAL=   1 )
  NUMBER OF FIELDS=    13  INDEXES=   0  FILES=     2
  TOTAL LENGTH OF ALL FIELDS=   95
 
 SECTION 01
 STRUCTURE OF GNTINT   FILE EMPINFO ON 06/19/90 AT 12.17.40
 
          EMPINFO
  01      S0
 **************
 *EMP_ID      **
 *LAST_NAME   **
 *FIRST_NAME  **
 *HIRE_DATE   **
 *            **
 ***************
  **************
        I
        I
        I
        I COURSE
  02    I KM
 ..............
 :CNAME       ::
 :WHO         ::K
 :GRADE       ::
 :YR_TAKEN    ::
 :            ::
 :............::
  .............:
  JOINED  COURSE

Note: “JOINED COURSE” indicates that this relationship was created by a dynamic JOIN command rather than a multi-segment Master File.


Top of page

x
? JOIN

For all dynamic joins that are in effect, the ? JOIN query command identifies the fields representing the primary/foreign key field pair, the host structure, the cross-referenced structure, any specified join name, the type of join (non-unique or unique, as indicated by the presence or absence of the ALL keyword), and whether the join is conditional.

To list all active dynamic join structures, enter:

? JOIN

For example:

? join
 JOINS CURRENTLY ACTIVE
 
HOST                           CROSSREFERENCE
FIELD        FILE    TAG      FIELD        FILE     TAG    AS   ALL WH
-----        ----    ---      -----        ----     ---    --   --- --
PAYEID       PAYINFO  P1       EMP_ID       EMPINFO  E1           Y   Y

Note: The ? JOIN query command displays only the first join pair for multi-field joins.


Top of page

x
JOIN CLEAR

A maximum of 1023 dynamic joins can be active in any FOCUS session. The JOIN CLEAR command can release either all or specific existing joins. The syntax is

JOIN CLEAR {*|joinname}

where:

*

Clears all existing joins.

joinname

Is a specific join to be cleared.

The effect of the JOIN CLEAR command depends on whether conditional joins exist:

The JOIN CLEAR * command clears every join that was issued along with its associated virtual fields. However, virtual fields defined in the null context (prior to any joins) remain in effect.


Information Builders