Thread: feature request START WITH ... CONNECT BY
Recently I found an Oracle extension, START WITH .. CONNECT BY, which greatly eases parsing of n-way tree structures stored in table format. I'd prefer implementing this myself, but alas, I feel I need something which is a bit easier, to get accustomed to the code base. the following is copyrighted by Oracle Corporation. So, sue me for posting it. :) START WITH condition Specify a condition that identifies the row(s) to be used as the root(s) of a hierarchical query. Oracle uses as root(s) all rows that satisfy this condition. If you omit this clause, Oracle uses all rows in the table as root rows. The START WITH condition can contain a subquery. CONNECT BY condition Specify a condition that identifies the relationship between parent rows and child rows of the hierarchy. condition can be any condition as described in "Conditions". However, some part of the condition must use the PRIOR operator to refer to the parent row. The part of the condition containing the PRIOR operator must have one of the following forms: PRIOR expr comparison_operator expr expr comparison_operator PRIOR expr Restriction: The CONNECT BY condition cannot contain a subquery. Ola -- Ola Sundell ola@miranda.org - olas@wiw.org - ola.sundell@personalchemistry.com http://miranda.org/~ola
> Recently I found an Oracle extension, START WITH .. CONNECT BY, which > greatly eases parsing of n-way tree structures stored in table format. > > I'd prefer implementing this myself, but alas, I feel I need something > which is a bit easier, to get accustomed to the code base. > Hi, I am currently porting business application from Oracle to PG, for one of our customers. I work hardly on migrating CONNECT BY queries, using the really good libraries from OpenACS project (version 4). It a kind of 1 day/man to enable tree structure in PG for one table accessed with a CONNECT BY query. So I'll be really glad to you if you enable CONNECT BY statments in PG!! So, what you want to know about connect by statment? read: http://www.arsdigita.com/books/sql/trees.html and: http://openacs.org/bboard/q-and-a-fetch-msg.tcl?msg_id=0000j6&topic_id=12&topic=OpenACS%204%2e0%20Design download OpenACS source code for version 4.x and : We're using this extensively in openacs4. If you download the latest code, you can find the tree encoding table and some tree-utility routines in openacs-4/packages/acs-kernel/sql/postgresql/postgresql.sql.For corresponding trigger routines look at openacs-4/packages/acs-kernel/sql/postgresql/acs-objects-create.sql - specifically look at the acs_objects table definition and its associated triggers. With regards to inheritance, we looked seriously at prior to starting the opeancs4 porting activities and we opted not to use it because it was deficient in serveral areas. -- Dan Wickstrom, September 7, 2001 Hope this helps -- Jean-Paul ARGUDO IDEALX S.A.S Consultant bases de données 15-17, av. de Ségur http://IDEALX.com/ F-75007 PARIS
I jump again on this mail to ask for SQL help. Since I've not found a list on
pgsql-users or something like that, I apologize to post it to hackers..
So.. I'm porting from Oracle to PG. I have many CONNECT BY queries, luckyly,
only 2 tables are hierarchical. I've adopted OpenACS solution, since I am sure
it is the best way to do with that problem.
But, I found a problem wich I have no brain left today to resolve.
I port following Oracle CONNECT BY statment:
--ORACLE QUERY
--
--select
-- sum(t01_caf) SCAF,
-- sum(t01_itm_cnt) SART
--from T01_&DateData
--start with T01_upr_lvl_typ = &TypNiv and T01_upr_lvl_nbr = &Niv
--connect by prior T01_lvl_typ = T01_upr_lvl_typ and prior T01_lvl_nbr =
T01_upr_lvl_nbr
--
-- The execution in the Oracle DB returns:
--
--
-- SCAF SART
------------ ----------
--40164802,4 1404296
--
-- with variables &TypNiv = 0 et &Niv = 0
--
-- PG port:
--
\set TypNiv 0
\set Niv 0
--
select sum(t01_caf) as SCAF, sum(t01_itm_cnt) as SCAF
from t01_20011231
where strpos(t01_tree_sortkey,(select t01_tree_sortkey from t01_20011231
where t01_lvl_typ = :TypNiv and t01_lvl_nbr = :Niv))=1
group by ???;
The problem is that I am no longuer able to find the RIGHT group by statment :-/
Can someone help me ? I'm sure it is surely kind simplistic? dunno..
Ah! The purpose of the query is to sum values on all nodes children of one node.
Inthis crappy customer database, a node is identifyied uniquely with couple
(t01_lvl_typ,t01_lvl_nbr), ((couple t01_upr_lvl_typ,t01_upr_lvl_nbr identifies
uniquely the Father of the node)) because there can be nodes at different level
(lvl_typ) with the same identifyier (lvl_nbr). I dont want to user concat || to
create a pseudo-unique-identifyer, because I think there may be perfs problems
...
Thanks. Best regards & wishes.
--
Jean-Paul ARGUDO IDEALX S.A.S
Consultant bases de données 15-17, av. de Ségur
http://IDEALX.com/ F-75007 PARIS
At last, here's the solution: NO NEED to group by :-/ ???
Now I ask for a WHY to hackers :)
select sum(t01_caf) as SCAF, sum(t01_itm_cnt) as SCAF
from t01_20011231
where strpos(t01_tree_sortkey,(select t01_tree_sortkey from t01_20011231
where t01_upr_lvl_typ = :TypNiv and t01_upr_lvl_nbr =
:Niv))=1;
--
-- Stangely, I don't really understand why, there is no need of group by clause
-- there!
--
-- Here's the result:
-- scaf | scaf
---------------+---------
-- 40164802.36 | 1404296
--(1 row)
Thanks
--
Jean-Paul ARGUDO IDEALX S.A.S
Consultant bases de données 15-17, av. de Ségur
http://IDEALX.com/ F-75007 PARIS
On Tue, 2002-02-12 at 19:53, Ola Sundell wrote: > Recently I found an Oracle extension, START WITH .. CONNECT BY, which > greatly eases parsing of n-way tree structures stored in table format. > It's in TODO as WITH RECURSIVE, which is the SQL3 way of doing it, but I don't know if anyone is seriously working on it. I have done a little investigation, and I think that this could be doable without too much changes in planner/executor by doing repeated merge or hash joins. If we want automatic checks for infinite recursion there are also two ways of doing it: 1) use a has of already selected rows or 2) pick new rows from a realize'd table and mark them as removed there. ------------- Hannu