Specify Column Values in a Relationship

The Relationship Editor lists the pairs of corresponding parent table and child table entries that make up the relationship.

Define or edit relationships by:

Note: When you edit a relationship definition, the Data Type and Status values are inserted automatically.

Restrictions

Although the rules for creating Optim™ relationships are more flexible than those for creating database-defined relationships, there are some restrictions:

In a Relationship definition for a multi-byte or Unicode database:

EXAMPLES:

Parent Supported/Not Supported Child Description
CHAR
supported
CHAR Supported, semantics must match
NCHAR
supported
NCHAR Supported, semantics irrelevant.
CHAR
not supported
NCHAR Not Supported
CHAR
supported
VARCHAR Supported, semantics must match.
NCHAR
supported
NVARCHAR Supported, semantics irrelevant.
CHAR||NCHAR
supported
NCHAR||CHAR Supported, if character semantics; not supported if byte semantics.
CHAR||NCHAR
supported
NCHAR||NCHAR Supported, if character semantics; not supported if byte semantics.

Violating these restrictions causes an error. For further details about column compatibility, see Compatibility Rules for Relationships.

Select Column Names from a List

Select Parent Columns or Child Columns from the Tools menu to add or replace column names. You can switch between the Parent Table Columns dialog and Child Table Columns dialog by selecting the appropriate command from the Tools menu in the Relationship Editor.

parent table columns dialog where you can select columns to include in the relationship
Note: Because the dialogs to select parent and child columns are identical, the Parent Table Columns dialog is used to explain dialog details.

Table

Fully qualified name of the parent table from which you can select names of columns to include in the relationship.

Column Name

To select from the list of columns, drag column names from the dialog to the appropriate grid cell in the Relationship Editor. The inserted column name replaces any value in the current grid cell. The data types of the paired columns in the editor are evaluated and the Status is automatically updated.

Change the column display by selecting View menu commands in the Parent Table Columns dialog:

  • To view a list of all columns in the table, select All.
  • To view all unused columns, select Unused. If the table contains many columns, it may be helpful to display only unused columns.

Data Type

Data type for each column in the parent table.

Edit Parent and Child Columns

You can specify or modify a column name by typing over the name or using shortcut menu commands to Insert, Remove, or Remove All column names.

In addition, you can specify expressions, including substrings, concatenations, constants and literals for either the parent or child table column in a relationship. This flexibility is one of the most powerful features of Optim relationships. You can use any of the following values:

Column Name
An explicit column name in the parent or child table. (Column names are case-insensitive.)
NULL
NULL.
Numeric Constant
A numeric constant. The value must conform to the data type, precision and scale defined for the column.
Boolean Constant
A Boolean constant as TRUE or FALSE.
String Literal
A string literal for a non-arithmetic value:
  • You can specify a string literal when the corresponding column contains character data.
  • You must enclose a string literal in single quotes.

You can define a string literal containing any characters, for example: 'CA' or '90210'.

Hexadecimal Literal
A hexadecimal literal: X‘1234567890ABCDEF'
or 0X12 34567890ABCDEF
Substring
A Substring Function to use a portion of a source column value.
Concatenated Expression
A Concatenated Character or Binary expression to obtain a derived value.

Substring Function

Use the Substring Function to select part of a column value for a relationship. The Substring Function returns a substring of the contents of the named column. The Substring Function format is:

SUBSTR(columnname, start, [length])

columnname
Name of a character or binary column.
start
The position of the first character in the string.
length
The number of characters to use.
  • If the locale uses a comma as the decimal separator, you must leave a space after each comma that separates numeric parameters (for example, after the comma between start and length.
  • start and length are integers greater than or equal to 1.
  • start plus length cannot exceed the total data length plus 1.
  • column-name and start value are required. If you specify only one integer, it is used as the start value. The substring begins at start and includes the remainder of the column value.

Example

If the PHONE_NUMBER column is defined as CHAR(10), you can use the Substring Function to map the area code. To obtain a substring of the first three positions of the phone number (area code) for the destination column, specify:

SUBSTR(PHONE_NUMBER, 1, 3)

Concatenated Expressions

Concatenation allows you to combine column values or combine a column value with another value, using a concatenation operator (CONCAT, ||, or +). A concatenated expression can include character or binary values, but not both:

Character Values
Concatenated character values can be character columns, string literals, or substrings of character columns.
Binary Values
Concatenated character values can be binary columns, hexadecimal literals, or substrings of binary columns.
Note: A concatenated expression cannot include a zero-length string literal (' ') or a special register.

Example 1

You can define relationships using different data structures. For example, the data in two or more columns in one table may correspond to data in a single column in another table. You can define this type of relationship using concatenation or the Substring Function.

Assume that an address in the CUSTOMERS table is in two columns, ADDRESS1 and ADDRESS2, and in one column, ADDRESS, in the SHIP_TO table. You can define a relationship between the two tables by concatenating the columns in the CUSTOMERS table:

CUSTOMERS Table SHIP_TO Table
ADDRESS1 || ADDRESS2 ADDRESS
ADDRESS1 CONCAT ADDRESS2 ADDRESS
ADDRESS1 + ADDRESS2 ADDRESS

Example 2

You can define the same relationship as in the prior example, using substrings:

CUSTOMERS Table SHIP_TO Table
ADDRESS1 SUBSTR(ADDRESS,1,25)
ADDRESS2 SUBSTR(ADDRESS,26,25)

To compare the results of using a concatenation operator or the Substring Function, examine the generated SQL in each case.

SQL for a relationship using the concatenation operator:

SELECT * FROM TABLE2
 WHERE TABLE2.ADDRESS = 'composite value from both parent columns'

SQL for the relationship defined using the Substring Function:

SELECT * FROM TABLE2
 WHERE SUBSTR(TABLE2.ADDRESS, 1,25)='value from parent ADDRESS1 
column'
 AND   SUBSTR(TABLE2.ADDRESS,26,25)='value from parent ADDRESS2 
column'

Complex SQL is generated in the substring example; the DBMS must scan all rows in the table to achieve the desired result and may not use an index, even if one exists. In general, concatenation provides better performance.

Data-Driven Relationships

A data-driven relationship exists when a row in the parent table is related to rows in one of several child tables on the basis of the value in a particular column. You can define a data-driven relationship using:

  • String literals or hexadecimal literals
  • Numeric Constants and Boolean Constants
  • NULL

For example, consider a pair of sample tables used to determine employee insurance rates for males and females. The EMPLOYEE table stores the identification, age, and gender details for each employee:

EMPLOYEE_ID AGE GENDER
058-44-2244 38 F
106-46-0620 40 M
248-91-2890 27 M

The FEMALE_RATES table contains insurance rates for women based on age; the MALE_RATES table contains insurance rates for men based on age:

FEMALE_RATES
AGE RATE
38 .25
39 .33
40 .43
MALE_RATES
AGE RATE
38 .31
39 .38
40 .47

Two relationships are needed, one for rows in the EMPLOYEE table that contain data about female employees, and one for rows in the EMPLOYEE table that contain data about male employees.

Rows in the EMPLOYEE table are related to the FEMALE_RATES table when the value in the GENDER column matches the string literal 'F'. The AGE column in the EMPLOYEE table corresponds to the AGE column in the FEMALE_RATES table.

EMPLOYEE FEMALE_RATES
GENDER 'F'
AGE AGE

In the second relationship, rows in the EMPLOYEE table are related to the MALE_RATES table. This relationship is identical to the first, except that the value in the GENDER column matches the string literal 'M'.

EMPLOYEE MALE_RATES
GENDER 'M'
AGE AGE

For any row in the EMPLOYEE table, only one of the relationships can be satisfied because the column EMPLOYEE.GENDER must be 'M' or 'F'. After the appropriate relationship is selected, the related rows are identified by comparing values in the AGE columns.