Example - Mixing WITH and WITH RECURSIVE Queries - Teradata Warehouse Miner

In this example, a With (Recursive) Group SQL element is used to combine two WITH queries and a WITH RECURSIVE query. Note that if there were multiple recursive queries it would be necessary to not define variables in the base query, which would presumably defeat the purpose of defining WITH queries.

The SQL generated by the base query in this case 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)
)
,
"Derived Table 1" ("AvgX")  AS
(SELECT
	 AVG(CAST("_twmSQ0"."income" AS FLOAT)) AS "AvgX"
FROM "twm_source"."twm_customer" AS "_twmSQ0"
)
,
"Derived Table 2" ("AvgXSquared")  AS
(SELECT
	 AVG(CAST("_twmSQ0"."income" AS FLOAT) * CAST("_twmSQ0"."income" AS FLOAT)) AS "AvgXSquared"
FROM "twm_source"."twm_customer" AS "_twmSQ0"
)
SELECT
	 "_twmVC0"."cust_id" AS "cust_id"
	,"_twmVC1"."income" AS "income"
	,("_twmVC1"."income" - "_twmVC2"."AvgX") / SQRT("_twmVC3"."AvgXSquared" - ("_twmVC2"."AvgX" ** 2)) AS "incomeZScore"
FROM "Referral Seed" AS "_twmVC0"
	LEFT OUTER JOIN "twm_source"."twm_customer" AS "_twmVC1"
		ON "_twmVC0"."cust_id" = "_twmVC1"."cust_id"
	, "Derived Table 1" AS "_twmVC2"
	, "Derived Table 2" AS "_twmVC3"
ORDER BY "_twmVC0"."cust_id"