Hi All,
Does anyone know what is the equivalent query for

1 SELECT name, salary
2 FROM emp
3 START WITH name = 'Goyal'
4 CONNECT BY PRIOR empid = mgrid

in DB2

and given the table as

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

5 INSERT INTO emp
6 VALUES ( 1, 'Jones', 30000, 10),
7 ( 2, 'Hall', 35000, 10),
8 ( 3, 'Kim', 40000, 10),
9 ( 4, 'Lindsay', 38000, 10),
10 ( 5, 'McKeough', 42000, 11),
11 ( 6, 'Barnes', 41000, 11),
12 ( 7, 'O''Neil', 36000, 12),
13 ( 8, 'Smith', 34000, 12),
14 ( 9, 'Shoeman', 33000, 12),
15 (10, 'Monroe', 50000, 15),
16 (11, 'Zander', 52000, 16),
17 (12, 'Henry', 51000, 16),
18 (13, 'Aaron', 54000, 15),
19 (14, 'Scott', 53000, 16),
20 (15, 'Mills', 70000, 17),
21 (16, 'Goyal', 80000, 17),
22 (17, 'Urbassek', 95000, NULL);


Since this Connect by clause cannot be used in DB2 for OS390 please let me know how to go for the searching of Hierarchical types in Db2

Thanks and Regards,

Nirmala S

you can use CTE expressions(with ... union all) to acheive it
there is a good tech article by Serge on "port connect by to db2"

http://www-128.ibm.com/developerwork...dm-0510rielau/

--Rahul Singh

Thanks for your Quick Response and the website was useful

But CTE can be used only in Db2 for windows application
and i have to code it in Db2 for OS390
So i have a Problem in using "with" clause in DB2 for OS390


Thanks and Regards,

Nirmala S