In the article on SQL/JSON query functions we saw how JSON_TABLE converts JSON data to relational form. This article further explores various JSON_TABLE options for parsing and handling errors in JSON data.
Pseudocolumns
A pseudocolumn behaves like a table column but is not actually one. ROWNUM, SYSDATE, USER are a few examples of pseudocolumns.
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 (image alongside), get a single SQL to return the 4 types of employee roles as columns:
- PROJECT_MANAGER (Level 1)
- DBA (Level 2)
- TEAM_LEAD (Level 2)
- TEAM_MEMBER (Level 3)
This is trickier than transposing rows as columns in a non-hierarchical table, and needs a little extra to get the result. But it is nowhere near as tough as it appears. No multiple self-joins, no recursive CTE.
This post will show you how to flatten hierarchical data using a single SQL in Oracle.
The Magic Of ROWNUM
The “ROWNUM greater than” query never fails to have an eye-popping effect the first time anyone sees it. If you haven’t worked with ROWNUM in Oracle much before, be prepared!
First things first. What is ROWNUM?
ROWNUM is a pseudocolumn, assigning a number to every row returned by a query. The numbers follow the sequence 1, 2, 3…N, where N is the total number of rows in the selected set. This is useful when you want to do some filtering based on the number of rows, such as:
Selecting ODD or EVEN rows from a table
Question: How can I select only the even/odd rows from an Oracle table?
Answer: Talking of “even or odd rows” is meaningless in Oracle until you have ordered the rows. Oracle does not store rows in a specific order – the order has to come from the query.
Once the order is specified, then the query to retrieve odd rows or even rows can be written in this form:
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:
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 below: