RE: hierarchical or tree type queries using OLWGS
Joe
Support for hierarchical tree traversal is not there in Informix.
There is support in Oracle in the form of the CONNECT BY PRIOR
statement, but it is probably a nonstandard SQL extension (SQL
gurus please correct me if I am wrong). However you can achieve
the same thing using recursion in a host language. Unfortunately
since SQL does not (perhaps should not?) support recursive
definition for its objects such as TEMP tables, this solution
cannot be done in SQL alone.
Heres my attempt at solving your problem using 4GL (you can do
the same thing in ESQL/C).
I created a table that contains data about employees and their
managers (the manager would be the parent and the employee would
be the child using your terminology). The table definition follows:
CREATE TABLE "informix".emp (
emp_id SERIAL,
emp_name CHAR(20),
mgr_name CHAR(20));
I then loaded the following data:
/* start data */
0|ERROL|TOP| /* This is the top level */
0|CLIFF|ERROL|
0|DIANNE|ERROL|
0|FRED|CLIFF|
0|AL|CLIFF|
0|LON|CLIFF|
0|JOHN|CLIFF|
0|KAREN|FRED|
0|ART|FRED|
0|MARK|FRED|
0|SUJIT|FRED|
0|BRENT|AL|
0|BILL|AL|
0|JERRY|DIANNE|
0|TENG|DIANNE|
0|HAL|BRENT|
0|SUTANO|BRENT|
0|GIRISH|BILL|
0|SUNDAR|BILL|
/* end data */
And here is the 4GL program that will explode the hierarchy. You can
specify the explosion to begin from either the TOP level (where the
employee has no manager) or from somewhere within the hierarchy to
explode downwards. I have tried two cases indicated by the #'d LET
statements.
/* start program */
DATABASE test
MAIN
DEFINE
v_emp_name CHAR(20),
v_mgr_name CHAR(20),
v_level INTEGER
LET v_mgr_name = "TOP"
#LET v_mgr_name = "CLIFF"
#LET v_mgr_name = "FRED"
LET v_level = 0
CALL get_subs(v_mgr_name, v_level);
END MAIN
FUNCTION get_subs(v_mgr_name, v_level)
DEFINE
v_mgr_name CHAR(20),
v_emp_name CHAR(20),
v_level INTEGER,
va_emps ARRAY[10] OF CHAR(20),
i INTEGER
DECLARE cGetSubs CURSOR FOR
SELECT emp_name FROM emp
WHERE mgr_name = v_mgr_nameLET i = 0
FOREACH cGetSubs INTO v_emp_name
LET i = i + 1
LET va_emps[i] = v_emp_name
END FOREACH
CLOSE cGetSubs
LET v_level = v_level + 1
WHILE i > 0
DISPLAY v_level, " ", v_mgr_name, va_emps[i]
CALL get_subs(va_emps[i], v_level)
LET i = i - 1
END WHILE
LET v_level = v_level - 1
END FUNCTION
/* end program */
HTH
Sujit Pal
----------
>From: Joe Freeman[SMTP:joe@freemansoft.com]
>Sent: Tuesday, August 12, 1997 6:43 PM
>To: informix-list@rmy.emory.edu
>Subject: hierachal or tree type queries using OLWGS
>
>We have a table which has parent_id pointer which points up to the same
>table so that we can buld a tree of data in the table. (Each child
>points to its parent). I'd like to write a query that finds a node in
>the tree and all its decendants. Is there any support for this type of
>query in informix? (I think Oracle and one of the MS products has
>something called hierarchal cursors to do this)
>?
>
>--
>FreemanSoft Inc.
>Consulting on Intranets based on Netscape and/or OPENSTEP
>technologies in the Washington DC area.