Implementing hierarchical queries for recursion support
The hierarchical query is a new type of SQL statement in DB2 9.7, which enables the retrieval of the hierarchical data. One can enable this feature by setting the DB2_COMPATIBILITY_VECTOR registry variable to ORA.
We can build the hierarchical queries by using the CONNECT BY clause, pseudo columns such as LEVEL, unary operators such as CONNECT_BY_ROOT and PRIOR, and using a new scalar function, SYS_CONNECT_BY_PATH.
Getting ready
We will run though CONNECT BY, LEVEL, CONNECT_BY_ROOT, and PRIOR to see how easy it is to get the hierarchical data in DB 9.7
How to do it...
If a database table contains hierarchical data, then we can use hierarchical queries to extract the data in a hierarchical order. Let's go through an example, and understand how to use it.
1. In most of the organizations, we will have hierarchical reporting structure. Let's create a table to capture all the employee information, and key in the employee details for analysis...
