Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2008
    Posts
    2

    Unanswered: Hirarchial Query (Recursive)

    My table looks as follows:

    col1,col2
    1,a
    2,b
    3,c
    4,d
    5,e
    6,f

    I am trying to write a db2 qyery which would return me following output
    a
    a-b
    a-b-c
    a-b-c-d
    a-b-c-d-e
    a-b-c-d-e-f

    I have written a oracle query for the same, which is:
    sys_connect_by_path(col2,'-') start with col1=1 connect by col1=prior col1+1

    I tried to do it in DB2 but I am not able to get the logic. Can anyone help?

    Thanks

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5

  3. #3
    Join Date
    Jul 2008
    Posts
    2
    I have written a sql but there is a slight prob with it.

    What i have is:

    WITH parent (col1,path) AS
    (SELECT DISTINCT
    col1
    ,cast(col2 as char)
    FROM my_table
    WHERE col1 = 1
    UNION ALL
    SELECT C.col1
    ,cast(P.col2 as char) || '-' || cast(C.col2 as char)
    FROM my_table C
    ,parent P
    WHERE P.col1 = C.col1-1
    )
    SELECT *
    FROM parent;

    result I am getting is:
    1,a
    2,a-b
    3,b-c
    4,c-d
    5,d-e
    6,e-f

    But what I am expecting is:
    1,a
    2,a-b
    3,a-b-c
    4,a-b-c-d
    5,a-b-c-d-e
    6,a-b-c-d-e-f

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    What DB2 version and OS are you using?

    I tried your SQL and got errors. First p.col2 was undefined and then value a-b is too long.

    I changed it to this and it works as intended:

    WITH parent (col1,path) AS
    (SELECT DISTINCT
    col1
    ,col2
    FROM mytable
    WHERE col1 = 1
    UNION ALL
    SELECT C.col1
    ,P.path || '-' ||C.col2
    FROM mytable C
    ,parent P
    WHERE P.col1 = C.col1-1
    )
    SELECT *
    FROM parent;


    Andy

Posting Permissions

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