1 / 17

Hierarchical Retrieval

Hierarchical Retrieval. Objectives. After completing this lesson, you should be able to do the following: Interpret the concept of a hierarchical query Create a tree-structured report Format hierarchical data Exclude branches from the tree structure. ….

kirkan
Télécharger la présentation

Hierarchical Retrieval

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Hierarchical Retrieval

  2. Objectives • After completing this lesson, you should be able to do the following: • Interpret the concept of a hierarchical query • Create a tree-structured report • Format hierarchical data • Exclude branches from the tree structure

  3. … Sample Data from the EMPLOYEES Table

  4. Natural Tree Structure EMPLOYEE_ID = 100 (Parent) King MANAGER_ID = 100 (Child) Mourgos Kochhar De Haan Zlotkey Hartstein Whalen Higgins Rajs Davies Matos Vargas Hunold Fay Gietz Grant Ernst Lorentz Abel Taylor

  5. Hierarchical Queries WHEREcondition: SELECT [LEVEL], column, expr... FROM table [WHERE condition(s)] [START WITH condition(s)] [CONNECT BY PRIOR condition(s)] ; expr comparison_operator expr

  6. Walking the Tree • Specifies the condition that must be met • Accepts any valid condition • Using the EMPLOYEES table, start with the employee whose last name is Kochhar. Starting Point START WITH column1 = value ...START WITH last_name= 'Kochhar'

  7. Walking the Tree Walk from the top down, using the EMPLOYEES table. CONNECT BY PRIOR column1 = column2 ... CONNECT BY PRIOR employee_id = manager_id Direction Column1 = Parent Key Column2 = Child Key Top down Bottom up Column1 = Child Key Column2 = Parent Key

  8. Walking the Tree: From the Bottom Up SELECT employee_id, last_name, job_id, manager_id FROM employees START WITH employee_id = 101 CONNECT BY PRIOR manager_id = employee_id ;

  9. Walking the Tree: From the Top Down SELECT last_name||' reports to '|| PRIOR last_name "Walk Top Down" FROM employees START WITH last_name = 'King' CONNECT BY PRIOR employee_id = manager_id ;

  10. Ranking Rows with the LEVEL Pseudocolumn Level 1 root/parent King Level 2 parent/child Mourgos Kochhar De Haan Zlotkey Hartstein Level 3 parent/child /leaf Whalen Higgins Rajs Davies Matos Vargas Hunold Fay Gietz Grant Ernst Lorentz Abel Taylor Level 4 leaf

  11. Formatting Hierarchical Reports Using LEVEL and LPAD • Create a report displaying company management levels, beginning with the highest level and indenting each of the following levels. COLUMN org_chart FORMAT A12 SELECT LPAD(last_name, LENGTH(last_name)+(LEVEL*2)-2,'_') AS org_chart FROM employees START WITH last_name='King' CONNECT BY PRIOR employee_id=manager_id;

  12. Pruning Branches Use the WHERE clause to eliminate a node. Use the CONNECT BY clause to eliminate a branch. CONNECT BY PRIOR employee_id = manager_id AND last_name != 'Higgins' WHERE last_name != 'Higgins' Kochhar Kochhar Whalen Higgins Whalen Higgins Gietz Gietz

  13. Summary • In this lesson, you should have learned the following: • You can use hierarchical queries to view a hierarchical relationship between rows in a table. • You specify the direction and starting point of the query. • You can eliminate nodes or branches by pruning.

  14. Practice 7(Exercise 1 ) • Produce a report showing an organization chart for Mourgos’s department. • Print last names, salaries, and department IDs.

  15. Practice 7(Exercise 2 ) • Create a report that shows the hierarchy of the managers for the employee Lorentz. • Don’t display Lorentz • Display his immediate manager first.

  16. Practice 7(Exercise 3) • Create an indented report showing the management hierarchy starting from the employee whose LAST_NAME is Kochhar. Print the employee’s last name, manager ID, and department ID.

  17. QUIZ • Produce a company organization chart that shows the management hierarchy. • Print the employee’s last name, employee ID, manager ID, and job ID. • Exclude all people with a job ID of IT_PROG. • Start with the person at the top level who has no manager id and exclude De Haan and those employees who report to De Haan. Good luck

More Related