Hello Readers! I hope my previous articles on Oracle were useful. In this one, I am explaining an Oracle pseudo column. The level is one of the pseudo columns that Oracle adds up with each queried row. It is very useful in some cases, which I am explaining in this article.
- The level is generated automatically during query processing. By default, each generated row has the same level that is 1.
- Although levels for each queried row starts varying when we provide some level criteria.This criteria is given using the ‘CONNECT BY’ clause.
Level Pseudo column in action.
Suppose we have a table, named Employee. Employee table contains the employee’s serial number, employee’s name, and the manager of that particular employee. The MGR column contains the ’empno’ of the employee, under whom the employee works in the organisation.
As we can see, each employee works under another employee except the employee named ‘King’. King works under no one as the MGR entry for ‘King’ is NULL. ‘Ajay’ works under ‘King’ as the Entry of MGR column for Ajay is 3, which is the Employee_number of ‘King’.
Now how can we see what is the hierarchy that each employee follows? Finally, This query shows each employee name and his level in the organisation tree.
SELECT LEVEL,Employee_name from Employee CONNECT BY Mgr = PRIOR Employee_no START WITH Mgr IS NULL;
Code Explanation for querying ‘LEVEL’.
The CONNECT BY clause connects the Mgr and Employee_no column as specified by the PRIOR clause. The START WITH clause specifies where to start processing the Tree, also we used the IS NULL to list up those records as LEVEL 1, which have NULL in the Mgr Entry(as they work under nobody).
This query provides a more visually instructive response, with appended ‘-’.
SELECT LPAD(RPAD(Employee_name,10,' '),LEVEL+10,'-') FROM Employee CONNECT BY Mgr = PRIOR Employee_no START WITH Mgr IS NULL;
Oracle provides several other pseudo columns like ‘RowID’ and ‘Rownum’. Hopefully, I will post their use cases as well. These pseudo columns are hidden by default. But, they are very useful sometimes.
So that is all for pseudo columns. I have posted a JavaFX material design tutorial recently, so check it out. Comments and suggestions are always welcome.