Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2006
    Posts
    2

    Question Unanswered: Regarding the CONNECT BY Clause in DB2

    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

  2. #2
    Join Date
    Jul 2006
    Location
    Pune , India
    Posts
    433
    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

  3. #3
    Join Date
    Dec 2006
    Posts
    2
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •