Skip to Content

CTE recursive in SAP HANA SP12

Nov 23, 2017 at 06:57 PM


avatar image


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' 
    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


Steven Dauw

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

1 Answer

Best Answer
Lars Breddemann
Nov 23, 2017 at 10:10 PM

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.

10 |10000 characters needed characters left characters exceeded