leftouterjoin: syntax and options

The syntax and options of the leftouterjoin join operator.

The syntax for the leftouterjoin operator is:


leftouterjoin
  -key field_name [-cs | -ci] [-param params]
  [-key field_name [-cs | -ci] [-param params] ...]
  [-collation_sequence locale | collation_file_pathname | OFF]

There is one required option, -key. You can specify it multiple times. Only top-level, non-vector fields can be keys.

Table 1. leftouterjoin Operator Option
Option Use
-key -key field_name [-ci or -cs] [-param params ]

Specify the name of the key field or fields.

You can specify multiple keys. For each one, specify the -key option and supply the key's name.

By default, InfoSphere® DataStage® interprets the value of key fields in a case-sensitive manner. Specify -ci to override this default. Do so for each key you choose, for example:

-key A -ci -key B -ci

The -param suboption allows you to specify extra parameters for a field. Specify parameters using property = value pairs separated by commas.
-collation_ sequence -collation_sequence locale | collation_file_pathname | OFF

This option determines how your string data is sorted. You can:

  • Specify a predefined IBM® ICU locale
  • Write your own collation sequence using ICU syntax, and supply its collation_file_pathname
  • Specify OFF so that string comparisons are made using Unicode code-point value order, independent of any locale or custom sequence.

By default, InfoSphere DataStage sorts strings using byte-wise comparisons.

For more information, see reference this IBM ICU site:

http://oss.software.ibm.com/icu/userguide

/Collate_Intro.html