SQL Variables and Expressions

The syntax for using a SQL variable in an expression is

&ColumnName@LinkTemplate

For example, to retrieve the values in the PRICE column in the table referenced by the link template HOUSES, enter &PRICE@HOUSES.

To relate a second table to the table specified in the link template and return a value from one of its fields, use this syntax

&[ [ [catalog. ] schema. ] colname@LT [.lnka1, lnka2, ..., lnkaN [.lnkb1, lnkb2, ..., lnkbN ] ]

The expression must be entered on one line.

• The parameters preceding the @ symbol identify the related table. The LT parameter identifies the primary table. If no schema or catalog is defined, it is assumed the related table exists in the same schema as the primary table.
• The lnk parameter (or lnk1,lnk2,...,lnkN parameter list) identifies the column name (or names) to reference for the relation if you are not using the key or keys identified in the link template. If no lnk column is defined it is assumed that the columns identified in the link template exist in both tables and are referenced for the relation.
• The lnka parameter (or lnka1,lnka2,...,lnkaN parameter list) identifies the column name (or names) to use in the primary table.
• The lnkb parameter (or lnkb1,lnkb2,...,lnkbN parameter list) identifies the column name (or names) to use in the related table if the column name (or names) to reference for the relation are not the same in both tables. The number of lnka parameters and lnkb parameters must be the same.

SQL Expressions and SQL Statements

The expression:

&catalog.schema.table.colname @linktemplate.lnka1,lnka2,...,lnkaN.lnkb1,lnkb2,...,lnkbN

is equivalent to the following SQL statement:

SELECT catalog.schema.table.colname FROM catalog.schema.table WHERE lnka1 = lnkb1 AND lnka2 = lnkb2 AND ... AND lnkaN = lnkbN AND linktemplatekey1 = linktemplatevalue1 AND linktemplatekey2 = linktemplatevalue2 AND ... AND linktemplatekeyN = linktemplatevalueN

where lnka1,...,lnkaN are columns from the primary table, lnkb1,...,lnkbN are columns from the secondary table, linktemplatekey1,...,linktemplatekeyN are link template key columns from the primary table, and linktemplatevalue1,...,linktemplatevalueN are the key column values obtained from the object.