Merge Syntax | Vantage Analytics Library - Syntax - Vantage Analytics Library

Call td_analyze (
	'merge',
	'database = val_source,val_source; 
	tablename = customer,customer_analysis; 
	columns = {cust_id,age},{cust_id,age}; 
	Optional Parameters;'
);

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 the first list are the columns that appear in the selected list of the merged 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 Merge with UNION example, cust_id and income are selected from the customer and customer_analysis tables, assigning the aliases cid and inc respectively in the first table.

database

The database parameter contains a list of the databases that contain the input tables to merge.

In the Merge with UNION 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

merge

The merge parameter must meet the following conditions:

• Is required
• Is the first parameter
• Always enclosed in single quotes

tablename

The list of tables to be merged, 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 Merge with UNION example, the tables being merged are customer and customer_analysis, which happen to be in the same database (val_source).

Optional Parameters

addidcolumn

When the Union merge style is requested, an option is provided to add an identifying column to the answer set. This column assumes an integer value from 1 to n to indicate the input table each row in the answer set comes from. By default, this column is named MERGEID, or a name may be given by using the optional idcolumnname parameter.

addidcolumn ={ true|false }

columnstoexclude

The columns, if any, to exclude during processing when the parameter columns=all is specified. (Note that columnstoexclude can only be used when columns=all.) While this parameter is valid in many functions, take note of the following considerations when using the Merge function:

• Each input table must have the same number of columns
• The columns in the first table determine the column names and types in the result table

Refer to the columnstoexclude and UNION 3 Tables examples to see this parameter in use.

fallback

Setting the fallback parameter to true requests a mirrored copy of the output table in the Analytics Database when outputstyle=table. (See note on Fallback in the Welcome to Vantage Analytics Library topic.)

gensqlonly

When set to 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.

idcolumnname

When is set to true, the idcolumnname parameter is enabled to give the column a desired name. (This parameter is only valid when addidcolumn=true, which in turn is only valid if the merge style is union.)

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.

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;

mergestyle

The following merge styles are allowed:

• Union
• Intersect
• Except

If mergestyle is not specified, a union operation is performed. (Refer to the Merge introduction for a detailed description of these merge operators.)

mergestyle={union|intersect|except}

multiset

Whether the output table can contain duplicate rows when outputstyle=table or volatile.

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.

retainduplicates

The retaining of duplicates is affected by the retainduplicates, multiset, and outputstyle parameters. Duplicates are retained based on the following criteria:

• Select: Duplicates are retained if retainduplicates=true
• Table: Duplicate rows are returned only if both retainduplicates and multiset are selected
• Volatile table: The retainduplicates parameter is ignored
• Output view: The multiset parameter is ignored

The use of this parameter results in the SQL keyword all following the merge style. That is, UNION ALL, INTERSECT ALL, or EXCEPT ALL is placed in the generated SQL.

where

Requests that the generated SQL contained the specified WHERE clause. (Do not include the leading keyword WHERE in the parameter text.)