Join Syntax | Vantage Analytics Library - Syntax - Vantage Analytics Library

Vantage Analytics Library User Guide
Deployment
VantageCloud
VantageCore
Edition
VMware
Enterprise
IntelliFlex
Lake
Product
Vantage Analytics Library
Release Number
2.2.0
Published
June 2025
ft:locale
en-US
ft:lastEdition
2025-07-02
dita:mapPath
ibw1595473364329.ditamap
dita:ditavalPath
iup1603985291876.ditaval
dita:id
zyl1473786378775
Product Category
Teradata Vantage

Required Parameters

columns
The list of input columns, one list for each input table. (A column list is denoted by inclusion in braces { }, separated by commas.) The columns in these lists are also the columns that appear in the select list of the joined output table.
An alias can be associated with any of these input columns by following the name with a forward slash followed by the alias name. For example, cust_id/cid associates the alias cid with the column cust_id. Aliases for columns other than the first table are ignored.
For convenience, the keyword all is used to select all of the columns in the input table. When columns=all is entered, all columns from all of the input tables are processed. Exceptions are specified by using the optional columnstoexclude parameter.
In the inner join example, cust_id and income are selected from the customer table, and cc_rev is selected from the customer_analysis table.
database
The database parameter contains a list of the databases that contain the input tables to join.
In the inner join example, the first database in the list (val_source) corresponds to the first table (customer), and the second database in the list to the second table (customer_analysis). Note that the databases in the list may each be the same or different than the other databases in the list.
join
The join parameter must meet the following conditions:
  • Is required
  • Is the first parameter
  • Is always enclosed in single quotes
tablename
The list of tables to be joined, matched one-for-one with the list of databases in the database parameter, as well as the list of column lists in the columns parameter.
In the inner join example, the tables being joined are customer and customer_analysis, which happen to be in the same database (val_source).

Optional Parameters

gensqlonly
When true, the SQL for the requested function is returned as a result set but not run. When not specified or set to false, the SQL is run but not returned.
index
When a list of columns is specified, the index parameter requests that the output table contain the index columns when outputstyle=table or volatile. Either index or the noindex parameters are required with the table or volatile output styles.
indexunique
When true, requests that the output table contain a unique primary index when outputstyle=table or volatile.
joincolumns
Specifies the columns to join the tables or views in this analysis. For tables, the primary index columns are used as default values which may be overridden. Join columns are matched for each table or view, one-for-one in the order specified. Each table or view must therefore have the same number of join columns specified.
The following example is of a joincolumns phrase with two columns for each of the two tables, with col1 and col2 belonging to the first input table, and colA and colB to a second input table:
joincolumns={col1,col2}, {colA,colB}
You can view the join columns or primary index defaults as they appear in the ON clauses of the generated SQL along with their table aliases.
joinstyle
The following joinstyles are allowed:
  • Inner
  • Leftouter
  • Rightouter
  • Fullouter
If joinstyle is not specified, an inner join is performed. Note that the same selected join style is performed for each input table.
joinstyle={inner|leftouter|rightouter|fullouter}
lockingclause
Requests that the generated SQL contain the specified locking clause. The following is an example of a locking clause when the output style defaults to select: 
LOCKING mydb.mytable FOR ACCESS;
noindex
Requests that the output table contains no index columns (NOPI) when outputstyle=table or volatile.
outputdatabase
The database containing the output table, used only if the outputstyle is table, view, or volatile.
outputstyle
The following output styles are allowed:
  • Select
  • Table
  • View
  • Volatile
If outputstyle is not specified, the function generates a SELECT statement and does not create a table or view.
outputstyle={select|table|view|volatile}
outputtablename
The output table containing the denormalized data, used only if the outputstyle is table, view, or volatile. If used, the table must reside in the database indicated by the outputdatabase parameter.
overwrite
When overwrite is set to true or not set, the output table is dropped before creating a new one.
where
Requests that the generated SQL contains the specified WHERE clause. (Do not to include the leading keyword WHERE in the parameter text.)
When joining a table by referring to it in a WHERE clause, you must refer to the table by using the alias generated by the function for the table. For example, WHERE=_joinTable_1.account_active=_joinTable_0.account_active.