How to Hierarchical Query, Parents, Children and Their Cycles

This is a three-part Series

1. How to Hierarchical Query, Parents, Children and Their Cycles
2. How To Create Report in Oracle APEX based on Hierarchical Query
3. How to use Hierarchical Query in Tree Plugin

Part-1 How to Hierarchical Query, Parents, Children and Their Cycles

Almost every one on this planet like trees right. Some of us like them as essential part of the world ecological system while others have created a very peculiar taste for it, like the timber mafia and the likes.

Usually you would imagine a tree standing up , however , programmers usually think of trees up-side-down,weird hmm... till it all make sense.😇

An Example of a Hierarchy is an Organization chart representing a hierarchy of employees.

First, Know your terminology.

• You can have multiple trees in a hierarchical table. 
• Node
• A row in a table that represents a specific entry in a hierarchical tree structure. 
• Root [node]
• The uppermost node/Starting Point in a hierarchical structure. 
• It has no parent. and only one root in any given tree
• Parent [node]
• A node that is one level up in a tree. It has a child node below it.
• Child [node]
• A node that is one level down in a tree. It has a parent node above it.
• Leaf [node]
• A node at the lowest levels with no children. As is analogous with the leaf of a tree there is nothing beyond it.
• Leaf Nodes do not all need to be at the same level, but must be without children.
• Level
• A layer of nodes. From the Root and Traversing(hopping from one node to another) through Its children, on each hop of Node downards is the next Level.

Here is what we gonna do:

1. The Foremost Clauses, Operators and The Template Query
2. Research into Data to find How to Construct your Hierarchy
3. A Little Extra Detail

The Foremost Clauses, Operators and The Template Query

The Foremost Clauses and Operators:

• The START WITH condition1
• All rows that satisfy START WITH condition1 are considered root rows.
• If you don't specify the START WITH clause, all rows are considered root rows.
• CONNECT BY condition2 clause and The PRIOR operator
• condition2 must contain the PRIOR operator, which is used to identify columns from the parent row, 
• condition2 cannot contain a subquery.
• PRIOR is used to connect each child row to its parent row by connecting manager_emp_id in the child to emp_id in the parent
• The LEVEL pseudocolumn
• Shows the Level of the current row in the Hierarchy
• ORDER SIBLINGS BY Column;
• Sorting at each level while ensuring that child nodes remain underneath their parents, at the same time preserving the hierarchy

The Template Query

The Template Query will cover the following:

• Tree Traversal Using ANSI SQL, 
• Identifying the Number of Levels
• Identifying Leaf Nodes
• Finding the Path to a Node
• Ordering Hierarchical Data

If You Use the Template Query You would at least know what is going on with the Hierarchy, You will get Enough Information to see the Hierarchy.

Code

Select LEVEL, LPAD('  ',3*(LEVEL - 1)) || Column AS  Alias,
  DECODE(CONNECT_BY_ISLEAF, 1, 'TRUE', 'FALSE') IS_LEAF,
 TRIM(LEADING '>' FROM
            TRIM(LEADING '=' FROM SYS_CONNECT_BY_PATH(Column, '=>'))) As Path
from
 Table t
START WITH Condition1
CONNECT BY Condition2
ORDER SIBLINGS BY Column;

Uses

• LEVEL  => display which Level is the Current row on.
• CONNECT_BY_ISLEAF => pseudocolumn that returns 1 if the current row is a leaf, and returns 0 if the current row is not a leaf
• LPAD('  ',3*(LEVEL - 1)) ||  => to have a nice Indented Display with each level
• TRIM(LEADING '>' FROM  TRIM(LEADING '=' FROM SYS_CONNECT_BY_PATH(Column, '=>'))) => to have a nice display From the Root Node to the Current Node.
• ORDER SIBLINGS BY Column; => for Ordering on a Column and ensuring that child nodes remain underneath their parent

I have used the scott[y] from Star Trek 😉 , schema throughout the article

Just plugin the Table and Columns Names to see the Result

Select LEVEL,
       DECODE(CONNECT_BY_ISLEAF, 1, 'TRUE', 'FALSE') IS_LEAF,
       LPAD('  ', 3 * (LEVEL - 1)) || t.ename Name,
       TRIM(LEADING '>' FROM
            TRIM(LEADING '=' FROM SYS_CONNECT_BY_PATH(t.ename, '=>'))) Path,
            CONNECT_BY_ROOT t.ename as "Top Manager"
           
  from emp t
 START WITH t.mgr is NULL
CONNECT BY PRIOR t.empno = t.mgr
 ORDER SIBLINGS BY t.ename;

PRIOR is used to connect each child row to its parent row by connecting manager_empno in the child to empno in the parent

Research into Data to find How to Construct your Hierarchy

Now it's time play around and research into data to see how will you construct your hierarchy.

• Finding Root Nodes
• Finding the Connection/Relationship between Nodes
• Finding a Node's Immediate Parent
• Finding Leaf Nodes

Finding Root Nodes

For Finding Root Nodes, We know that Root [node] The uppermost node/Starting Point in a hierarchical structure which has no parent
So I need employee which has no manager and hence t.mgr is NULL

Select t.empno ,t.ename , t.mgr
 FROM emp t
 where
  t.mgr is NULL

This will form the Starting base for my Hierarchical query.
As
START WITH t.mgr is NULL

Finding the Connection/Relationship between Nodes

As we know that each employee has a manager ,  except the root node ,  and the for each employee the manager(empno) is stored in the mgr Column, so we can use this information to construct our relatioship of parent-child.

CONNECT BY PRIOR t.empno = t.mgr

Finding a Node's Immediate Parent

We use a simple self-join to find the immediate parent, i.e. each employee's Manager.

Select e.ename "Employee" , m.ename "Manager"
 FROM emp e JOIN emp m
 ON e.mgr = m.empno
 Order by m.ename

Finding Leaf Nodes

Here is a Co-related Subquery using the EXISTS operator, to get the employees who does not have children, i.e. who are only employees but not a manager and thus whose empno is not in the mgr Column.

Select *
 FROM emp e
 WHERE NOT EXISTS (SELECT 1 FROM emp t where e.empno = t.mgr)

Remember I used the CONNECT_BY_ISLEAF  in the Template Query , however , here we are talking about research in to data for preparing and comparison for validaty.

A Little Extra Detail

• Finding the Number of Levels
• Checking for Ascendancy/Finding in the Sub-Tree
• Listing Multiple Root Nodes
• Listing the Top Few Levels of a Hierarchy
• Aggregating a Hierarchy
• Identifying Cycles and ignoring it

Finding the Number of Levels

Here are a few queries to find

The Maximum depth of the Hierarchy

Select MAX(LEVEL)        
  from emp t
 START WITH t.mgr is NULL
CONNECT BY PRIOR t.empno = t.mgr

The No of Nodes Per Level

Select LEVEL, COUNT(t.ename) NO_OF_NODES      
  from emp t
 START WITH t.mgr is NULL
CONNECT BY PRIOR t.empno = t.mgr
 GROUP BY LEVEL;

Checking for Ascendancy/Finding in the Sub-Tree

To see if a  Node is Direct or Indirect Ascendant(Parent) of  a [Child/Descendant] Node.Let's say If we want to see if a Manager is a Direct or Indirect Manager of an Employee. For example, to see if Jones is a Manager(Direct/Indirect) of  Smith or Ward

 Select LEVEL,
       DECODE(CONNECT_BY_ISLEAF, 1, 'TRUE', 'FALSE') IS_LEAF,
       LPAD('  ', 3 * (LEVEL - 1)) || t.ename Name,
       TRIM(LEADING '>' FROM
            TRIM(LEADING '=' FROM SYS_CONNECT_BY_PATH(t.ename, '=>'))) Path,
            CONNECT_BY_ROOT t.ename as "Top Manager"
         
  from emp t
  WHERE t.ename = 'SMITH'
START WITH t.ename = 'JONES'
CONNECT BY PRIOR t.empno = t.mgr
 ORDER SIBLINGS BY t.ename;

Here we START WITH t.ename = 'JONES'  ANd Look for 'SMITH' in the Sub-tree , WHERE t.ename = 'SMITH'

Listing Multiple Root Nodes

For Example Finding the Topmost Manager of each department

Select LEVEL,
       DECODE(CONNECT_BY_ISLEAF, 1, 'TRUE', 'FALSE') IS_LEAF,
       LPAD('  ', 3 * (LEVEL - 1)) || t.ename Name,
       TRIM(LEADING '>' FROM
            TRIM(LEADING '=' FROM SYS_CONNECT_BY_PATH(t.ename, '=>'))) Path,
            CONNECT_BY_ROOT t.ename as "Top Manager"
         
  from emp t
 START WITH t.mgr is NULL
CONNECT BY t.mgr = PRIOR t.empno
and t.deptno != PRIOR t.deptno
 ORDER SIBLINGS BY t.ename;

by adding and t.deptno != PRIOR t.deptno found the top-most managers.

Listing the Top Few Levels of a Hierarchy

So Listing on the Top-Managers

Select LEVEL,
       DECODE(CONNECT_BY_ISLEAF, 1, 'TRUE', 'FALSE') IS_LEAF,
       LPAD('  ', 3 * (LEVEL - 1)) || t.ename Name,
       TRIM(LEADING '>' FROM
            TRIM(LEADING '=' FROM SYS_CONNECT_BY_PATH(t.ename, '=>'))) Path,
            CONNECT_BY_ROOT t.ename as "Top Manager"
         
  from emp t
  Where LEVEL <= 2
 START WITH t.mgr is NULL
CONNECT BY t.mgr = PRIOR t.empno
 ORDER SIBLINGS BY t.ename;

We used the Where LEVEL <= 2 to limit the query to only the First  2 Levels.

Aggregating a Hierarchy

For example, you may want to sum the salaries of all employees reporting to a specific employee

Select SUM(t.sal)    
  from emp t
 START WITH t.ename = 'JONES'
CONNECT BY PRIOR t.empno = t.mgr

You may want to consider each employee as a root/Manager, and for each employee print out the sum of the salaries of all subordinate employees.

For Each Manager , The Sum of the Manager and  Subordinates Salary

 WITH
   T
AS
(
   SELECT
      CONNECT_BY_ROOT ENAME ENAME,
      SAL
   FROM
      EMP
   CONNECT BY
      MGR=PRIOR EMPNO
)
SELECT
   ENAME,
   SUM(SAL)
FROM
   T
GROUP BY
   ENAME;


 Select LEVEL,
       DECODE(CONNECT_BY_ISLEAF, 1, 'TRUE', 'FALSE') IS_LEAF,
       LPAD('  ', 3 * (LEVEL - 1)) || t.ename Name,
       TRIM(LEADING '>' FROM
            TRIM(LEADING '=' FROM SYS_CONNECT_BY_PATH(t.ename, '=>'))) Path,
            CONNECT_BY_ROOT t.ename as "Top Manager"
         
  from emp t

 --START WITH t.mgr is NULL
CONNECT BY PRIOR t.empno = t.mgr
 ORDER SIBLINGS BY t.ename;

For Each Manager , The Sum of its Subordinates Salary

WITH
   T
AS
(
  Select LEVEL,
       DECODE(CONNECT_BY_ISLEAF, 1, 'TRUE', 'FALSE') IS_LEAF,
       t.ename Name,
       CONNECT_BY_ROOT t.ename as Manager ,
       t.sal SALARY,
       TRIM(LEADING '>' FROM
            TRIM(LEADING '=' FROM SYS_CONNECT_BY_PATH(t.ename, '=>'))) Path      
  from emp t
 --START WITH t.mgr is NULL
Where   CONNECT_BY_ROOT t.ename != t.ename
CONNECT BY PRIOR t.empno = t.mgr
 ORDER SIBLINGS BY t.ename
)
SELECT
   Manager,
   SUM(SALARY)
FROM
   T
GROUP BY
   Manager;

Here are the important Points

•  --START WITH t.mgr is NULL , is commented, Remember when I said, If you don't specify the START WITH clause, all rows are considered root rows. Here is exactly the case , we are considering all rows and their Mangers
• Where   CONNECT_BY_ROOT t.ename != t.ename, therefore , employee will consider itself in the salary computation.
• If you find that the With Clause is not applicable in your situation, Just replace it with an Inline View in the from Clause.

Identifying Cycles and Ignoring it

When a node's child is also its parent , then their is a cycle in the Hierarchy.

• To Ignore Cycle you can use the CONNECT BY NOCYCLE  clause.
• To identify a Cycle you can use CONNECT_BY_ISCYCLE 
• pseudocolumn returns 1 if the current row has a child that is also its ancestor; otherwise, it returns 0
• can be used only in conjunction with the CONNECT BY NOCYCLE  clause 

Read More

How To Treat The NULL Vacuum

It make some sense as the vacuum is something devoid of matter, something we do not really know about its contents, As does the NULL in a database.

Here are a few points that need consideration as NULL plays the role of a double-edged sword in a database, providing a very efficient way to store unknown values , however not treated wisely could have a nastier effect especially, during data manipulation:

• The Term null value is inaccurate as null indicates the lack of a value.
• A null is as being a marker for a missing value that might never be known, might be determined later, or is not applicable. 
• Nulls can be stored in columns of any data type that are not restricted by NOT NULL or PRIMARY KEY
• Nulls aren't equal (or unequal) to each other. You can't determine whether a null matches any other value, including another null, so the expressions NULL=any_value, NULL<>any_value, NULL=NULL, and NULL<>NULL are neither true nor false but unknown;
• Use IS [NOT] NULL to detect a null;
• Oracle treats an empty string ('') as a null. 

Select DECODE('',NULL, 'TRUE', 'FALSE') IS_NULL
From dual

With test as (Select '' as col from dual)
Select 'TRUE' IS_NULL
From test t
Where t.col is NULL

• DISTINCT Treats all the nulls in a particular column as duplicates
• For Sorting you can specify Order by col [NULLS (FIRST|LAST)]. If you don not specify and Sort by a column that contains nulls, the nulls will be either NULLS LAST if ASC , also remember ASC is the Default if not specified, and NULL FIRST if desc
• Any computations involving a Null can result in Null

Select 12+3+NULL Sum
from dual

so better to use always

Select 12+3+NVL(NULL,0) Sum
from dual

• Aggregate functions, such as COUNT(COLUM_HAVING_NULLS),SUM, MAX, MIN, AVG, etc., all ignore NULLs while  COUNT(*) and GROUPING doesn't ignore NULLS 
• If the grouping column in a GROUP BY clause contains nulls, all the nulls are put in a single group
• Nulls affect the results of joins, the Inner Joins and The Left and Right Outer Joins. 
• Nulls can cause problems in subqueries. I  suggest to do some googling on the topic as you can get some weird results just by placing keywords in different positions in your query. For example,
  WHERE NOT col IN (SELECT col FROM table1); can yeild different results from WHERE  col NOT IN (SELECT col FROM table1);  
• http://www.techrepublic.com/article/oracle-tip-understand-how-nulls-affect-in-and-exists/🔗
• In Set Operations, UNION,MINUS,INTERSECT will treat nulls as duplicates, Except for the UNION ALL which treats each null as different.

select 1, NULL from dual 
Union 
select 2, NULL from dual
Union
Select 1, NULL from dual;

select 1, NULL from dual 
Union
select 2, NULL from dual
Minus
Select 1, NULL from dual;

select 1, NULL from dual 
Union 
select 2, NULL from dual
Intersect
Select 1, NULL from dual;

• Sometimes we call the same function twice in our query without any need thereof.Here is a tip when NVL is used in the where clause.

NVL(PRIMARY_QTY,0) > 0

Means 3 Things

1. Nulls  converted to Zero(0) and
2. PRIMARY_QTY is > 0
3. so in the Select we can just use  PRIMARY_QTY no need to use NVL(PRIMARY_QTY,0) PRIMARY_QTY , since it's already filtered out

Some Resources

NULL Related Functions🔗
NULL Related Functions🔗
Tips about Null🔗
Null and Index🔗
Use Index to get Null values🔗

Read More

Hard-coding vs. Soft-coding in PL/SQL and SQL

Thanksto Steven Feuerstein for his awesome work on pl/sql.
This post is mostly summary and compilation with useful links at the end.
All the files relating to hard-coding can be downloaded from here.🔗

Hard-coding

Hard- coding, usually involves using Literal values, would be fine if nothing ever changed in our applications, only and only if:

• – Requirements, Tables' Definitions, Rules & Formulas and Configuration Constants  stayed the same...

Whenever anything changes, you have to find all the places you explicitly coded it, and fix them.

Soft Coding

– Rather than explicitly code values, rules and algorithms, make them "soft" or dynamic –changeable and settable at runtime.
and here is my definition:

Soft coding involves a way to code the definition , values, rules and algorithms which incorporate such a level of flexibility and manageability that any foreseeable technical and business requirements could be met by it ,without any significant change in the original code.

The concept is

• Repeat nothing: become allergic to redundant repetition.
• Aim for a "single point of definition"(SPOD) in everything you write and thus a great tool for refactoring.
• Information hiding(Data Encapsulation) – Hide, hide, hide: values, implementations, workarounds
  • – Avoid exposing the implementation details of formulas,rules, algorithms, data access.
  • – The more you hide, the more flexibility you have.

• "Never" and "Always" in software
  • – It's never going to stay the same.
  • – It's always going to change.

 

So Lets take quick look on the various hard-coding and its solutions.

Literal values

• – Especially language-specific literals, Limits, Statuses,Flags etc. should only be in SPOD
• Code files:
• thisuser*.*
• soft_code_literals.sql

Solution

• :Hide literals behind:

• Create a Packaged Constant, assigning the value to that constant.
  • Fast on Retrieval
  • Some time the additional typing need by placing package name as the prefix, however this can be avoided if the constants are used within the package and thus can be defined in the package body and referenced by the constant name directly
• Create a Function that hides and returns the value.
  • Pros are
    • Encapsulation Hides the value in the Package body
    • and dynamic, easy to refactor
  • Cons are Performance Overhead
    • The function can be deterministic as the value is predetermined(never changing) and there are no external dependencies in the function , and which can add to optimized performance
  • Still more expensive to call a function than to reference a constant
• Soft code in tables , Store and manage the value in a database table , soft code it.
  • – The Most Dynamic
  • – More complex code
  • – More overhead, but caching can avoid this problem.

Constrained declarations

• – Every declaration of the form VARCHAR2(N) to be a bug. – Unless it is the SPOD for that type.
• Code Files:
• fullname.pks
• plsql_limits.pks
• string_tracker3.*

Solution:

• Instead, anchor the declaration back to its "source".
  • – %TYPE for variables based on columns
  • – %ROWTYPE for records based on table/cursor
  • –  OR if you can't anchor back to a DB element? use  SUBTYPES for all applications-specific types
    • Critical when working with complex structures like collections of records, and nested collections.
    • Example of the hardcoded values
      l_full_name VARCHAR2(100);
      l_big_string VARCHAR2(32767);
    • And using subtype
      CREATE OR REPLACE PACKAGE employee_rp As
      SUBTYPE fullname_t IS VARCHAR2 (1000);
      
      Then in your code use
      l_full_name employees_rp.full_name_t;
      l_big_string plsql_limits.maxvarchar2;

Rules and formulas

• – Especially the "trivial" ones, more critical than repeated literals
• – They will change & get more complex.
• Code Files:
• get_time.sql
• sf_timer.*

Solution

• – Learn to recognize rules and formulas. Often they are missed, especially when simple.
• – Hide the logic behind functions.
• Example: Never trust a rule!
  • DBMS_UTILITY.GET_TIME andGET_CPU_TIME (10g) use to compute elapsed time down to the hundredth of a second

SQL statements

• - Every SQL statement that you write is a hard-coding! difficult to contemplate
• – PL/SQL is, in fact, the best place for SQL.
• – Follow the principles; they are your guide.
• – Don't repeat anything!

Solution

• :hide SQL inside a data access layer.
  • Think of SQL as a service that is provided to you, not something you write.
  • – Or if you write it, you put it somewhere so that it can be easily found, reused, and maintained.
  • This service consists of programs defined in the data access layer.Known as
    • –  table APIs,
    • –  transaction APIs,
    • –  or data encapsulation
  • these programs contain all the intelligence about business transactions and underlying tables.
  • Pros:
    • Change/improve my implementation with minimal impact on my application code.
      • – The underlying data model is constantly changing.
      • – We can depend on Oracle to add new features.
      • – We learn new ways to take advantage of PL/SQL.
    • Vastly improve my SQL-related error handling.
      • – Do you handle dup_val_on_index for INSERTs,too_many_rows for SELECT INTOs, etc?
    • Greatly increase my productivity
      • – I want to spend as much time as possible implementing business requirements.
  • Cons: checkout the stackoverflow Tapi Vs Xapi discussion in the links at the end 
  • For each table, we have these generated packages:
    • – <table>_CP for DML
    • – <table>_QP for queries
    • – <table>_TP for types
    • And the Coded – <table>_XP "extra stuff" package with custom SQL logic and related code
  • It must be very consistent, well-designed and efficient or it will not be used.
  • Best solution: generate as much of the code as possible.
    • – And any custom SQL statements should be written once and placed in a standard container (usually a package).

Hide Algorithmic details

• Assume that whatever you are working on will change – and hide it behind an API.
• – Logging errors
• – Function result cache
• – Manipulating collection contents
• Code Files:
• 11g_emplu.pkg
• string_tracker3.*
• cc_smartargs.pkb
• – Example: error logging mechanism
  • We usually, but not always, want to write error information out to a log table.

WHEN NO_DATA_FOUND THEN
l_code := SQLCODE;
INSERT INTO errlog
VALUES ( l_code, 'No company for id ' || TO_CHAR ( v_id ), 'fixdebt', SYSDATE, USER );
WHEN OTHERS THEN
l_code := SQLCODE; l_errm := SQLERRM;
INSERT INTO errlog
VALUES (l_code, l_errm, 'fixdebt', SYSDATE, USER );
RAISE;
END;

• Now using a shared, generic logging utility , we re-write based on the Quest Error Manager, so You do less work and get more information
• Download form Here: Quest Error Manager

WHEN NO_DATA_FOUND
THEN
q$error_manager.register_error (text_in => 'No company for id ' || TO_CHAR ( v_id ));
WHEN OTHERS
THEN
q$error_manager.raise_unanticipated (name1_in => 'COMPANY_ID', value1_in => v_id);
END;

• The Oracle11g function result cache
  • The optimal way to query and cache data changes over time.
    • – Explicit and implicit cursors
    • – FORALL and BULK COLLECT
    • – And now the function result cache
  • With the Result Cache,provided the same(matching) inputs as parameters the  function return the data previously cached with  

FUNCTION onerow (employee_id_in IN employees.employee_id%TYPE)
RETURN employees%ROWTYPE RESULT_CACHE;

• Manipulating collection contents
  • Collections are Oracle's version of arrays in PL/SQL.
  • – A relatively complicated but critically important data type.
  • Best to hide collection references behind procedures and functions.
  • – Especially when you work with string-indexed and multi-level collections.

Further Reading

PDFs

Say-Goodbye-to-Hard_2D00_Coding.pdf
Hardcoding 1
Hard Coding 2

Hardcoding

YouTube -Hard Coding Playlist
https://www.toadworld.com/platforms/oracle/b/weblog/archive/2011/09/15/avoiding-hard-coded-values-in-views
https://jeffkemponoracle.com/2010/03/02/please-hard-code-your-literals/
http://pretius.com/how-to-avoid-hard-coding-in-your-plsql-code/

SQL hardcoding , TAPIs, XAPIs & Data Access Layer

https://jeffkemponoracle.com/2010/06/23/tapi-vs-xapi/
https://stackoverflow.com/questions/3092041/understanding-the-differences-between-table-and-transaction-apis?answertab=active#tab-top

Read More