A typical scenario that involves splitting a comma-separated string into rows:
- in a UI-driven query, the user would enter a list of IDs into an input box, and
- the application should retrieve details for the input list of IDs
The count of IDs in the list can vary — the user may enter a single value or multiple, and the underlying SQL should fetch the details for all of them.
This sounds simple enough on the face of it: bind a parameter to a comma-separated list of values such as ‘A1,A2,A4’ and then look for corresponding rows using SQL for those IDs.
There’s a catch though.
[click to continue…]
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)
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.
[click to continue…]
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:
[click to continue…]
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:
[click to continue…]
