I want to explain, how hierarchical data can be
retrieved using a single query.
With MySQL, I would have to issue many select
statements successively from the
application side. So I tried out sapdb and searched
for recursive selects.
This concept isn�t very well described in the manual.
There is only the EBNF
notation and not a single useful example.
To whom it may be intersting, I want to present my
first tests.
First I set up a table to store the hierarchical data.
create table test.locations ( id integer, name
varchar(40), parent integer )
insert into test.locations (id, name, parent) values (
0, 'universe', -1 )
insert into test.locations (id, name, parent) values (
1, 'our galaxy', 0 )
insert into test.locations (id, name, parent) values (
2, 'other galaxy', 0 )
insert into test.locations (id, name, parent) values (
3, 'our solar system', 1 )
insert into test.locations (id, name, parent) values (
4, 'other solar system', 1 )
insert into test.locations (id, name, parent) values (
5, 'mars', 3 )
insert into test.locations (id, name, parent) values (
6, 'venus', 3 )
insert into test.locations (id, name, parent) values (
7, 'earth', 3 )
insert into test.locations (id, name, parent) values (
8, 'europe', 7 )
insert into test.locations (id, name, parent) values (
9, 'germany', 8 )
insert into test.locations (id, name, parent) values (
10, 'france', 8 )
insert into test.locations (id, name, parent) values (
11, 'england', 8 )
insert into test.locations (id, name, parent) values (
12, 'berlin', 9 )
insert into test.locations (id, name, parent) values (
13, 'munich', 9 )
insert into test.locations (id, name, parent) values (
14, 'paris', 10 )
insert into test.locations (id, name, parent) values (
15, 'lyon', 10 )
insert into test.locations (id, name, parent) values (
16, 'london', 11 )
insert into test.locations (id, name, parent) values (
17, 'dover', 11 )
Now I wanted to get all child-elements of a given
root.
I used the following cursor.
DECLARE C CURSOR FOR
WITH RECURSIVE TREE (NODE_NAME, NODE_ID, NODE_PARENT,
NODE_LEVEL) AS
(
SELECT name, id, parent, 0
FROM test.locations WHERE name = 'universe'
UNION ALL
SELECT name, id, parent, NODE_LEVEL+1
FROM test.locations, TREE
WHERE TREE.NODE_ID = test.locations.parent
)
SELECT NODE_NAME, NODE_ID, NODE_PARENT, NODE_LEVEL
FROM TREE
ORDER BY NODE_ID
NODE_LEVEL shows the level of recursion. This ist the
number of levels between
the given root element and the current child-element.
The first select-Statement in the cursor defines the
start of the recursion.
The second refers to the first in the case of
recursion.
The example above produces the following output:
universe 0 -1 0
our galaxy 1 0 1
other galaxy 2 0 1
our solar system 3 1 2
other solar system 4 1 2
mars 5 3 3
venus 6 3 3
earth 7 3 3
europe 8 7 4
germany 9 8 5
france 10 8 5
england 11 8 5
berlin 12 9 6
munich 13 9 6
paris 14 10 6
lyon 15 10 6
london 16 11 6
dover 17 11 6
Select another root element is analogue to the first
example:
DECLARE C CURSOR FOR
WITH RECURSIVE TREE (NODE_NAME, NODE_ID, NODE_PARENT,
NODE_LEVEL) AS
(
SELECT name, id, parent, 0
FROM test.locations WHERE name = 'europe'
UNION ALL
SELECT name, id, parent, NODE_LEVEL+1
FROM test.locations, TREE
WHERE TREE.NODE_ID = test.locations.parent
)
SELECT NODE_NAME, NODE_ID, NODE_PARENT, NODE_LEVEL
FROM TREE
ORDER BY NODE_ID
europe 8 7 0
germany 9 8 1
france 10 8 1
england 11 8 1
berlin 12 9 2
munich 13 9 2
paris 14 10 2
lyon 15 10 2
london 16 11 2
dover 17 11 2
If you want to get all parent elements from a given
child-element, instead
of retrieving all childs from a given parent, you just
have to change the
second select statement.
DECLARE C CURSOR FOR
WITH RECURSIVE TREE (NODE_NAME, NODE_ID, NODE_PARENT,
NODE_LEVEL) AS
(
SELECT name, id, parent, 0
FROM test.locations WHERE name = 'berlin'
UNION ALL
SELECT name, id, parent, NODE_LEVEL+1
FROM test.locations, TREE
WHERE TREE.NODE_PARENT = test.locations.id
)
SELECT NODE_NAME, NODE_ID, NODE_PARENT, NODE_LEVEL
FROM TREE
ORDER BY NODE_ID
This produces the following output:
universe 0 -1 6
our galaxy 1 0 5
our solar system 3 1 4
earth 7 3 3
europe 8 7 2
germany 9 8 1
berlin 12 9 0
I hope, this may help someone as a starting point for
using this concept.
I�m happy that sapdb supports this feature. Otherwise
it would bring up
a massive overhead or investment using mysql or
oracle.
Greetings
Daniel Sch�fer
=====
===================================
Daniel D. Sch�fer
35457 Lollar
+49-(0)-175-7628960
http://www.ddschaefer.de
[EMAIL PROTECTED]
__________________________________________________________________
Gesendet von Yahoo! Mail - http://mail.yahoo.de
Bis zu 100 MB Speicher bei http://premiummail.yahoo.de
_______________________________________________
sapdb.general mailing list
[EMAIL PROTECTED]
http://listserv.sap.com/mailman/listinfo/sapdb.general
