dbTalk Databases Forums  

advanced SQL question

Go Back dbTalk Databases Forums Databases comp.databases.oracle.misc advanced SQL question

comp.databases.oracle.misc comp.databases.oracle.misc


Discuss advanced SQL question in the comp.databases.oracle.misc forum.



Reply
 
Thread Tools Display Modes
  #1  
Old   
Andreas Mosmann
 
Posts: n/a

Default advanced SQL question - 09-15-2010 , 08:12 AM





Hi,

there is f.e. the following table:

NAME FROM TO LENGTH
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

in the knode 123456 is a circle OABCO (see the *) and BF outside of this
circle (all circles start with 'O').
knode 654321 has no circle at all.

I need the result
NAME CIRCLE CIRCLELENGTH
123456 OABCO 55

Is there a way to do this via SQL or do I need PLSQL?

Thanks in advance
Andreas


--
wenn email, dann AndreasMosmann <bei> web <punkt> de

Reply With Quote
  #2  
Old   
Michel Cadot
 
Posts: n/a

Default Re: advanced SQL question - 09-15-2010 , 08:43 AM





"Andreas Mosmann" <mosmann (AT) expires-30-09-2010 (DOT) news-group.org> a écrit dans le message de news: 1284556358.9 (AT) user (DOT) newsoffice.de...
Quote:
Hi,

there is f.e. the following table:

NAME FROM TO LENGTH
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

in the knode 123456 is a circle OABCO (see the *) and BF outside of this
circle (all circles start with 'O').
knode 654321 has no circle at all.

I need the result
NAME CIRCLE CIRCLELENGTH
123456 OABCO 55

Is there a way to do this via SQL or do I need PLSQL?

Thanks in advance
Andreas


--
wenn email, dann AndreasMosmann <bei> web <punkt> de
Yes, I show you if you post a test case: create table and
insert statements for your data and Oracle version (with at least
2 decimals).

Regards
Michel

Reply With Quote
  #3  
Old   
Andreas Mosmann
 
Posts: n/a

Default Re: advanced SQL question - 09-15-2010 , 09:09 AM


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

Reply With Quote
  #4  
Old   
Michel Cadot
 
Posts: n/a

Default Re: advanced SQL question - 09-15-2010 , 10:01 AM


"Andreas Mosmann" <mosmann (AT) expires-30-09-2010 (DOT) news-group.org> a écrit dans le message de news: 1284559753.19 (AT) user (DOT) newsoffice.de...
Quote:
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

Reply With Quote
  #5  
Old   
Maxim Demenko
 
Posts: n/a

Default Re: advanced SQL question - 09-15-2010 , 04:32 PM


On 15.09.2010 17:01, Michel Cadot wrote:
Quote:
"Andreas Mosmann"<mosmann (AT) expires-30-09-2010 (DOT) news-group.org> a écrit dans le message de news: 1284559753.19 (AT) user (DOT) 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',clengt h,'x'),'/'),'/',''))
as circlelength

;-)

Best regards

Maxim

Reply With Quote
  #6  
Old   
Michel Cadot
 
Posts: n/a

Default Re: advanced SQL question - 09-17-2010 , 07:55 AM


"Maxim Demenko" <mdemenko (AT) gmail (DOT) com> a écrit dans le message de news: 4C913B74.1030107 (AT) gmail (DOT) com...
Quote:
On 15.09.2010 17:01, Michel Cadot wrote:
"Andreas Mosmann"<mosmann (AT) expires-30-09-2010 (DOT) news-group.org> a écrit dans le message de news:
1284559753.19 (AT) user (DOT) 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',clengt h,'x'),'/'),'/',''))
as circlelength

;-)

Best regards

Maxim
Indeed! I forgot the length.

Regards
Michel

Reply With Quote
  #7  
Old   
Gerard H. Pille
 
Posts: n/a

Default Re: advanced SQL question - 09-17-2010 , 04:07 PM


Michel Cadot schreef:
Quote:
"Maxim Demenko"<mdemenko (AT) gmail (DOT) com> a écrit dans le message de news: 4C913B74.1030107 (AT) gmail (DOT) com...
| + length(replace(sys_connect_by_path(lpad('x',clengt h,'x'),'/'),'/',''))
| as circlelength
|
| ;-)
|
| Best regards
|
| Maxim

Indeed! I forgot the length.

Regards
Michel
The length of the circle, eh? It proves one is never too old to learn.

Reply With Quote
  #8  
Old   
Andreas Mosmann
 
Posts: n/a

Default Re: advanced SQL question - 09-20-2010 , 03:20 AM


Gerard H. Pille schrieb am 17.09.2010 in
<4c93d79e$0$5283$ba620e4c (AT) news (DOT) skynet.be>:

Quote:
Michel Cadot schreef:
"Maxim Demenko"<mdemenko (AT) gmail (DOT) com> a écrit dans le message de news:
4C913B74.1030107 (AT) gmail (DOT) com...
| + length(replace(sys_connect_by_path(lpad('x',clengt h,'x'),'/'),'/',''))
| as circlelength
|
| ;-)
|
| Best regards
|
| Maxim

Indeed! I forgot the length.

Regards
Michel



The length of the circle, eh? It proves one is never too old to learn.
It worked fine, now I have to translate it into the correct structure.
Thank you all, I learned a lot

Andreas

--
wenn email, dann AndreasMosmann <bei> web <punkt> de

Reply With Quote
Reply

« Previous Thread | Next Thread »



Thread Tools
Display Modes
Linear Mode Linear Mode

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
vB code is On
Smilies are On
[IMG] code is On
HTML code is Off



Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.


Contact Us - dbTalk Databases Forums - Archive - Top