Example - Multiple Seed and Recursive Queries - 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

In this example, two seed query analyses and two recursive query analyses are utilized to perform a recursive query, finding all of the customers referred directly or indirectly to a business by customers 1362480 and 1362489.

Example - Multiple Seed and Recursive Queries

The generated SQL is as follows.

WITH RECURSIVE "Referral Seed 1" ("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
	 "_twmSQ0"."cust_id" AS "cust_id"
	,0 AS "level"
FROM "twm_results"."_twm_referral" AS "_twmSQ0"
WHERE "_twmSQ0"."referred_by" = 1362489
UNION ALL
SELECT
	 "_twmVC0"."cust_id" AS "cust_id"
	,"_twmVC1"."level" + 1 AS "level"
FROM "twm_results"."_twm_referral" AS "_twmVC0"
	, "Referral Seed 1" AS "_twmVC1"
WHERE ("_twmVC1"."cust_id" = "_twmVC0"."referred_by") AND ("_twmVC1"."level" <= 3)
UNION ALL
SELECT
	 "_twmVC0"."cust_id" AS "cust_id"
	,"_twmVC1"."level" + 1 AS "level"
FROM "twm_results"."_twm_referral" AS "_twmVC0"
	, "Referral Seed 1" AS "_twmVC1"
WHERE ("_twmVC1"."cust_id" = "_twmVC0"."referred_by") AND ("_twmVC1"."level" <= 3)
)
SELECT DISTINCT
	 "_twmVC0"."cust_id" AS "cust_id"
FROM "Referral Seed 1" AS "_twmVC0"
ORDER BY 1
The result set is:
  • 1362481
  • 1362484
  • 1362485
  • 1362486
  • 1362487
  • 1362488
  • 1362492
  • 1362498
  • 1362499
  • 1362500
  • 1362501