ddlRelateEx() - arrange hierarchical data

ddlRelateEx() - arrange hierarchical data ddlRelateEx() - arrange hierarchical data

The ddlRelateEx() API function is an extension of the ddlRelate() API function that more closely models the ANSI SQL JOIN syntax and functionality.

Prototype:

   typedef struct JOININFO{
      char jiSchemaTableName1[288];
      char jiSchemaTableName2[288];
      char jiAliasName[128];
      char jiJoinType[64];
      char jiJoinCondition[800];
      int jiJoinOrder;
   }JOININFO;
 
   void ddlRelateEx(
      CONTROL *ctl,
      char *OptnStr,
      char *ArgList,
      int JoinInfoCount,            /* the number of JOININFO{} members */
      JOININFO *ji);

Permitted values in OptnStr: NA

Form of ArgList:

<cx> [PRESENT BY [D] <field1> [[D] <field2> ..]] [<value-clause>] [<calc-clause>] [<post-calc-clause>]

Where:

<cx> is the name of the collection
<field1> <field2> ... are the PRESENT BY fields
<value-clause> is an optional clause for carrying data to the next process;
      VALUE <value-field1> ... <value-var1> ...
<calc-clause> is an optional clause for calculating PRESENT BY and/or VALUE parameters
      <calc-expression> [<calc-expression>] ...
      CALCOBJECT <calc-object-name>
            <calc-expression> is:
                   {CALC | SET | IF | ELSE | ENDIF} statement
<post-calc-clause> is an optional clause for processing sort results in a single step
      POSTCALCOBJECT <post-calc-object-name>

The PRESENT BY Clause performs a SORT on the results of the RELATEX JOIN. It is used to translate a GROUP BY or ORDER BY Clause. However, if both GROUP BY and ORDER BY Clauses are present, additional processing is required.

The VALUE Clause is an optimization:
      1) If no POSTCALCOBJECT is present, the specified values are written to the result file, alleviating
         the subsequent process from having to re-read the data.
      2) If a POSTCALCOBJECT is present, the values are passed thru memory to the POSTCALCOBJECT.
The VALUE Clause is especially useful for virtual data and for calculated values.

The CALCOBJECT Clause specifies a Calc Object that operates on data as it is being read to calculate variables based on the incoming data.

The POSTCALCOBJECT Clause specifies a Calc Object that operates on the output of the RELATEX. There are several types of functions that can be performed. For example:
      . Compute aggregate and grouping;
      . Compute other calculated values;
      . Create a result temporary table;
      . Perform a HAVING Clause
A POSTCALCOBJECT is functionally equivalent to
      . RELATEX
      . CALCOBJECT
but is more efficient.

Description of Parameter JoinInfoCount:

int JoinInfoCount is the number of JOININFO{} elements that are being passed in.

Description of Parameter ji:

JOININFO *ji is an array of JoinInfoCount JOININFO{} elements that are being passed in. The elements are in JOIN order.

Description of typedef struct JOININFO{}:

typedef struct JOININFO{} is a structure that is used to pass JOIN parameters into the ddlRelateEx() API function. The members of typedef struct JOININFO{} are:

char jiSchemaTableName1[288]	This is the Schema Name + Table Name of the Left Table of the JOIN. If Token Transparency is not active, this member conforms to the base Thunderbolt API naming conventions. In this case, the Schema Name, the dot notation and enclosing quotes are not allowed. If Token Transparency is active, this member conforms to the Token Transparency naming conventions; this includes the optional inclusion of the Schema Name, the dot notation and enclosing quotes.
char jiSchemaTableName2[288]	This is the Schema Name + Table Name of the Right Table of the JOIN. Refer to the previous item for format details. Note that the Right Table specified for the Nth element must reoccur as the Left Table of the N+1st element.
char jiAliasName[128]	Reserved for future use.
char jiJoinType[64]	The literal LEFT, RIGHT, FULL, INNER or CROSS.
char jiJoinCondition[800]	For all Join Types except CROSS, this is the Join Condition in the form: <JOIN-Term-1> [AND <JOIN-Term-2> [...]] Where: <JOIN-Term> ::= {<column1> <op> <column2> \| <column1> <op> <:constant>} <op> ::= {=, <=, <, >=, >, <>, LK, NLK} <column1> and/or <column2> may be modified by the ISNULL() operator as: ISNULL(<column>,<alternate-value>) <column1> and <column2> must refer to the Left table of this element or a preceeding table. Both names must be unambiguous. One or both of the field names may use the dot notation with a table name to prevent ambiguity. <column1> and <column2> must be the same data type and size; or their respective data types must be contained in the list of permitted cross data types. <constant> is a single string or numeric constant of the same type as <column1>; or multiple constants separated by commas. For all cases except <>, multiple constants are treated as an OR. For <>, multiple constants are treated as AND. By definition, CROSS JOIN does not have a Join Condition; meaning that this item would be null (empty) for CROSS Join Type. However, there is a special provision for CROSS JOIN that if the Join Condition parameter is not null, it is translated to a POSTCALCOBJECT in the form: RETURN IF NOT (condition) NOTEMIT
int jiJoinOrder	Reserved for future use. The default is 1 for the first element, 2 for the second element, etc.

The values within each JOININFO{} element (both singly and collectively) must reflect the FROM clause of a legal Ansi SQL query. For example, the legal Ansi SQL query fragment:
       SELECT ... FROM Orders JOIN Emp ON Orders.EmpID = Emp.EmpID
             JOIN Cust ON Orders.CustID = Cust.CustID
             JOIN Invoices ON Orders.InvoiceID = Invoices.InvoiceID ...

would be encoded (using the alternate encoding described in Notes below) as
       / Orders / Emp / INNER / Orders.EmpID = Emp.EmpID
             // Emp / Cust / INNER / Orders.CustID = Cust.CustID
             // Cust / Invoices / INNER / Orders.InvoiceID = Invoices.InvoiceID

Description:

ddlRelateEx() combines the records in the specified collection and the specified tables into a hierarchical order. The hierarchy is defined by the order of the tables in the JOININFO{} structure.

The result of ddlRelateEx() is an ordered global collection. The result collection is addressed in subsequent functions by the <cx> name without a table name. The result collection persists until either:
       1) It is explicitly destroyed by ddlClear();
       2) It is implicitly destroyed by a function that re-defines the collection; for example:
             ddlFill(), ddlFind(), etc
       3) It is superceeded by another ddlRelate(), ddlRelateEx();

When the ddlRelateEx() function includes a POSTCALCOBJECT, creation of the result collection can be modified by the Calc Object NOTEMIT statement.

Values returned in Control Structure:

Control.Ans1	Number of records in final RELATed collection
Control.Ans2	Initial Number of records in unRELATed collection
Control.Ans3	Number of frames (logical records) in result
Control.Ans1L	Number of records in final RELATed collection (64 bit)
Control.Ans2L	Initial Number of records in unRELATed collection (64 bit)
Control.Ans3L	Number of frames (logical records) in result (64 bit)

Notes:

When called thru ddlCommand(), or from ddtl, or from TQL, the JOININFO{} data can be specified in an alternate fashion.

In this case, the syntax is:

   RELATEX <ArgList> <JoinInfoList>
      where <JoinInfoList> is in the form:
         / <SchemaTableName1> / <SchemaTableName2> / <Join Type> / <JoinCondition> /

Advanced ddlRelateEx() API Functionality