HierarchyIntro

CREATE SCHEMA DEVTEST;
DROP TABLE DEVTEST.COMPANYHIERARCHY;
CREATE COLUMN TABLE DEVTEST.COMPANYHIERARCHY ( 
	EMPLOYEE VARCHAR(5), 
	SUPERVISOR VARCHAR(5),	
	LOETYPE VARCHAR(1), 
	ORD INTEGER, 
	SALARY INTEGER 
);

INSERT INTO DEVTEST.COMPANYHIERARCHY VALUES ( 'F1 A1',  null, 	'x', 1, 150 );
INSERT INTO DEVTEST.COMPANYHIERARCHY VALUES ( 'F1 B1', 'F1 A1', 'y', 1, 120 );
INSERT INTO DEVTEST.COMPANYHIERARCHY VALUES ( 'F1 B2', 'F1 A1', 'z', 2, 90 );
INSERT INTO DEVTEST.COMPANYHIERARCHY VALUES ( 'F1 C1', 'F1 B1', 'x', 1, 40 );
INSERT INTO DEVTEST.COMPANYHIERARCHY VALUES ( 'F1 C2', 'F1 B1', 'y', 2, 60 );
INSERT INTO DEVTEST.COMPANYHIERARCHY VALUES ( 'F1 C3', 'F1 B2', 'z', 3, 75 );
INSERT INTO DEVTEST.COMPANYHIERARCHY VALUES ( 'F1 C4', 'F1 B2', 'x', 4, 30 );
INSERT INTO DEVTEST.COMPANYHIERARCHY VALUES ( 'F1 D1', 'F1 C3', 'y', 1, 10 );
INSERT INTO DEVTEST.COMPANYHIERARCHY VALUES ( 'F1 D2', 'F1 C3', 'z', 2, 25 );
INSERT INTO DEVTEST.COMPANYHIERARCHY VALUES ( 'F1 D3', 'F1 C4', 'x', 3, 30 );
INSERT INTO DEVTEST.COMPANYHIERARCHY VALUES ( 'F2 A2',  null, 	'y', 2, 80 );
INSERT INTO DEVTEST.COMPANYHIERARCHY VALUES ( 'F2 B3', 'F2 A2', 'z', 3, 45 );
INSERT INTO DEVTEST.COMPANYHIERARCHY VALUES ( 'F2 C5', 'F2 A2', 'x', 5, 30 );

/*
HIERARCHY (
	<hierarchy_genfunc_source_spec>
	[<hierarchy_genfunc_start_cond>]
	[<hierarchy_genfunc_depth_spec>]
	[<hierarchy_genfunc_orphan_spec>]
	[<hierarchy_genfunc_cache_spec>]
)
*/

SELECT *
FROM HIERARCHY (
	SOURCE ( 
		SELECT 
			EMPLOYEE AS node_id, 
			SUPERVISOR AS parent_id
		FROM DEVTEST.COMPANYHIERARCHY
	)
);