TERADATA: SQL recursive query that selects from a recursive query

Question

I am trying to perform recursion multiple times using Teradata my query is similar to the following, upon referencing the query_1 from query_2 and trying to run it gives an error saying that query_1 doesn't exist.

WITH 
RECURSIVE query_1
AS (
 --some recursive query
)
   ,RECURSIVE query_2
   AS (
   --another recursive query based on results from query_1
   )
SELECT *
FROM query_2

I want to concatenate values from different levels of hierarchy so I want to turn

------------------------------------
 trx    indx1   indx2   val
------------------------------------
 x1      1       1       a
 x1      1       2       b
 x1      1       3       c
 x1      2       1       d
...................................
-----------------------------------

Into the following

----------------
 trx        val
----------------
 x1       a/b/c/d

You can't have nested recursive queries (and I can't imagine why you should need it). — dnoeth, Apr 19 '18 at 08:38
Not sure about Terada, but with standard SQL, the `recursive` keyword must only be specified **once** right after the `WITH` keyword, regardless which of the CTEs is recursive. Did you try to remove the second `RECURSIVE` keyword? — , Apr 19 '18 at 08:56
You can use XMLAGG to concatenate: https://stackoverflow.com/a/29549409/2527905 or you concatenated while you do the hierarchy query. — dnoeth, Apr 19 '18 at 09:19
XMLAGG has done it for me! however the ORDER BY supports only one column so I had to concatenate every level in a subquery — Galal Ouda, Apr 19 '18 at 09:56

Galal Ouda · Answer 1 · 2018-04-22T08:01:21.857

0

XMLAGG function solved the problem

    SELECT TRX
        , TRIM(TRAILING '/' FROM (XMLAGG(TRIM(val)|| '/' ORDER BY indx1||indx2 ASC) (VARCHAR(10000)))) val
    FROM table_name
    GROUP BY 1,2

however, I wish the "order by" inside XMLAGG supports multiple expressions this way we won't need subqueries to concatenate multiple levels of hierarchies.

thanks donoeth for the solution

edited Apr 22 '18 at 08:01

answered Apr 19 '18 at 11:03

Galal Ouda

81
1
9

There's no need for two aggregations, simply combine both columns into one, e.g. `SELECT TRX , Trim(Trailing '/' FROM (XmlAgg(Trim(val)|| '/' ORDER BY indx1||indx2 ASC) (VARCHAR(10000)))) val FROM TableName GROUP BY 1` – dnoeth Apr 19 '18 at 12:36
@dnoeth XMLAGG fails when it finds non english characters, is there any work around? – Galal Ouda Oct 17 '18 at 07:10
1

It's probably not failing due to XMLAGG but due to a wrong session character set, should be UTF-8 or UTF16. – dnoeth Oct 17 '18 at 15:25

TERADATA: SQL recursive query that selects from a recursive query

1 Answers1