CTE recursive in SAP HANA SP12

[stvndw]

Hello,

I am confronted with a challenge to explore a parent-child relation.

I am trying to use recursive CTE for this.

Example table:

Parent-Child A C A B A B E C J C G I

The SQL statement would be

WITH rel as (
    SELECT * FROM mytable WHERE "Child" = 'A' 
   UNION ALL 
    SELECT p.* 
      FROM mytable p 
      JOIN rel pa 
        ON pa."Child" = p."Parent") 

select * from rel;

When I execute this statement without the UNION ALL part. It works fine.

But when I try to execute the whole statement. It gives following error:

SAP DBTech JDBC: [259]: invalid table name: Could not find table/view REL in schema myschema: line 7 col 6 (at pos 149)

Anybody any idea what I am doing wrong?

I read CTE is added to HANA from SP7 and we are working on SP12

Regards,

Steven Dauw

[lbreddemann · Accepted solution]

While CTEs are available in HANA - as you’ve mentioned - from HANA 1 SP7, recursive CTEs are not.

HANA 2 supports hierarchy processing in SQL.