topic Re: Drill Down in SQL: in Application Development and Automation Discussions

Drill Down in SQL:

Former Member — Mon, 18 Jan 2016 09:10:05 GMT

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.

Re: Drill Down in SQL:

RaymondGiuseppi — Mon, 18 Jan 2016 09:40:05 GMT

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

Re: Drill Down in SQL:

Former Member — Mon, 18 Jan 2016 09:47:15 GMT

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

Re: Drill Down in SQL:

retired_member — Mon, 18 Jan 2016 11:17:19 GMT

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.

Re: Drill Down in SQL:

Former Member — Mon, 18 Jan 2016 11:48:58 GMT

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.

Re: Drill Down in SQL:

retired_member — Mon, 18 Jan 2016 12:02:42 GMT

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

Re: Drill Down in SQL:

matt — Mon, 18 Jan 2016 12:15:58 GMT

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

Re: Drill Down in SQL:

Former Member — Mon, 18 Jan 2016 12:33:28 GMT

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