User Guide

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

Back Next

Merging USTs

A virtual input table file 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 VirtualTable,FileNo
       MERGED_FROM [connection_string] merge_clause
       [TRANSFORM transform_clause] ...
[NOSYNC]
;

Source Clause

Refer to the Source Clause
description in the

Single UST

section.

Transform Clause

Refer to the Transform Clause
description in the

Single UST

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 virtual table column names, formats, lengths and order.

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

tgt_flds

defined in the first pair. They can not specify format, length nor order. The

tgt_fld

names must match those defined by the first pair.

Example

The virtual input table

SQLinput

is to be created from data extracted from tables

EXEMPL

and

EMPLOYEES

. These tables are found on different databases (mars and jupiter respectively).

The common layout of the virtual table is defined by the first

merge_clause / transform_clause

pair as:


MyId   C(10)
NAME   C(50)
Addr   C(50)
SSN    format/length same as EXEMPL.SSN

Columns

ADDR_L1

ADDR_L2

ZIP

from table

EXEMPL

will be concatenated to form a value for Addr.

Columns

GIVEN

and

FAMILY

from table

EMPLOYEES

will be concatenated to form a value for

NAME

. The field

EMPNO

EMPLOYEES

maps to

SSN


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

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

Rename Saved Search

Table of Contents

User Guide

User Guide

Merging USTs

Merging USTs

Source Clause

Transform Clause

Merge Clause