Recursiv/hierarchical query?

[Bart Pietercil]

Hi List, Ruslan


For me an sql_function would be

select collect_branch(tbl_a,  
link_to_follow,startfrom_recid,optional_nr_of_levels_before_returning)
which would return the recid's of the collected record, so I could  
write this

select tbl_a.* where recid in (select  
collect_branch(tbl_a,link_to_follow,aRecid))

Maybe I am to focused on my short term needs and we should be able to  
give instead of a link_to_follow a join condition ?
Maybe add also a count_branch_depth(tbl_A,link_to_follow,aRecID) which  
would return the number of iterations before reaching the lowest level  
of the branch.

Then we could write something like:

if(count_branch_depth(tbl_A,link_to_follow,aRecID) > someNumber) then
	select tbl_a.* where recid in (select  
collect_branch(tbl_a,link_to_follow,aRecid,someNumber))
else
	select tbl_a.* where recid in (select  
collect_branch(tbl_a,link_to_follow,aRecid))
end if

tia,


Bart


On 22 Jan 2009, at 08:18, Ruslan Zasukhin wrote:

> On 1/22/09 1:49 AM, "Bart Pietercil" <bart.pietercil at cognosis.be>  
> wrote:
>
> Hi Bart,
> Hi Thorsten,
>
> 1) not agree that DB should not walk by tree :)
>    * actually Oracle, MS have own syntaxes to do this.
>    * SQL 2003 also have solution for this task, although it seems ugly
>        it needs write SQL query on 100 lines to get it as I have see,
>
> 2) Its issue of Relational model - it works with tables.
>
> 3) Valentina built as Object-Relational, so not moral issues to add  
> trees
> :-)
>
>
> I suggest we all together think about this task.
> I have show already how will looks 3 new API function.
> Now we need invent nice SQL for these tasks.
>
> Of course we need again look around on other dbs, then select best  
> things.
>
>
>> mmm, then we need to switch to a database that has OO in its roots...
>> Oh no, wait.....
>
>> On 21 Jan 2009, at 23:09, Thorsten Hohage wrote:
>
>>> IMHO searching a list is a DB job, traversing a tree is a OO- 
>>> concept.
>
> -- 
> Best regards,
>
> Ruslan Zasukhin
> VP Engineering and New Technology
> Paradigma Software, Inc
>
> Valentina - Joining Worlds of Information
> http://www.paradigmasoft.com
>
> [I feel the need: the need for speed]
>
>
> _______________________________________________
> Valentina mailing list
> Valentina at lists.macserve.net
> http://lists.macserve.net/mailman/listinfo/valentina