Topic
  • 1 reply
  • Latest Post - ‏2013-09-03T15:03:20Z by NaseemH
SystemAdmin
SystemAdmin
5837 Posts

Pinned topic Can not use CONNECT_BY_ISCYCLE pseudo column in the WHERE & PROJECTION

‏2012-05-21T22:53:49Z |
Hi,

I'm using DB2 Express 9.7.5 to try it's hierarcial query from this example: ibm.com/developerworks/ibmi/library/i-db2connectby/index.html
But the example do not work if I use CONNECT_BY_ISCYCLE pseudo column in the WHERE & PROJECTION. This is my code for testing this feature:
drop table flights;

create table flights (
departure varchar(32),
arrival varchar(32),
price int
);

insert into flights (departure, arrival, price)
values
('Bremen', 'Hamburg', 35),
('Bremen', 'Hannover', 40),
('Bremen', 'Koln', 45),
('Bremen', 'Berlin', 85),

('Frankfurt', 'Berlin', 80),
('Frankfurt', 'Dresden', 95),
('Frankfurt', 'Hannover', 55),
('Frankfurt', 'Koln', 40),
('Frankfurt', 'Karlsruhe', 50),
('Frankfurt', 'Munchen', 65),
('Frankfurt', 'Stuttgart', 40),
('Frankfurt', 'Bremen', 75),

('Berlin', 'Bremen', 85),
('Berlin', 'Dresden', 55),
('Berlin', 'Frankfurt', 80),
('Berlin', 'Hamburg', 80),
('Berlin', 'Hannover', 90),
('Berlin', 'Munchen', 90),
('Berlin', 'Stuttgart', 85),

('Hamburg', 'Dresden', 80),
('Hamburg', 'Dresden', 35),
('Hamburg', 'Dresden', 50),
('Hannover', 'Dresden', 90),
('Hannover', 'Dresden', 40)
;

SELECT
CONNECT_BY_ROOT departure AS departure,
arrival,
LEVEL,
Connect_By_Root Departure concat
Sys_Connect_By_Path(Arrival, ' - ') "Connection"
FROM flights
WHERE
arrival = 'Berlin' AND
CONNECT_BY_ISCYCLE = 0 AND
LEVEL <= 3
START WITH departure = 'Frankfurt'
CONNECT BY NOCYCLE
PRIOR arrival = departure AND
arrival <> 'Frankfurt'
ORDER BY LEVEL;

The query above do not work because I use "CONNECT_BY_ISCYCLE = 0" in the WHERE condition (wrong context usage error). But if I omit it, then the query do work, but I need that filter to remove the CYCLED path.

Is this bug have been fixed in 10.1 version?
Was someone have experienced with this problem?
  • NaseemH
    NaseemH
    1 Post

    Re: Can not use CONNECT_BY_ISCYCLE pseudo column in the WHERE &#38; PROJECTION

    ‏2013-09-03T15:03:20Z  

    I'm facing the same problem, is this a patch issue with DB2 9.7 version? Any Leads would be helpful.

     

    Thanks