Query / Procedure

[Ruslan Zasukhin]

On 7/2/08 5:01 PM, "Ivan Smahin" <ivan_smahin at paradigmasoft.com> wrote:

> Hello Fabian,
> 
> Wednesday, July 2, 2008, 4:34:31 PM, you wrote:
> 
>> Hi Ivan,
> 
>> I hope you can help me out with a demo or idea.
>> As you may remember, my database has a table which links multiple
>> addresses for a multi-level-marketing.
> 
>> So I have fields address_id,manager_id,startdate,enddate,...
> 
>> Address and Mangerids are from table addresses.
> 
>> So I can select who is the boss of another address, who is his
>> boss, who his boss and so on. Startdate and enddate are for historical save.
> 
>> Now I want to get all transactions that where dann for a boss and
>> all of his "childs" and all of their "childs" and so on. Maybe limited to a
>> specific timespam.
> 
>> Actually I use a loop in realbasic, but this needs a lot of time sometimes.
> 
>> So maybe I can using something other like a function or procedure? Can you
>> help me.
> 
>> Demodata is attached.
> 
> 
> I will think about it.
> 
> But you know - the main disadvantage of the
> SQL and relational model in particular is inability to query
> hierarchical structures.

Right,
 
> But I think it is possible to write some SP which will get some
> "boss" id and collect the info about his "children" It could be some
> temporary table for example. In turn each child can be the boss - so
> it would be recursively called SP.
> 
> That is not good because of infinite recursion possibility.
> Have to think about it.

IMO SP can help only if your RB app works with remote server
Then you win reducing many calls by network.


On local db, API way can help a lots here.


-- 
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]