Drill Down in SQL:

[Former Member]

I have a data model, which represent a set of hierarchical nodes. I would like to kwno, if is possible to get the children of a node with a SQL query.

I try to explain with an example, with DB model. I have two tables: ZNODE  (nodes master data) and ZRELATION (hierarchy).

ZNODE

ID	TEXT
1	N1
2	N2
3	N1.1
4	N1.2
5	N2.1
6	N1.1.1

ZRELATION

PARENT	ID
1	3
1	4
2	5
3	6

If I filter the query by node 1, I would get: N1.1, N1.2, N1.1.1 .

Can I do this with SQL?

Thanks!!

Carlos.

[raymond_giuseppi]

A single SQL statement can easily give immediate children, but AFAIK not for an infinite number of hierarchy level, no syntax for recursivity?

SELECT text FROM znode AS a
  WHERE id = 1
    OR EXISTS ( SELECT * FROM zrelation AS b
                  WHERE id = a~1
                    AND b~parent = 1 )
    OR EXISTS ( SELECT * FROM zrelation AS b
                  WHERE id = a~1
                    AND EXISTS ( SELECT * FROM zrelation AS c
                                   WHERE id = b~1 AND c~parent = 1 ). " etc.

Regards,

Raymond

[Former Member]

Thanks Raymond, but does not help me, because in real scenario I don't know the maximum depth of the tree.

[horst_keller]

Inside ABAP, you can model a solution with meshes and reflexive transitive associations  (see example).

We have associations in CDS too but no transitive ones. Therefore, in SQL/CDS you are retricted to the direct subnodes.

[Former Member]

Thanks Horst. With ABAP, i know how can develop the algorithm.

But we have a HANA DB, and i want to delegate the calculation in SQL (if it is possible. For your answers, and my knowledge, I see that it is not possible).

If I use Hana Studio , Do you know wich is the best option to do it?  Attribute view or Analytical View or Calculation View??

Thanks to all!! Carlos.

[horst_keller]

I'm not an HANA expert, but I'd say calculation views. Attribute views and analytic views are modelled in Hana Studio but implemented in SQL Calculation views can be programmed in SQLScript. If you are able to implement the algorithm in SQLScript you can also put it into an AMDP to make it available in ABAP.

Horst

[matt]

Moderator message: I suggest you close this thread and open a new one in the HANA space.

[Former Member]

Thanks to all!! I will open this thread in HANA space.