Fetching from a hierarchical data structure?

  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.

  The problem that I'm having is that the resulting select statements are huge - larger than Sybase can handle. Why are they so huge? Well since the order objects themselves are keyed to particular leaves, if the user enters a selection in the middle of the hierarchy we have to find the leaves from that point down, and then do a search with a huge OR statement for all of the leaves. Consider this simple case...

   shipping billing

      A         1
     / \       / \
    B   C     2   3

  If the user wants to see everything in B and 3 that's fine...

SELECT * FROM orders WHERE shipping_id='B' and billing_id='3'

  But consider what happens when they select A and 1...

SELECT * FROM orders WHERE (shipping_id='B' and billing_id='3') or (shipping_id='B' and billing_id='2') or (shipping_id='C' and billing_id='3') or (shipping_id='C' and billing_id='2')

  That might not look so bad, but consider the case when there's 50 items in each tree and they pick items in the middle.

  A consultant wrote some Java code to avoid the resulting gigantic select statement by breaking down the query into chunks of ten billing/shipping pairs. This means lots of little select statements though, all on a network round-trip. At the end we have a set of order objects, but then we have to go back and select the unique addresses (and such) for them, which means another huge series of selects, fetching the details for each one of the basic records.

  Is there some way we can do this walk entirely in SQL? Instead of fetching all the leaves and then constructing a huge select, is there some way the SQL itself can walk the tree? Each account has a pointer to it's parent (or null).

  Any suggestions?

Maury Received on Fri Apr 06 2001 - 14:18:58 CDT