Merge Examples | Vantage Analytics Library - Merge Examples - Vantage Analytics Library

Call:

call td_analyze('merge','
database=
	val_source,
	val_source;
tablename=
	customer,
	customer_analysis;
columns=
	{cust_id/cid, income/inc},
	{cust_id, income};
where=
	{cust_id < 1362490},
	{cust_id < 1362490};
');

SQL:

SELECT
 	 "cust_id" AS "cid"
	,"income" AS "inc"
FROM "val_source"."customer"
WHERE cust_id < 1362490
UNION
SELECT
 	 "cust_id"
	,"income" AS "income"
FROM "val_source"."customer_analysis"
WHERE cust_id < 1362490

Results:

Call:

call td_analyze('merge','
database=
	val_source,
	val_source;
tablename=
	customer,
	customer_analysis;
columns=
	{cust_id/cid, income/inc},
	{cust_id, income};
where=
	{cust_id < 1362490},
	{cust_id < 1362490};

outputstyle=table;
outputdatabase=val_results;
outputtablename=merge_example2;
index=cid;
');

SELECT:

select * from val_results.merge_example2 order by 1;

SQL:

DROP TABLE "val_results"."merge_example2";
CREATE SET TABLE "val_results"."merge_example2", NO FALLBACK, NO BEFORE JOURNAL, NO AFTER JOURNAL AS (
SELECT
 	 "cust_id" AS "cid"
	,"income" AS "inc"
FROM "val_source"."customer"
WHERE cust_id < 1362490
UNION
SELECT
 	 "cust_id"
	,"income" AS "income"
FROM "val_source"."customer_analysis"
WHERE cust_id < 1362490
) WITH DATA PRIMARY INDEX ("cid");

Results:

Call:

call td_analyze('merge','
database=
	val_source,
	val_source;
tablename=
	customer,
	customer_analysis;
columns=
	{cust_id/cid, income/inc},
	{cust_id, income};
where=
	{cust_id < 1362490},
	{cust_id < 1362490};

outputstyle=volatile;
outputtablename=merge_example3;
noindex=true;
');

SELECT:

select * from merge_example3 order by 1;

SQL:

DROP TABLE "val_user"."merge_example3";
 CREATE MULTISET VOLATILE TABLE "val_user"."merge_example3", NO FALLBACK, NO BEFORE JOURNAL, NO AFTER JOURNAL AS (
SELECT
 	 "cust_id" AS "cid"
	,"income" AS "inc"
FROM "val_source"."customer"
WHERE cust_id < 1362490
UNION
SELECT
 	 "cust_id"
	,"income" AS "income"
FROM "val_source"."customer_analysis"
WHERE cust_id < 1362490
) WITH DATA NO PRIMARY INDEX ON COMMIT PRESERVE ROWS;

Results:

Call:

call td_analyze('merge','
database=
	val_source,
	val_source;
tablename=
	customer,
	customer_analysis;
columns=
	{cust_id/cid, income/inc},
	{cust_id, income};
where=
	{cust_id < 1362490},
	{cust_id < 1362490};

outputstyle=view;
outputdatabase=val_results;
outputtablename=merge_example4;
');

SELECT:

select * from val_results.merge_example4 order by 1;

SQL:

DROP VIEW "val_results"."merge_example4";
CREATE VIEW "val_results"."merge_example4" AS (
SELECT
 	 "cust_id" AS "cid"
	,"income" AS "inc"
FROM "val_source"."customer"
WHERE cust_id < 1362490
UNION
SELECT
 	 "cust_id"
	,"income" AS "income"
FROM "val_source"."customer_analysis"
WHERE cust_id < 1362490
);

Results:

This example demonstrates the use of the columns=all and columnstoexclude parameters together. Note the following output differences of the two merged input tables as compared to the previous merged table examples:

• The checking_acct table contains the per_check_fee column that is not present in the savings_acct table.
• The savings_acct table contains the acct_type column that is not present in the checking_acct table.
• Each table contains a WHERE clause limiting the output to 12 returned rows.

Call:

call td_analyze('merge','
database=
        val_source,
        val_source;
tablename=
        checking_acct,
        savings_acct;
columns=all;
columnstoexclude=
        {per_check_fee},
        {acct_type};
where=
        {cust_id < 1362490},
        {cust_id < 1362490};
');

SQL:

SELECT
 	 "cust_id"
	,"acct_nbr"
	,"minimum_balance"
	,"account_active"
	,"acct_start_date"
	,"acct_end_date"
	,"starting_balance"
	,"ending_balance"
FROM "val_source"."checking_acct"
WHERE cust_id < 1362490
UNION
SELECT
 	 "cust_id"
	,"acct_nbr"
	,"minimum_balance"
	,"account_active"
	,"acct_start_date"
	,"acct_end_date"
	,"starting_balance"
	,"ending_balance"
FROM "val_source"."savings_acct"
WHERE cust_id < 1362490
;

Results:

This example demonstrates the merging of three account tables: checking, credit, and savings. With mergestyle=union, the UNION operator appears in two places in the generated SQL. The columns=all and columnstoexclude parameters are convenient to use because most of the columns in the three tables are the same. There are also three WHERE clauses, one for each input table, to limit the number of rows returned to only 17 rows. The parameter addidcolumn=true includes a column MERGEID as the last column in the output table. The following values refer to which account table is indicated for MERGEID:

Call:

call td_analyze('merge','
mergestyle=union;
addidcolumn=true;
database=
        val_source,
        val_source,
        val_source;
tablename=
        checking_acct,
        credit_acct,
        savings_acct;
columns=all;
columnstoexclude=
        {minimum_balance, per_check_fee},
        {credit_limit, credit_rating},
        {minimum_balance, acct_type};
where=
        {cust_id < 1362490},
        {cust_id < 1362490},
        {cust_id < 1362490};
');

SQL:

SELECT
 	 "cust_id"
	,"acct_nbr"
	,"account_active"
	,"acct_start_date"
	,"acct_end_date"
	,"starting_balance"
	,"ending_balance"
	,1 AS MERGEID
FROM "val_source"."checking_acct"
WHERE cust_id < 1362490
UNION
SELECT
 	 "cust_id"
	,"acct_nbr"
	,"account_active"
	,"acct_start_date"
	,"acct_end_date"
	,"starting_balance"
	,"ending_balance"
	,2 AS MERGEID
FROM "val_source"."credit_acct"
WHERE cust_id < 1362490
UNION
SELECT
 	 "cust_id"
	,"acct_nbr"
	,"account_active"
	,"acct_start_date"
	,"acct_end_date"
	,"starting_balance"
	,"ending_balance"
	,3 AS MERGEID
FROM "val_source"."savings_acct"
WHERE cust_id < 1362490
;

Results:

Call:

call td_analyze('merge','
database=
	val_source,
	val_source;
tablename=
	customer,
	customer_analysis;
columns=
	{cust_id, income},
	{cust_id, income};
where=
	{cust_id < 1362490},
	{cust_id < 1362485};
mergestyle=except;
');

SQL:

SELECT
 	 "cust_id"
	,"income" AS "income"
FROM "val_source"."customer"
WHERE cust_id < 1362490
EXCEPT
SELECT
 	 "cust_id"
	,"income" AS "income"
FROM "val_source"."customer_analysis"
WHERE cust_id < 1362485
;

Results:

Call:

call td_analyze('merge','
database=
	val_source,
	val_source;
tablename=
	customer,
	customer_analysis;
columns=
	{cust_id, income},
	{cust_id, income};
where=
	{cust_id < 1362490},
	{cust_id < 1362485};
mergestyle=intersect;
');

SQL:

SELECT
 	 "cust_id"
	,"income" AS "income"
FROM "val_source"."customer"
WHERE cust_id < 1362490
INTERSECT
SELECT
 	 "cust_id"
	,"income" AS "income"
FROM "val_source"."customer_analysis"
WHERE cust_id < 1362485
;

Results: