Denorm Syntax | Vantage Analytics Library - Syntax - Vantage Analytics Library

Call td_analyze (
	'denormalize',			
	'database = val_source; 
	tablename = accounts; 
	indexretaincolumns = cust_id; 
	indexremovecolumns = acct_type; 
	denormalizecolumns = ending_balance;
	Optional Parameters;'
);

The following example of generated SQL and denormalized DATA are referred to later in this section.

Generated SQL

SELECT
 "cust_id"

,MIN(CASE WHEN "acct_type" = 'CC' THEN "ending_balance" ELSE NULL END) AS "CC_ending_balance"
,MIN(CASE WHEN "acct_type" = 'CK' THEN "ending_balance" ELSE NULL END) AS "CK_ending_balance"
,MIN(CASE WHEN "acct_type" = 'SV' THEN "ending_balance" ELSE NULL END) AS "SV_ending_balance"

FROM "val_source"."accounts"
GROUP BY "cust_id"
;

Denormalized DATA

Required Parameters

database

The database name containing the table to analyze.

denormalize

The denormalize parameter must meet the following conditions:

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

denormalizecolumns

A list of columns to denormalize (such as, duplicating the values from removed index columns).

In the Generated SQL and Demoralized DATA examples at the beginning of this section, the column ending_balance is a denormalized column. A single column like ending_balance becomes in this example three columns with a prefix for each removed index column value (CC, CK, and SV).

indexremovecolumns

A list of index columns to remove (denormalize by the values of these columns). You can remove up to three index columns that correspond to valuestodenorm, valuestodenorm2, and valuestodenorm3 (described later in this section).

In the Generated SQL example at the beginning of this section, acct_type is defined by the indexremovecolumns parameter and CC, CK, and SV are the column values used in denormalizing. The indexremovecolumns parameter values together with the denormalizecolumns parameter also form default prefixes for the new columns.

indexretaincolumns

Index columns to retain (not remove) in the resulting denormalized table.

You can assign an alias here for an indexretaincolumns column. For example, assigning alias 'cid' to column cust_id:

indexetaincolumns=cust_id/cid;

In the Generated SQL and Denormalized DATA examples at the beginning of this section, cust_id is the index to retain.

tablename

The table containing the data to denormalize. It must reside in the database indicated by the database parameter.

Optional Parameters

aggregation

Specify an aggregation method when new columns have multiple values to choose from. Setting an aggregation method is useful only when there are non-unique index values or when a part of a multi-part index is being ignored (for example, neither retained nor removed. In other cases, the result is the same no matter what aggregation method is being employed.)

In the Generated SQL example at the beginning of this section the aggregation method used was the default method MIN (Minimum). Refer to the retaincolumns example in the Denorm Examples section.

Supported aggregation functions include the following:

• AVG
• SUM
• MIN (default)
• MAX
• COUNT

Not all aggregation methods are valid for all denormalizecolumns data types. The following rules apply:

• AVG and SUM are only valid with numeric data. The numeric data can be contained in a character column that is purely numeric.
• AVG and SUM are not valid with date data.
• MIN and MAX return the same data type as the input data type.
• COUNT returns the number of qualifying rows.

compress

Used to compress undefined denormalize column values in the output table. Requests data compression of either NULL or 0 values depending on the value of the nullvalues parameter.

The compress parameter is only used when the output style is table or volatile. The use of the select or view output style results in an error.

Valid values are true and false (default).

The following is an example of the compress parameter and the resulting SQL. It corresponds to the denormalizecolumns example results with the addition of compression.

Call:

call td_analyze('denormalize','
database=val_source;
tablename=accounts;
indexretaincolumns=cust_id;
indexremovecolumns=acct_type;
valuestodenorm=CC,CK,SV;
denormalizecolumns=ending_balance;
outputstyle=table;
outputdatabase=val_results;
outputtablename=myCompress;
compress=true;
');

SQL:

DROP TABLE "val_results"."myCompress";
 CREATE SET TABLE "val_results"."myCompress", NO FALLBACK, NO BEFORE JOURNAL, NO AFTER JOURNAL
(
 "cust_id"
,"CC_ending_balance" COMPRESS NULL
,"CK_ending_balance" COMPRESS NULL
,"SV_ending_balance" COMPRESS NULL
)
 AS (
SELECT
"cust_id"
,MIN(CASE WHEN "acct_type" = 'CC' THEN "ending_balance" ELSE NULL END) AS "CC_ending_balance"
,MIN(CASE WHEN "acct_type" = 'CK' THEN "ending_balance" ELSE NULL END) AS "CK_ending_balance"
,MIN(CASE WHEN "acct_type" = 'SV' THEN "ending_balance" ELSE NULL END) AS "SV_ending_balance"

FROM "val_source"."accounts"
GROUP BY "cust_id"
) WITH DATA PRIMARY INDEX ("cust_id");

fallback

When true, a mirrored copy of the output table in the Database Engine 20 is requested when outputstyle=table. (See the note about fallback in Welcome to Vantage Analytics Library.)

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.

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;

nullvalues

Used to specify 0 instead of NULL (default) for undefined denormalized column values. If a DATE column is specified as a Denormalize column and 0 is selected, an error occurs.

In the Generated SQL example in the beginning of this section, setting nullvalues=0 replaces ELSE NULL with ELSE 0.

There are two valid values for this parameter: NULL (default) and 0.

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.

prefixes

Specify the prefixes for newly created columns, otherwise, the system generates the prefixes by default using one of the three valuestodenorm parameters, as applicable.

If the combination of the prefix and the longest denormalized column name (or alias) is greater than 128 characters in length, use the prefix parameter to specify a shorter prefix that does not exceed the 128 character limit. As an alternative, specifying a short alias for the denormalized column name avoids future automatic prefix generations from exceeding the character limit.

Using the examples at the beginning of this section, the defaulted prefixes are CC_, CK_, and SV_, which can also be specified as prefixes=CC_,CK_,SV_. Without the underscore, these are the values of one of the valuestodenorm parameters and are the values of the acct_type column defined in indexremovecolumns.

retaincolumns

List of already denormalized columns to retain. For example, having a constant value over the selected values of removed key columns. Refer to the retaincolumns example in the Denorm Examples section.

If the column or columns listed here are not already denormalized, a larger-than-expected output table can result.

You can assign an alias for a retaincolumns column. For example, assigning alias 'an' to column acct_nbr:

retaincolumns=acct_nbr/an;

valuestodenorm

The first column values in indexremovecolumns. Each value listed here, in whole or in combination with the other values parameters, results in a denormalized column.

The valuestodenorm parameter is linked with the parameters valuestodenorm2 and valuestodenorm3. Considering these three parameters in order, you can use the first by itself, the first two together, or all three. No other combinations are allowed.

If a denorm value is an empty string, it can be specified by leaving the value out of the valuestodenorm list as the following example shows. The same applies to the parameters valuestodenorm2 and valuestodenorm3.

Cases that work:

String value	Description
valuestodenorm=,A	Results in 2 values, empty string, and A
valuestodenorm=,A,B;	Results in 3 values, empty string, A and B
valuestodenorm=A,,C;	Results in 3 values, A, empty string, and C

Cases that do not work:

String value	Description
valuestodenorm=A,;	Only A results; change the order to ,A
valuestodenorm='';	Cannot specify with quote marks; needs at least 2 values
valuestodenorm=;	Invalid name and value pair error

An SQL literal consisting of all space characters is treated the same as one containing no space characters, such as an empty string.

In the following example, CC, CK, and SV are valuestodenorm and acct_type is the indexremovecolumns that assumes these values. The default prefixes CC_, CK_, and SV_ are used.

Call:

call td_analyze(
	'denormalize','
	database=val_source;
	tablename= accounts;
	indexretaincolumns=cust_id;
	indexremovecolumns=acct_type;
	valuestodenorm=CC,CK,SV;
	denormalizecolumns=ending_balance;
');

SQL:

SELECT
	 "cust_id"
	,MIN(CASE WHEN "acct_type" = 'CC' THEN "ending_balance" ELSE NULL END) AS "CC_ending_balance"
	,MIN(CASE WHEN "acct_type" = 'CK' THEN "ending_balance" ELSE NULL END) AS "CK_ending_balance"
	,MIN(CASE WHEN "acct_type" = 'SV' THEN "ending_balance" ELSE NULL END) AS "SV_ending_balance"

FROM "val_source"."accounts"
GROUP BY "cust_id"
;

valuestodenorm2

The second column values in indexremovecolumns. Each value listed here, in whole or in combination with the other values parameters, results in a denormalized column.

The valuestodenorm2 parameter is linked with the parameters valuestodenorm and valuestodenorm3. Considering these three parameters in order, you can use the first by itself, the first two together, or all three. No other combinations are allowed.

In the following example, CC, CK, and SV are valuestodenorm while Y and N are valuestodenorm2, and acct_type and account_active are the indexremovecolumns that assume these values. The default prefixes are used, such as CC_Y_ and SV_N_.

Call:

call td_analyze(
	'denormalize','
	database=val_source;
	tablename=accounts;
	indexretaincolumns=cust_id;
	indexremovecolumns=acct_type,account_active;
	valuestodenorm=CC,CK,SV;
	valuestodenorm2=Y,N;
	denormalizecolumns=ending_balance;
');

SQL:

SELECT

	 "cust_id"
	,MIN(CASE WHEN "acct_type" = 'CC' AND "account_active" = 'Y' THEN "ending_balance" ELSE NULL END) AS "CC_Y_ending_balance"
	,MIN(CASE WHEN "acct_type" = 'CK' AND "account_active" = 'Y' THEN "ending_balance" ELSE NULL END) AS "CK_Y_ending_balance"
	,MIN(CASE WHEN "acct_type" = 'SV' AND "account_active" = 'Y' THEN "ending_balance" ELSE NULL END) AS "SV_Y_ending_balance"
	,MIN(CASE WHEN "acct_type" = 'CC' AND "account_active" = 'N' THEN "ending_balance" ELSE NULL END) AS "CC_N_ending_balance"
	,MIN(CASE WHEN "acct_type" = 'CK' AND "account_active" = 'N' THEN "ending_balance" ELSE NULL END) AS "CK_N_ending_balance"
	,MIN(CASE WHEN "acct_type" = 'SV' AND "account_active" = 'N' THEN "ending_balance" ELSE NULL END) AS "SV_N_ending_balance"

FROM "val_source"."accounts"
GROUP BY "cust_id";

Refer to the valuestodenorm information for a description on how to handle empty strings.

valuestodenorm3

The values of the third column in indexremovecolumns. Each value listed here, in whole or in combination with the other values parameters, results in a denormalized column.

The valuestodenorm3 parameter is linked with the parameters valuestodenorm and valuestodenorm2. Considering these three parameters in order, you can use the first by itself, the first two together, or all three. No other combinations are allowed.

The following example is using the valuestodenorm2 example with the addition of the third parameter, valuestodenorm3:

indexremovecolumns=acct_type,account_active, mycolumn;
valuestodenorm=CC,CK,SV;
valuestodenorm2=Y,N;
valuestodenorm3=myvalue1,myvalue2;

Refer to the valuestodenorm information for a description on how to handle empty strings.

where

Requests that the generated SQL contain the specified WHERE clause. The where parameter is independent of the output style requested. Do not include the leading keyword WHERE in the parameter text.