5.9 Hierarchical queries
|
| < Day Day Up > |
|
5.9 Hierarchical queries
With Oracle CONNECT BY ... START WITH ...clause can be used to select data that has a hierarchical relationship, usually some sort of parent - child relationships. In Example 5-29, the table EMPLOYEES consists of just these attributes: parent and child. We make sure (by means of a unique constraint) that the child is unique within the table.
Example 5-29: Oracle hierarchical query
 |
SELECT substr(lpad(' ', level * 2) || emp_id,1,20) AS emp_id , last_name , emp_mgr_id , level FROM employees CONNECT BY PRIOR emp_id = emp_mgr_id START WITH emp_mgr_id IS NULL; EMP_ID LAST_NAME EMP_MGR_ID LEVEL -------------------- -------------------- ---------- ---------- 10000 Sands 1 10001 Marcus 10000 2 10004 Polite 10001 3 10005 Tenor 10001 3 10002 January 10000 2 10008 Even 10002 3 10010 December 10002 3 10011 August 10002 3 10003 March 10000 2 10006 Blonde 10003 3 10007 Damon 10003 3 10009 Ration 10003 3 |
In the following we provide you a DB2 UDF to get the same result. The identity of each row is actually its encoded position in the hierarchy. This is arbitrary. The solution also assumes that the resulting sorting can be done based on the path. Here this works as long as not more than nine siblings exist on any given level. However, a simple formatting of the path to a specific number of digits per level can solve this problem.
The script uses SQL table functions. This capability however is only used for encapsulation and not required for function.
The function get_direct_childs() collects all immediate children of a node in the hierarchy and returns them together with their relative position to each other and in the tree; see Example 5-30.
Example 5-30: Computing of direct child data
|
CREATE FUNCTION get_direct_childs(code VARCHAR(30), parent INTEGER) RETURNS TABLE(code VARCHAR(30), id INTEGER) READS SQL DATA DETERMINISTIC NO EXTERNAL ACTION RETURN SELECT code || '.' || RTRIM(CHAR(RANK() OVER (ORDER BY child_id))), child_id FROM (SELECT empno FROM emp WHERE emp.mgr = get_direct_childs.parent) AS T(child_id)!
|
The function get_rec_childs() contains the recursive logic. It starts with the root, which is provided by the caller, and then collects children until no more new children can be found. See Example 5-31.
Example 5-31: Hierarchical query with entry point
|
CREATE FUNCTION get_rec_childs(root INTEGER) RETURNS TABLE(code VARCHAR(30), id INTEGER) READS SQL DATA DETERMINISTIC NO EXTERNAL ACTION RETURN WITH rec(code, id) AS (VALUES(CAST('1' AS VARCHAR(30)), root) UNION ALL SELECT t.code, t.id FROM rec, TABLE(get_direct_childs(rec.code, rec.id)) AS T) SELECT code, id FROM rec ! |
This function get_level() compute the hierarchy level of the current data; see Example 5-32.
Example 5-32: Compute hierarchy level
|
CREATE FUNCTION get_level(code VARCHAR(30)) RETURNS INTEGER DETERMINISTIC NO EXTERNAL ACTION RETURN (length(code) - length(replace(code, '.', '')))!
|
You get the hierarchy tree with the UDF get_rec_childs(). As a parameter, you have to specify the root.
In Example 5-33 you see the use of the hierarchy function and its output with an adequate format.
Example 5-33: Sample use of hierarchical query
|
SELECT T.code ,T.id ,substr( (space(2 * get_level(code)) || employees.last_name) , 1 , 20) as last_name ,emp_mgr_id FROM TABLE(get_rec_childs(10000)) AS T ,employees where T.id = employees.emp_id ORDER BY code! CODE ID LAST_NAME EMP_MGR_ID ----------- ----------- -------------------- ----------- 1 10000 Sands - 1.1 10001 Marcus 10000 1.1.1 10004 Polite 10001 1.1.2 10005 Tenor 10001 1.2 10002 January 10000 1.2.1 10008 Even 10002 1.2.2 10010 December 10002 1.2.3 10011 August 10002 1.3 10003 March 10000 1.3.1 10006 Blonde 10003 1.3.2 10007 Damon 10003 1.3.3 10009 Ration 10003
|
|
| < Day Day Up > |
|
