Results 1 to 3 of 3
  1. #1
    Join Date
    May 2016
    Posts
    9

    Answered: connect by prior

    Hi
    I want to know how to convert this oracle query to Db2
    Oracle query:
    SELECT no,lvl
    FROM A
    WHERE cat <> 'S'
    CONNECT BY PRIOR pid = id
    CREATE TABLE A(
    no decimal(19,4) NOT NULL,
    id varchar(10) NOT NULL,
    pid varchar(10) NOT NULL,
    lvl varchar(10) NULL,
    cat varchar(1) not NULL
    );

    insert into A (no,id,pid,lvl,cat) values(1,'A','P','1','C');
    insert into A (no,id,pid,lvl,cat) values(2,'B','A','1.1','F');
    insert into A (no,id,pid,lvl,cat) values(3,'C','B','1.1.1','S');
    insert into A (no,id,pid,lvl,cat) values(4,'D','A','1.2','F');
    insert into A (no,id,pid,lvl,cat) values(5,'E','D','1.2.1','S');

    Thanks in advance

  2. Best Answer
    Posted by mark.b

    "Hi,

    try this:
    Code:
    with t(no, lvl, id) as (
    select no, lvl, id
    from a a
    where cat<>'S'
    and not exists (select 1 from a b where b.id=a.pid)
      union all
    select a.no, a.lvl, a.id
    from a, t
    where a.cat<>'S' and a.pid=t.id
    )
    select no, lvl
    from t;
    "


  3. #2
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    Hi,

    try this:
    Code:
    with t(no, lvl, id) as (
    select no, lvl, id
    from a a
    where cat<>'S'
    and not exists (select 1 from a b where b.id=a.pid)
      union all
    select a.no, a.lvl, a.id
    from a, t
    where a.cat<>'S' and a.pid=t.id
    )
    select no, lvl
    from t;
    Regards,
    Mark.

  4. #3
    Join Date
    May 2016
    Posts
    9
    Thanks Mark. Worked

Tags for this Thread

Posting Permissions

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