hierarchical queries

Hierarchical Data in a Relational Table

Flatten Hierarchical Data using a Single SQL

Oracle has some neat ways of handling hierarchical data. A single Oracle SQL can find the entire tree of data above or below a node in a hierarchy using the CONNECT BY clause. What if the requirement is to flatten hierarchical data? For example, given a table containing the employee reporting hierarchy of an organization […]

Read the Full Article →
LEVEL Pseudocolumn in Hierarchical Queries

LEVEL Pseudocolumn in Hierarchical Queries

LEVEL is a pseudocolumn (i.e. not a real column in the database but available in a query), which has a special function in hierarchical queries – it returns the position of any row in the hierarchy. Consider the hierarchy of employees in SCOTT’s EMP table, shown in tree structure like below:

Read the Full Article →
Hierarchical Queries in Oracle

Hierarchical Queries: A QuickStart Guide

A hierarchical query is one that works on data with a "tree" relationship. An Example of Hierarchical Data The employee-manager relationship in SCOTT’s famous EMP table. Each employee record has a manager’s id associated with it. In effect, there is a "tree" of data relationships At each level, I can get the employee-manager relationship as […]

Read the Full Article →