Skip to Content
-1

CTE recursive in SAP HANA SP12

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

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Best Answer
    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.

    Add comment
    10|10000 characters needed characters exceeded