Design Guide

10.5 HotFix 2
- 10.5 HotFix 1
- 10.5
- 10.2 HotFix 1
- 10.2
- 10.1
- 10.0 HotFix 1
- 10.0

Back Next

Join_By

You can create an IDT by joining two or more USTs from a single source database. This is normally done when the source tables are normalized into multiple tables and the IDT needs to support search and matching strategies by using data from all source tables.

The syntax is identical to the syntax of a single UST with the addition of

join_expression


CREATE_IDT IDT-Name
SOURCED_FROM connection_string source_clause
[TRANSFORM   transform_clause]
JOIN_BY      join_expression
[SELECT_BY   select_expression]
SYNC | NOSYNC
;

You cannot join columns that are of binary data types.

Source Clause

For more information about the source clause, see the Create_IDT section.

Transform Clause

For more information about the transform clause, see the Create_IDT section.

Join Expression

The primary table is a UST that you mention in the

sourced_from

clause. A secondary table is another UST that you join to the primary table by using a foreign key stored in the primary.

Perform an outer join on the primary table by adding all the rows in the primary table to the IDT even if any row fails to join to any secondary table. In this case, the columns extracted from the secondary table are set to NULL.

Use join_expression
to specify how to join the primary table to one of the secondary tables or more. Generally, it specifies how to join a parent table to a child. Specify join_expression
for each pair of tables that you plan to join. The expression defines the relationship between the tables, where the parent contains the foreign key of the child.

Use the following format for join_expression
:

parent_table_column = child_table_column [AND p_col = c_col], . . .

where,

parent_table_column
is a fully qualified column name in the

parent table

child_table_column
is a fully qualified column name in the

child table

p_col
is an unqualified column name in the

parent table

(for compound keys).

c_col
is an unqualified column name in the

child table

(for compound keys).

All parent_table_columns
specified in a join_expression
must be included in the IDT as non-virtual fields. For performance reasons, it is recommended that parent_table_columns
are indexed. If

SYNC

is also specified, all join fields must be indexed.

Example

The IDT

idt_xform

is to be created. Columns are extracted from two tables,

TESTX50A

and

TEXTX50B

. These tables belong to schema

#SSA_UID#

which is evaluated at parse time using the environment variable

SSA_UID

The tables are joined using the

EMPNO

column in

TESTX50A

and the

SSN

column in table

TESTX50B

Various transformations are used. Columns

given

and

family

are concatenated to form a field called

NAME

. The

order

parameter is used to change the default ordering of fields in the IDT to:

myid, NAME, EMPNO, SSN, TITLE, Addr

, and

Phone


CREATE_IDT
		  idt_xform
SOURCED_FROM odb:99:scott/tiger@server8.17
#SSA_UID#.TESTX50A.EMPNO     (PK) 			EmpNo N(25),
     #SSA_UID#.TESTX50A.given 			        $given,
     #SSA_UID#.TESTX50A.family 		        $family,
     #SSA_UID#.TESTX50A.ADDR 				        $addr,
     #SSA_UID#.TESTX50B.SSN,
     #SSA_UID#.TESTX50B.PHONE 			        $phone
TRANSFORM
     source-id                           myid     f(10) order 1,
	  insert-constant("hello there")        title    c(15),
     upper($addr)                        Addr     c(30),
     lower($phone)                       Phone    c(12),
     concat($given,$family)              NAME     c(50) order 2
JOIN_BY
     #SSA_UID#.TESTX50A.EMPNO = #SSA_UID#.TESTX50B.SSN
NOSYNC
;

Rename Saved Search

Table of Contents

Design Guide

Design Guide

Join_By

Join_By

Source Clause

Transform Clause

Join Expression