Re: advanced SQL question

From: Maxim Demenko <mdemenko_at_gmail.com>
Date: Wed, 15 Sep 2010 23:32:36 +0200
Message-ID: <4C913B74.1030107_at_gmail.com>

On 15.09.2010 17:01, Michel Cadot wrote:
> "Andreas Mosmann"<mosmann_at_expires-30-09-2010.news-group.org> a écrit dans le message de news: 1284559753.19_at_user.newsoffice.de...
> | Hi Michel,
> |
> | thank you :)
> |
> | Oracle Version 11.2.0.1.0
> |
> | create table TKNODES (CNAME VARCHAR(10), CFROM CHAR(1), CTO CHAR(1),
> | CLENGTH NUMBER(10,3))
> | /
> | insert into TKNODES (CNAME,CFROM,CTO,CLENGTH) values
> | ('123456','O','A',10);
> | insert into TKNODES (CNAME,CFROM,CTO,CLENGTH) values
> | ('123456','A','B',15);
> | insert into TKNODES (CNAME,CFROM,CTO,CLENGTH) values
> | ('123456','B','C',10);
> | insert into TKNODES (CNAME,CFROM,CTO,CLENGTH) values
> | ('123456','C','O',20);
> | insert into TKNODES (CNAME,CFROM,CTO,CLENGTH) values
> | ('123456','B','F',50);
> | insert into TKNODES (CNAME,CFROM,CTO,CLENGTH) values
> | ('654321','O','A',20);
> | insert into TKNODES (CNAME,CFROM,CTO,CLENGTH) values
> | ('654321','A','B',30);
> |
> |
> | --
> | wenn email, dann AndreasMosmann<bei> web<punkt> de
>
> SQL> select * from TKNODES order by cname;
> CNAME C C CLENGTH
> ---------- - - ----------
> 123456 O A 10
> 123456 A B 15
> 123456 B C 10
> 123456 C O 20
> 123456 B F 50
> 654321 O A 20
> 654321 A B 30
>
> SQL> select replace(sys_connect_by_path (cfrom, '/'), '/', '')||cto res
> 2 from tknodes
> 3 where connect_by_iscycle = 1 and cto = 'O'
> 4 connect by nocycle prior cname = cname and prior cto = cfrom
> 5 start with cfrom = 'O'
> 6 /
> RES
> ----------
> OABCO
>
> Regards
> Michel
>
>

+ length(replace(sys_connect_by_path(lpad('x',clength,'x'),'/'),'/','')) as circlelength

;-)

Best regards

Maxim Received on Wed Sep 15 2010 - 16:32:36 CDT