Skip to Content
-1
Nov 23, 2017 at 06:57 PM

CTE recursive in SAP HANA SP12

3488 Views Last edit Nov 24, 2017 at 12:17 AM 2 rev

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