how to form a tree structure from DB2 Table(s)?

Question

Please refer to this question database-structure-for-tree-data-structure

Is this possible in DB2 Database? I know it is possible in Oracle using START WITH and CONNECT BY, i don't have any idea in DB2, Is it possible to achieve this in DB2?

Thanks!

Answer 1

Is is possible with DB2, here is a link for reference.

Basically, you use the WITH syntax. Taken from the link, assume we have this table:

 CREATE TABLE emp(empid  INTEGER NOT NULL PRIMARY KEY,
                    name   VARCHAR(10),
                    salary DECIMAL(9, 2),
                    mgrid  INTEGER);

We can use this query:

WITH n(level, empid, name) AS 
          (SELECT 1, empid, name 
             FROM emp
             WHERE name = 'Goyal'
           UNION ALL
           SELECT n.level + 1, nplus1.empid, nplus1.name 
             FROM emp as nplus1, n
             WHERE n.empid = nplus1.mgrid)
SELECT level, name FROM n;