Example - With (Recursive) Group - Teradata Warehouse Miner

The previous example may be re-worked using a With (Recursive) Group, while yielding the same results.

The SQL generated by the base query in this case is:

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
	 "_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)
)
,
RECURSIVE "Referral Seed 2" ("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" = 1362489
UNION ALL
SELECT
	 "_twmVC0"."cust_id" AS "cust_id"
	,"_twmVC1"."level" + 1 AS "level"
FROM "twm_results"."_twm_referral" AS "_twmVC0"
	, "Referral Seed 2" AS "_twmVC1"
WHERE ("_twmVC1"."cust_id" = "_twmVC0"."referred_by") AND ("_twmVC1"."level" <= 3)
)
SELECT DISTINCT  * FROM "Referral Seed 1"
UNION ALL
SELECT DISTINCT  * FROM "Referral Seed 2"
;

In this example, the result set is exactly the same as in the previous example.