Example - With Recursive View - Teradata Warehouse Miner

Teradata Warehouse Miner User Guide - Volume 2ADS Generation

Product
Teradata Warehouse Miner
Release Number
5.4.4
Published
July 2017
Language
English (United States)
Last Update
2018-05-03
dita:mapPath
fcf1492702067123.ditamap
dita:ditavalPath
ft:empty
dita:id
B035-2301
Product Category
Software

To change the example above into a With Recursive View, first select the base query option to Store the tabular output of this analysis in the database on the Output - storage tab and select the Output Type View (the Output Name will automatically be set to Referral Seed, the name of the seed query analysis). Next, remove all variable definitions on the Input - variables tab.

This modified base query will generate the following SQL to create a Recursive View.

DROP VIEW "twm_results"."Referral Seed";
CREATE RECURSIVE VIEW "twm_results"."Referral Seed" ("cust_id", "level")  AS
(SELECT
	 "_twmSQ0"."cust_id" AS "cust_id"
	,0 AS "level"
FROM "twm_results"."_twm_referral" AS "_twmSQ0"
WHERE "_twmSQ0"."referred_by" = 1362480
UNION ALL
SELECT
	 "_twmVC0"."cust_id" AS "cust_id"
	,"_twmVC1"."level" + 1 AS "level"
FROM "twm_results"."_twm_referral" AS "_twmVC0"
	, "Referral Seed" AS "_twmVC1"
WHERE ("_twmVC1"."cust_id" = "_twmVC0"."referred_by") AND ("_twmVC1"."level" <= 3)
);