How to master the CONNECT BY clause in Oracle to analyze hierarchical data

Inbuilt offerings by a heavyweight RBMS provider.

Photo by Windows on Unsplash

This article explores the functionalities and features offered by CONNECT BY clause in Oracle with a hands-on exercise approach.

Prerequisite: Oracle 9g or latest installed, any oracle SQL client. I have used Oracle’s sample schema for this article, you can download it too from here. Execute the SQL in your oracle client and you should be all set with data and schema.

Let’s get started

CONNECT BY is an oracle clause to place eligible datasets hierarchically. Meaning, usage of this function is generally for creating a new resultant query that will elaborate hierarchical relations in a table. Here is the basic syntax:

[ START WITH condition ]

CONNECT BY [ NOCYCLE ] condition

• START WITH is an optional keyword that can be used as a starting point for hierarchy.
• CONNECT BY describes the relationship between a child and parent row in the hierarchy.
• PRIOR keyword is the backbone of this utility. In connectby condition, at least one expression must be qualified with the prior operator that describes the parent record. The condition in CONNECT BY can have multiple expressions, and it should have at least 1 (but not limited to 1) Prior clause. Prior is a urinary operator which evaluates the immediate expression and is practically used with the ‘=’ operator.

With other operators, PRIOR generally puts the flow in an infinite loop and makes Oracle throw an error. The prior expression can not refer to sequences, NEXTVAL for instance. This operator can also be used in the SELECT block of a SQL to refer to Parent records, let us run a SQL statement on the dataset we installed at the beginning of this article to understand PRIOR clause usage in the SELECT part of the query.

What’s more?

CONNECT_BY_ROOT is another handy operator provided by Oracle, it can be used to flatten the hierarchy of an associate to the root level and place it in a single column cell. Here’s the syntax-

NOCYCLE solves the infinite loop problem with CONNECT BY PRIOR. Let’s explore this operator by updating the manager id in the root record.

When you run the CONNECT BY query now Oracle will present you with an ORA-01436 error due to the infinite loop created. NOCYCLE helps us use CONNECT BY even in datasets with Loop, another operator CONNECT_BY_ISCYCLE can be used to list out all the rows with loop.

What if you want to identify the leaf node employee in a hierarchy?

CONNECT_BY_ISLEAF pseudo column can be used in select to cater to this requirement. Column retuns 1 if it is a leaf node record, 0 otherwise.

Only Child/leaf nodes will have CONNECT_BY_ISLEAF set as 1

Hierarchy levels can be tricky and Parent nodes can be often misunderstood as leaf nodes

Thank you for reading through, stay tuned…

Visit my blog: https://www.storedprocs.com/ for more articles on Data Engineering.

#data #oracle #PLSQL #database