Re: difficult select- question
From: Shakespeare <whatsin_at_xs4all.nl>
Date: Mon, 26 Oct 2009 20:32:44 +0100
Message-ID: <4ae5f95c$0$83241$e4fe514c_at_news.xs4all.nl>
Michel Cadot schreef:
> "Andreas Mosmann" <mosmann_at_expires-31-10-2009.news-group.org> a écrit dans le message de news: 1256558932.12_at_user.newsoffice.de...
> | Hi ng,
> |
> | I hope, there is someone with a good idea concerning the following
> | question:
> |
> | There is a sorted Table (or view) like the following:
> | F1|F2|F3|F4
> | AA|BB|10|20
> | AA|BB|20|30
> | AA|BB|40|50
> | AA|BB|50|60
> | AA|BB|60|70
> | AA|CC|70|80
> | AA|CC|80|90
> |
> | As you can see there are some rows, that are connected by F3=prior F4,
> | but there are leaks too. I need a result as the following:
> |
> | AA|BB|10|30|2 parts
> | AA|BB|40|70|3 parts
> | AA|CC|70|90|2 parts
> |
> | I know it is easy to solve by programming.
> | I think it is possible to do this with inline view and hierarchical
> | query.
> | I can imagine a way by select min(F3), max(F4) ... where exists (enough
> | connecting data in the middle).
> |
> | But what is the best (SQL-)way to solve this? (There actually are about
> | 15000 lines, the estimated result probably should not have more than 100
> | lines)
> | I could do it by PL/SQL, but I am interested in SQL, if exists
> |
> | Andreas Mosmann
> |
> |
> |
> |
> |
> | --
> | wenn email, dann AndreasMosmann <bei> web <punkt> de
>
>
> SQL> select connect_by_root f1 f1, f2,
> 2 connect_by_root f3 f3, f4,
> 3 (f4-connect_by_root f3)/10 cnt1,
> 4 length(sys_connect_by_path(f2,'/'))
> 5 - length(replace(sys_connect_by_path(f2,'/'),'/','')) cnt2
> 6 from t
> 7 where connect_by_isleaf = 1
> 8 connect by prior f1 = f1 and prior f2 = f2 and prior f4 = f3
> 9 start with (f1,f2,f3) not in (select f1,f2,f4 from t)
> 10 /
> F1 F2 F3 F4 CNT1 CNT2
> -- -- ---------- ---------- ---------- ----------
> AA BB 10 30 2 2
> AA BB 40 70 3 3
> AA CC 70 90 2 2
>
> 3 rows selected.
>
> Regards
> Michel
>
>
You see?
Date: Mon, 26 Oct 2009 20:32:44 +0100
Message-ID: <4ae5f95c$0$83241$e4fe514c_at_news.xs4all.nl>
Michel Cadot schreef:
> "Andreas Mosmann" <mosmann_at_expires-31-10-2009.news-group.org> a écrit dans le message de news: 1256558932.12_at_user.newsoffice.de...
> | Hi ng,
> |
> | I hope, there is someone with a good idea concerning the following
> | question:
> |
> | There is a sorted Table (or view) like the following:
> | F1|F2|F3|F4
> | AA|BB|10|20
> | AA|BB|20|30
> | AA|BB|40|50
> | AA|BB|50|60
> | AA|BB|60|70
> | AA|CC|70|80
> | AA|CC|80|90
> |
> | As you can see there are some rows, that are connected by F3=prior F4,
> | but there are leaks too. I need a result as the following:
> |
> | AA|BB|10|30|2 parts
> | AA|BB|40|70|3 parts
> | AA|CC|70|90|2 parts
> |
> | I know it is easy to solve by programming.
> | I think it is possible to do this with inline view and hierarchical
> | query.
> | I can imagine a way by select min(F3), max(F4) ... where exists (enough
> | connecting data in the middle).
> |
> | But what is the best (SQL-)way to solve this? (There actually are about
> | 15000 lines, the estimated result probably should not have more than 100
> | lines)
> | I could do it by PL/SQL, but I am interested in SQL, if exists
> |
> | Andreas Mosmann
> |
> |
> |
> |
> |
> | --
> | wenn email, dann AndreasMosmann <bei> web <punkt> de
>
>
> SQL> select connect_by_root f1 f1, f2,
> 2 connect_by_root f3 f3, f4,
> 3 (f4-connect_by_root f3)/10 cnt1,
> 4 length(sys_connect_by_path(f2,'/'))
> 5 - length(replace(sys_connect_by_path(f2,'/'),'/','')) cnt2
> 6 from t
> 7 where connect_by_isleaf = 1
> 8 connect by prior f1 = f1 and prior f2 = f2 and prior f4 = f3
> 9 start with (f1,f2,f3) not in (select f1,f2,f4 from t)
> 10 /
> F1 F2 F3 F4 CNT1 CNT2
> -- -- ---------- ---------- ---------- ----------
> AA BB 10 30 2 2
> AA BB 40 70 3 3
> AA CC 70 90 2 2
>
> 3 rows selected.
>
> Regards
> Michel
>
>
You see?
Shakespeare Received on Mon Oct 26 2009 - 14:32:44 CDT