DDL Command RELATE

The RELATE Command

The RELATE command merges data from different tables into one file for reporting.

Notation standards Options Answers

Syntax:

RELATE/options cx table1 [(trans1)] ... tableN [(transN] OVER fname1 ... fnameN [PRESENT BY Pfname1 ... PfnameN]

Where: cx represents a collection name;
tableN represents a Table name;
fnameN represents a primary sort field name;
PfnameN represents a Present By sort field name;
transN represents a translation;
A translation can have any of the following forms:
      (Ttable)
      (Ttable BY Sfname)
      (Ttable THRU [K] [U] Tfname)
      generic: (Ttable [THRU [K] [U] Tfname] [BY [D] [K] [U] Sfname] [JOIN {I | L | R | F}])
            Ttable represents the Translation Table name;
            Tfname represents the Translation Field name;
            Sfname represents the Sort Field name;
            I specifies INNER JOIN;
            L specifies LEFT OUTER JOIN;
            R specifies RIGHT OUTER JOIN;
            F specifies FULL OUTER JOIN;
A primary sort field name, a Present By field name or a Translation sort field name may be preceeded by one or more of the qualifiers D, K and U;
      D - this key is to be sorted in descending mode;
      K - this key is to be sorted in case sensitive mode;
      U - this key is to be sorted in case insensitive mode;

Options:

J - Isolate orphan records; this is called the Right Outer Join option;
K - Change the default mode for all translations to case sensitive;
L - Display the size of the temporary scratch file area.
P - produce Full Explosion;
X - Suppress the command response.
Y - Do not alter the command answer cells. [Thunderbolt RAD only]

Answers:
                Ctl.Ans1                 =The number of records related
                Ctl.Ans2                 =The number of records in the result
                Ctl.Ans3                 =The number of frames in the result

Notes:
(1) In the Isolate, Arrange, Report paradigm, RELATE performs the Arrange step on data that has been isolated into a collection.

(2) A translation inserts records from the specified collection (cx Ttable)

RELATE Examples

RELATE Full Explosion Option