Chasing the tail

Chasing the tail

Post by Peter Harr » Sat, 14 Jan 1995 00:38:48



Hi all,
I have got one here that is making me wonder: I am trying to
squeeze information out of a commercial accounting package, Bill of
Materials Header and Detail. So far so good, the catch is that the Detail
table is self referencing: components go to make up components etc until
you get the finished part.

Header ( header_key char(20), other stuff about the finished part.. )

Detail ( detail_key char(20), component_key char(20) <-references a
                                                       sub_component's
                                                       detail_key )

The detail table holds costing information which I need to sum to get a
cost per unit for each part in the header table. Easy?

I tried building a reference table, with each detail component listed
against what part in the header it ultimately goes to make up. The
mathematics of permutations and combinations made me fall off the edge
of the disk.

I tried recursively following the detail_key, component_key,
detail_key... trail for each finished part and had a program that took
days to run.

Anybody come across this sort of problem before? bright ideas? Am I
missing the obvious?

All the best,    Pete.

 
 
 

Chasing the tail

Post by Alan Popi » Sat, 14 Jan 1995 03:58:09


} Date: Thu, 12 Jan 1995 07:51:09 +1100 (EST)

} Subject: Chasing the tail

}
} Hi all,
} I have got one here that is making me wonder: I am trying to
} squeeze information out of a commercial accounting package, Bill of
} Materials Header and Detail. So far so good, the catch is that the Detail
} table is self referencing: components go to make up components etc until
} you get the finished part.
}
} Header ( header_key char(20), other stuff about the finished part.. )
}
} Detail ( detail_key char(20), component_key char(20) <-references a
}                                                        sub_component's
}                                                        detail_key )
}
} The detail table holds costing information which I need to sum to get a
} cost per unit for each part in the header table. Easy?
}
} I tried building a reference table, with each detail component listed
} against what part in the header it ultimately goes to make up. The
} mathematics of permutations and combinations made me fall off the edge
} of the disk.
}
} I tried recursively following the detail_key, component_key,
} detail_key... trail for each finished part and had a program that took
} days to run.
}
} Anybody come across this sort of problem before? bright ideas? Am I
} missing the obvious?
}
} All the best,    Pete.

It sounds like your database has a circular reference in the Detail-Detail
linkages.  This might be valid if a component has alternate configurations,
one of which logically ends the recursion.  Otherwise, your DB is in deep
kimchee.

Assuming no circular links, then your query should terminate long before
"days" of running.  Whether your disk gets full is a function of DB size
vs. free disk space.

Now if your DB does include valid alternate component configurations, some
of which (circularly) reference components "higher" in the tree, then you
need to write a "smart" parts explosion report that knows how to end the
recursion.  Straight SQL is normally not smart enough to do this.

Regards,
Alan                   ___________________________
______________________| R. Alan Popiel            |__________________________
\  Internet:          | Martin Marietta, SLS      |                         /

  )Voice:             | Denver, CO 80201-0179 USA |                       (
 /   303-977-9998     |___________________________|  (But you knew that!)  \
/________________________)                     (____________________________\

 
 
 

1. Chasing the tail

Thanks to jenb, Jack Parker & Alan Popiel.

What I ended up doing was a series of outer joins from the detail
table to itself. It looked a bit like this:

select header.header_key, header.description,
       level_1.detail_key, level_1.cost_per_unit, level_1.component_key,
       level_2.detail_key, level_2.cost_per_unit, level_2.component_key,
       level_3.detail_key, level_3.cost_per_unit, level_3.component_key,
       ...
  from header, detail level_1,
         outer ( detail level_2,
                outer ( detail level_3,
                        ...            
                                 ...))
While this is hard coding the levels of sub-components, is this case this was
a fixed system wide parameter determined by the application, so I could
get away with it. Like all these things, the answer was obvious when I
stopped thinking about it!

2. Connection pooling in asp

3. chasing the impossible?

4. Sybase FAQ: 4/16 - section 3

5. Chasing Down Some SQL Syntax

6. MIGRATION FROM ACCESS97 TO SQL SERVER

7. MDX - Tail + Filter on calculated member

8. 4.5 Query Bombs in 5.0

9. MDX - Filter and Tail

10. Tail Function in a Named Set

11. Looking for SQL 6.5 Tail version.

12. Preserve tailing blanks in data

13. REDUNDANT NARROW DIFFERENTIAL TWIN-TAIL SCSI BUS CONFIGURATION