Design Guide

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

Back Next

Merged_From

An IDT can be created by merging the contents of two or more User Source Tables. Multiple heterogeneous source databases are permitted. The columns extracted from the tables are mapped into a common format using multiple

merge_clause

and

transform_clause

pairs (one pair per UST).


CREATE_IDT IDT-Name
MERGED_FROM [connection_string] merge_clause
[TRANSFORM transform_clause] ...
SYNC | NOSYNC
;

Primary Key

The

(PK)

column must contain unique values within a given User Source Table. However it does not need to be unique within the merged IDT because MDM-RE automatically qualifies the

(PK)

column so that it can identify which source table the record came from. To do this, MDM-RE automatically adds a column called

IDS_PARSE_BLOCK_NO

to the IDT and populates it with the

merge_clause

occurrence number (starting from 1) used to create each IDT record. This column name is reserved and can not be specified as a

tgt_col

name.

Source Clause

Refer to the Source Clause description in the Create_IDT section.

Transform Clause

Refer to the Transform Clause description in the Create_IDT section.

Merge Clause

The

merge_clause

is identical to the

source_clause

in syntax but its semantics differ:

The first

merge_clause/transform_clause

pair is used to define the IDT column names, formats, lengths and order. It also nominates the primary-key field

(PK)

The second and subsequent pairs define the mapping from source columns in other USTs to the

tgt_cols

defined in the first pair. They cannot specify format, length,

nor order.

tgt_col

names must match those defined by the first pair.

All columns in a

merged_from

clause must come from the same source table.

Example

The IDT

idt_xform_merge

is to be created from data extracted from tables

TEXTX51A

and

TESTX51B

. These tables are found on different databases (

mars

and

jupiter

respectively).

The common layout of the IDT is defined by the first

merge_clause/transform_clause

pair as:

MyIdC(10)

NAMEC(50)

AddrC(50)

SSNformat/length same as

TESTX51A.SSN

Columns

ADDR_L1, ADDR_L2, ZIP

from table

TESTX51A

will be concatenated to form a value for

Addr

Columns

GIVEN

and

FAMILY

from table

TESTX51B

will be concatenated to form a value for

NAME

. The field

EMPNO

TESTX51B

maps to

SSN


CREATE_IDT
       idt_xform_merge
MERGED_FROM odb:99:scott/tiger@mars
       #SSA_UID#.TESTX51A.FULL_NAME           NAME C(50),
       #SSA_UID#.TESTX51A.SSN        (PK)     SSN,
       #SSA_UID#.TESTX51A.ADDR_L1             $a1,
       #SSA_UID#.TESTX51A.ADDR_L2             $a2,
       #SSA_UID#.TESTX51A.ZIP                 $zipcode
TRANSFORM
       source-id                              MyId c(10) order 1,
       concat($a1,$a2,$zipcode)               ADDR c(50) order 3

MERGED_FROM odb:99:buzz/lightyear@jupiter
       #SSA_UID#.TESTX51B.EMPNO    SSN,
       #SSA_UID#.TESTX51B.GIVEN    $given,
       #SSA_UID#.TESTX51B.FAMILY   $family,
       #SSA_UID#.TESTX51B.ADDR     ADDR
TRANSFORM
       concat($given,$family) NAME
SYNC
;

Rename Saved Search

Table of Contents

Design Guide

Design Guide

Merged_From

Merged_From

Primary Key

Source Clause

Transform Clause

Merge Clause