User Guide

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

Back Next

Joining USTs

A virtual input table may be created by joining two or more USTs from a single source database. This would normally be done if the source tables were normalized into multiple tables but the IDT requires data from all source tables. The syntax is identical to the Single USTs syntax with the addition of a

join_expression


CREATE_IDT VirtualName,FileNo
       SOURCED_FROM connection_string
             source_clause
       [TRANSFORM transform_clause]
       JOIN_BY join_expression
       [SELECT_BY select_expression]
       [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.

Join Expression

The primary table
is the first UST table mentioned in the

SOURCED_FROM

clause. A secondary table is a UST joined to the primary table using a foreign key stored in the primary.

join_expression

is used to specify how to join the primary table to one of the secondary tables or more generally, how to join a parent table to a child.

join_expression

must be provided for each pair of tables to be joined. It defines the relationship between tables, where the parent contains the foreign key of the child.

It is of the form,

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

where,

parent_table_column: This is a fully qualified column name in the parent table
.

child_table_column: This is a fully qualified column name in the child table
.

p_col: This is an unqualified column name in the parent table
(for compound keys).

c_col: This 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 created virtual table as non-virtual fields.

An outer join is performed on the primary table
, meaning that all rows in the primary table will be added to the virtual table even if they failed to be joined to any secondary tables. In this case, the columns extracted from the secondary table are set to NULL.

Example

The virtual input table

SQLinput

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

NAMEADDR

and

OTHERDETAILS

. 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

NAMEADDR

and the

SSN

column in table

OTHERDETAILS

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
        SQLinput,50
SOURCED_FROM ora:99:scott/tiger@server734
        #SSA_UID#.NAMEADDR.EMPNO            EmpNo N(25),
        #SSA_UID#.NAMEADDR.given            $given,
        #SSA_UID#.NAMEADDR.family           $family,
        #SSA_UID#.NAMEADDR.ADDR             $addr,
        #SSA_UID#.OTHERDETAILS.SSN,
        #SSA_UID#.OTHERDETAILS.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#.NAMEADDR.EMPNO = #SSA_UID#.OTHERDETAILS.SSN
NOSYNC
;

Rename Saved Search

Table of Contents

User Guide

User Guide

Joining USTs

Joining USTs

Source Clause

Transform Clause

Join Expression