Example - Teradata Warehouse Miner

Teradata Warehouse Miner User Guide - Volume 2ADS Generation

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

The following is a fictitious example of a With Recursive Query to select all of the customers referred to a business directly or indirectly by customer 1362480.

In support of this and subsequent examples in this section, the following test table is needed.

DROP TABLE twm_results._twm_referral;
CREATE TABLE twm_results._twm_referral
(cust_id INTEGER
, referred_by INTEGER);
INSERT INTO twm_results._twm_referral VALUES (1362480, NULL);
INSERT INTO twm_results._twm_referral VALUES (1362481, 1362480);
INSERT INTO twm_results._twm_referral VALUES (1362484, 1362480);
INSERT INTO twm_results._twm_referral VALUES (1362485, 1362480);
INSERT INTO twm_results._twm_referral VALUES (1362486, 1362481);
INSERT INTO twm_results._twm_referral VALUES (1362487, 1362481);
INSERT INTO twm_results._twm_referral VALUES (1362488, 1362487);
INSERT INTO twm_results._twm_referral VALUES (1362489, NULL);
INSERT INTO twm_results._twm_referral VALUES (1362492, 1362489);
INSERT INTO twm_results._twm_referral VALUES (1362498, 1362489);
INSERT INTO twm_results._twm_referral VALUES (1362499, 1362489);
INSERT INTO twm_results._twm_referral VALUES (1362500, 1362499);
INSERT INTO twm_results._twm_referral VALUES (1362501, 1362499);

The table of interest in this example contains two columns, cust_id and referred_by, which define a hierarchical relationship within the table, represented by the following example.

1362480
	<- 1362481
		<- 1362486
		<- 1362487
			<- 1362488
	<- 1362484
	<- 1362485
1362489
	<- 1362492
	<- 1362498
	<- 1362499
		<- 1362500
		<- 1362501

The With (Recursive) Clause in the base query analysis looks like the following, produced by selecting cust_id from Referral Seed and from Referral Recursion.

The seed query Referral Seed selects cust_id from the table of interest along with a constant 0 for level using the select list below, while the Where clause below “starts” the query by comparing the referred_by column in the table of interest to 1362480:

The recursive query Referral Recursion selects cust_id from the table of interest and level + 1 from Referral Seed with the following select list, along with a Where clause matching cust_id from Referral Seed to referred_by in the table of interest, while also limiting the recursion level from Referral Seed to 3.

The SQL generated by the base query follows.

WITH RECURSIVE "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)
)
SELECT
	 "_twmVC0"."cust_id" AS "cust_id"
FROM "Referral Seed" AS "_twmVC0"
ORDER BY "_twmVC0"."cust_id";

The result set consists of all of the customers referred directly or indirectly by 1362480.

cust_id
1362481
1362484
1362485
1362486
1362487
1362488