Re: Fetching from a hierarchical data structure?

maury,

one should think twice before posing a data modeling question to a bunch of DBAs...! ;)

anyhow, to provide you a really good answer would require seeing your data model, as i am sure billing entites and ship to locations have other uses in the application as well. i also expect that a billing entity may have a completely different location from one or more shipping locations, so this is a many-to-many relationship. it is unclear from your statement if "Buffalo" is a division or a receiving location. thus, to simplify this discussion, we will consider the question at hand the only relevant interest. this discussion also considers that tables can be created or restructured if needed so modifying SQL or code is only one approach.

that said, consider the following.

create table bill_to (org_id...)
create table ship_to (receiver_id, ....) create table org_receiver (receiver_id, org_id)

if you really want to avoid using "CONNECT BY" then you have to "flatten" the hierarchy resulting in a table with O(n**2) rows, with one table for each hiearchical structure.

create table org_hier (org_id, pred_id, level) -- pred_id is the org_id of any presedecessor in the hierarchy. level is optional, useful for ordering. create table rec_hier (receiver_id, pred_id, level) -- location hierarchy, analogous to organizational hierarchy

now, using these two hierarchy tables and the org_receiver table, you can process any query you like starting at any point you like.

example: Company X, Division A, District 1, Units 1, 2, and 3. show all organizations with orders for District 1.

    select b.org_id, ...

     from bill_to b, org_hier o
     where b.org_id = o.org_id and  o.pred_id = 'District 1'
     [order by o.level ]

"Maury Markowitz" <maury_at_remove_this.sympatico.ca.invalid> wrote in message news:CEoz6.37661$XV.9494838_at_news3.rdc1.on.home.com...
> I'm one of the authors of an order processing system, and I've run into
 a
> problem I'm hoping some of you could help with. Sorry for the x-post.
>
> Our customers have multiple divisions, each with many buildings (often
> shared) and multiple organizations and billing entities within each. In
> order to ship to them they have to tell us one of the shipping accounts
 and
> one of the billing accounts. So as you might expect, we have an order
> "object" that has a key to one of each of these account types, as well as
> pointers to other relevant info and status bits and such.
>
> The customers need to be able to do searches at any level of the
> hierarchy. In many cases they want to know every order for everything in
 the
> compnay, but in other cases they want to know all the items that were
> shipped to any of the shipping docks in a particular building. That's why
> they're hierarchical, so they can select "Buffalo" and see everything that
> went to any of their buildings there.
>
>
Received on Tue Apr 10 2001 - 23:29:29 CEST