Almost hate to open this can of worms, Pandora's Box, whatever, but playing around with one of Razzak's examples related to the DB Treeview Form Control, I feel compelled. So, lemme' ask, what do y'all do w/in RBase when dealing with hierarchical data, such as organizational charts for units, staff, biological classification, etc?
For example: UnitID UnitName ParentUnitID HasChildren ------ --------------- ------------ ----------- 000000 Global NULL||000000 Y -- Either NULL or UnitID=ParentUnitID would serve to indicate root node 110000 Europe 000000 Y 111000 Great Britain 110000 Y 111100 York 111000 N 120000 Germany 110000 Y 121000 Hesse 120000 Y 121100 Darmstadt 121000 N 122000 Bavaria 120000 Y 122100 Würzburg 122000 N 122200 München 122000 Y 122210 Bierhalle 122200 Y 122211 Augustiner 122210 N 122212 Englische 122210 N 200000 North America 000000 Y 220000 USA 200000 Y 221000 Tennessee 220000 Y 221100 Memphis 221000 Y 221110 Sports Arenas 221100 Y 221111 Autozone Park 221110 N 221112 Fedex Forum 221110 N (NOTE: There is no implicit||explicit embedded intelligence in the ID values; that would be a different can of worms.) Given the above (rather arbitrary) hierarchy, I would like to be able to gather data, including aggregating functions (COUNT, SUM, etc) starting at any level/node and look either up or down from that starting point. Using the example, I might want to know how many children roll-up to Bavaria or, from Memphis, how many sports arenas are in the data. Of course, the real usefulness would occur when I also used data associated with, say Bierhalle, as in how many gallons (well, liters) of beer were sold or with Memphis, Sports Arenas, how many seats are available. I've been reading some Celko about this and even he - whether you hate/love/don't care about Celko - says that his solution, sans special/proprietary hierarchical operators, is functional but inelegant and not terribly efficient. It also appears that some DBMS vendors have implemented special hierarchical operators, such as WITH (DB2), CONNECT (Oracle), etc. Having played with the DB Treeview control yesterday and having quickly mapped our own organization table (in)to it, I thought, "This is really cool." Now, however, I'm looking for a way to select a node and walk up/down from that point, grabbing and/or aggregating data associated with the node and direction in order to display it on the same form. This isn't yet an enhancement request, but given RBTI's implementation of this control, it seems an obvious next step. I also suspect that it would take Razzak and RTeam little time to do this in a fashion compliant with current SQL standards. I also have no doubt that at least one among us would be EVER SO GRATEFUL ... Thanks, Steve in Memphis J. Stephen Wills Program Manager, Research Informatics Office of the Vice Chancellor for Research University of Tennessee Health Science Center 62 S. Dunlap, Suite 400 Memphis, TN 38163 Office: 901-448-2389 FAX : 901-448-7133
