SQL Joins With Tree Structures: An Oracular Point Of View
March 17, 2015 Ted Holt
|
Tree structures are a part of life, especially in the world of manufacturing, where I make my living, so we may as well learn to deal with them. Today I return to this topic, featuring another tool that you can use to tackle the traversal of trees. In IBM 7.1, IBM added support for a tree-traversal syntax that Oracle invented ages ago. This syntax centers on two clauses of the select statement: START WITH and CONNECT BY. I’ll illustrate with a few simple queries. First, we need a tree structure to play with. Here are some simple bills of materials. select * from prodstruct order by 1,2 PARENT COMPSEQ COMPONENT QTYPER =========== ======= ============ ====== BARROW1 10 BUCKET1 1 BARROW1 20 HANDLE1 2 BARROW1 30 KITCOMMON 1 BARROW1 40 WHEELASSEM1 1 BARROW1 50 LABEL1 1 BARROW2 10 BUCKET2 1 BARROW2 20 HANDLE2 2 BARROW2 30 KITCOMMON 1 BARROW2 40 WHEELASSEM2 1 BARROW2 50 LABEL2 1 BARROW3 10 BUCKET3 1 BARROW3 20 HANDLE3 2 BARROW3 30 KITCOMMON 1 BARROW3 40 WHEELASSEM3 1 BARROW3 50 LABEL3 1 KITCOMMON 10 KITHARDWARE 2 KITCOMMON 20 LABELSAFETY 1 KITCOMMON 30 LEG 2 KITCOMMON 40 STRAP 1 KITHARDWARE 10 BOLT1 4 KITHARDWARE 20 BOLT2 2 WHEELASSEM1 10 AXLE1 1 WHEELASSEM1 20 TIRE1 1 WHEELASSEM1 30 TUBE1 1 WHEELASSEM1 40 WHEEL1 1 WHEELASSEM2 10 AXLE2 1 WHEELASSEM2 20 TIRE2 1 WHEELASSEM2 30 TUBE2 1 WHEELASSEM2 40 WHEEL2 1 WHEELASSEM3 10 AXLE3 1 WHEELASSEM3 20 TIRE3 1 WHEELASSEM3 30 TUBE3 1 WHEELASSEM3 40 WHEEL3 1 WHEEL1 10 WHEELBLANK1 2 WHEEL2 10 WHEELBLANK2 2 WHEEL3 10 WHEELBLANK3 2 Let’s find all the things that make up a BARROW1. select s.*, level from prodstruct as s start with s.parent= 'BARROW1' connect by prior s.component = s.parent PARENT COMPSEQ COMPONENT QTYPER LEVEL =========== ======= ============ ====== ===== BARROW1 10 BUCKET1 1 1 BARROW1 20 HANDLE1 2 1 BARROW1 30 KITCOMMON 1 1 KITCOMMON 40 STRAP 1 2 KITCOMMON 30 LEG 2 2 KITCOMMON 20 LABELSAFETY 1 2 KITCOMMON 10 KITHARDWARE 2 2 KITHARDWARE 20 BOLT2 2 3 KITHARDWARE 10 BOLT1 4 3 BARROW1 40 WHEELASSEM1 1 1 WHEELASSEM1 40 WHEEL1 1 2 WHEEL1 10 WHEELBLANK1 2 3 WHEELASSEM1 30 TUBE1 1 2 WHEELASSEM1 20 TIRE1 1 2 WHEELASSEM1 10 AXLE1 1 2 BARROW1 50 LABEL1 1 1 START WITH told the system that I wanted to explode the rows where BARROW1 is the parent item. CONNECT BY told the system to match the component of a retrieved (PRIOR) row to the parent of other rows. Notice the last column, which I created by including the LEVEL pseudo column. I purposely omitted the ORDER BY clause, hoping to see how the system would retrieve the data. The system has conducted what appears to me to be a depth-first search. You may, and probably will, prefer to retrieve the data in a certain sequence. You may use either ORDER BY or ORDER SIBLINGS BY, but not both, for this purpose. select s.*, level from prodstruct as s start with s.parent= 'BARROW1' connect by prior s.component = s.parent order siblings by 2 PARENT COMPSEQ COMPONENT QTYPER LEVEL =========== ======= =========== ====== ===== BARROW1 10 BUCKET1 1 1 BARROW1 20 HANDLE1 2 1 BARROW1 30 KITCOMMON 1 1 KITCOMMON 10 KITHARDWARE 2 2 KITHARDWARE 10 BOLT1 4 3 KITHARDWARE 20 BOLT2 2 3 KITCOMMON 20 LABELSAFETY 1 2 KITCOMMON 30 LEG 2 2 KITCOMMON 40 STRAP 1 2 BARROW1 40 WHEELASSEM1 1 1 WHEELASSEM1 10 AXLE1 1 2 WHEELASSEM1 20 TIRE1 1 2 WHEELASSEM1 30 TUBE1 1 2 WHEELASSEM1 40 WHEEL1 1 2 WHEEL1 10 WHEELBLANK1 2 3 BARROW1 50 LABEL1 1 1 I used ORDER SIBLINGS BY 2 to sort on the second column. Notice as an example the order of the rows with KITCOMMON in the PARENT column. Compare them to the same rows in the previous example. Let’s try a more useful query, very much like some queries I’ve been using lately in a project I’m working on. Which axle is used in a BARROW1? select s.*, level from prodstruct as s where s.component like 'AXLE%' start with s.parent= 'BARROW1' connect by prior s.component = s.parent PARENT COMPSEQ COMPONENT QTYPER LEVEL =========== ======= ========= ====== ===== WHEELASSEM1 10 AXLE1 1 2 BARROW1 requires one AXLE1, which is a component of wheel assembly WHEELASSEM1. AXLE1 is two levels deep in the bill of materials, but the query would have found it no matter how deep the axle had been. There’s more to this topic. See the Related Articles, especially Birgitta Hauser’s excellent article, listed below. My guess (and it is only a guess) is that IBM has added this “Oracular” syntax to help customers port Oracle applications to DB2. I’m not nuts about it. I may use it occasionally, but for the most part I plan to stick with recursive common table expressions. RELATED STORIES SQL Joins With Tree Structures Queries on the iSeries and System i Hierarchical Queries with DB2 Connect By
|
