For instance, if the user selects top most organization in hierarchy, the pay slips of all the children and sub children must be displayed in the same way if user selects a child organization then pay slips of that child and all of its sub children must be displayed.
I just need to write the query for getting this parent/child relationship on SQL, according to user input.
Requires Free Membership to View
Well, in order to display PARENT CHILD Relationship you can use the CONENCT BY statement... You can also use an intrinsic column called "LEVEL" in your SQL statement to provide indentation to your output.
Let's say you have an organization hierarchy table like this:
ORGANIZATION_HIEARARCHIES (ORG_HIER_ID NUMBER NOT NULL, CHILD_ORGANIZATION_ID NUMBER NOT NULL, PARENT_ORGANIZATION_ID NUMBER)
You also have an ORGANIZATIONS table that is something like this:
ORGANIZATION (ORGANIZATION_ID NUMBER NOT NULL, ORGANIZATION_NAME VARCHAR2(100) NOT NULL)
ORGANIZATION_ID is a foreign key to both CHILD_ORGANIZATION_ID column and PARENT_ORGANIZAITON_ID column.
Anyway, to provide a hierarchy that starts with a child_organization_id and displays all of its children and grand children you can use the following SQL statement.
SELECT t1.organization_id,
RPAD (' ', 3*(level-1))||t1.organization_name displayed_organization
FROM organizations t1,
organization_hierarchies t2
WHERE t2.child_organization_id = t1.organization_id
CONNECT BY t2.parent_organization_id = prior t2.child_organization_id
START WITH t2.child_organization_id = :PARAMETER_ORG_ID
ORDER SIBLINGS BY SORT_ORDER
Please note that the ORDER SIBLINGS BY SORT_ORDER clause is a new feature in Oracle9i SQL that enables you to order siblings in a hierarchical tree that was not available in earlier version of Oracle.
This was first published in March 2004
Join the conversationComment
Share
Comments
Results
Contribute to the conversation