AntDB-Oracle Compatibility Developer's Manual P2–2
Published: 2023-08-29 06:20:51

#### ROWNUM pseudo-column

ROWNUM is a pseudo-column that is used to assign an incremental, unique integer value to each row based on the order in which the rows are retrieved from the query. Thus ROWNUM is assigned in the order of 1 for the first fetched record and 2 for the second fetched record.

We can use this feature to limit the total number of rows fetched in a query. This feature is demonstrated in the following example.

```
SELECT empno, ename, job FROM emp WHERE ROWNUM < 5;

 EMPNO | ENAME |   JOB    
-------+-------+----------
  7369 | SMITH | CLERK
  7499 | ALLEN | SALESMAN
  7521 | WARD  | SALESMAN
  7566 | JONES | MANAGER
(4 rows)
```

The value of ROWNUM is assigned to each row in the result set before the sorting operation of the result set occurs. Thus, the result set is returned in the order specified by the ORDER BY clause. But as you can see in the following example, the ROWNUM value is not required in the output of an ascending sort.

```
SELECT ROWNUM, empno, ename, job FROM emp WHERE ROWNUM < 5 ORDER BY ename;

 ROWNUM |  EMPNO  | ENAME |   JOB    
--------+---------+-------+----------
      1 |    7876 | ADAMS | CLERK
      2 |    7499 | ALLEN | SALESMAN
      3 |    7698 | BLAKE | MANAGER
      4 |    7782 | CLARK | MANAGER
(4 rows)
```

The following example shows how to add a sequence number for each record in the jobhist table. First add a column with the name seqno to the table, and then set ROWNUM to seqno in the UPDATE command. This way we add a column with a sequence number to the jobhist table.

```
ALTER TABLE jobhist ADD column seqno NUMBER(3);
UPDATE jobhist SET seqno = ROWNUM;
```

The following SELECT command shows the new seqno value.

```
SELECT seqno, empno, TO_CHAR(startdate,'DD-MON-YY') AS start, job FROM jobhist;

 SEQNO | EMPNO |   START   |    JOB    
-------+-------+-----------+-------------------
     1 |  7369 | 17-DEC-80 | CLERK
     2 |  7499 | 20-FEB-81 | SALESMAN
     3 |  7521 | 22-FEB-81 | SALESMAN
     4 |  7566 | 02-APR-81 | MANAGER
     5 |  7654 | 28-SEP-81 | SALESMAN
     6 |  7698 | 01-MAY-81 | MANAGER
     7 |  7782 | 09-JUN-81 | MANAGER
     8 |  7788 | 19-APR-87 | CLERK
     9 |  7788 | 13-APR-88 | CLERK
    10 |  7788 | 05-MAY-90 | ANALYST
    11 |  7839 | 17-NOV-81 | PRESIDENT
    12 |  7844 | 08-SEP-81 | SALESMAN
    13 |  7876 | 23-MAY-87 | CLERK
    14 |  7900 | 03-DEC-81 | CLERK
    15 |  7900 | 15-JAN-83 | CLERK
    16 |  7902 | 03-DEC-81 | ANALYST
    17 |  7934 | 23-JAN-82 | CLERK
(17 rows)
```

#### Hierarchical Queries

A hierarchical query is a specific type of query used to return rows in a result set in hierarchical order in data based on forming parent-child relationships. A hierarchy is usually represented by a tree structure in reverse order. The tree consists of interconnected nodes. Each node may be connected to 0, 1, or even multiple child nodes. Each node is connected to a parent node, except for the topmost root node which has no parent. Each tree has only one root node. And a node without any children is called a leaf node. Trees have at least one leaf node - for example, in the smallest tree is composed of a single node, in which case this node is both the root and the leaf node.

In a hierarchical query, the rows in the result set represent nodes in one or more trees.

Note: If a row appears in more than one tree, this row will appear multiple times in the result set.

The hierarchical relationship in a query is described by the CONNECT BY clause, where the CONNECT BY clause determines the order in which the rows are returned in the result set. The following shows the context in which the CONNECT BY clause is used in the SELECT command and the associated optional clauses.

> SELECT select\_list FROM table\_expression \[ WHERE ...]
> 
> \[ START WITH start\_expression ]
> 
> CONNECT BY { PRIOR parent\_expr = child\_expr \|
> 
> child\_expr = PRIOR parent\_expr }
> 
> \[ ORDER SIBLINGS BY column1 \[ ASC \| DESC ]
> 
> \[, column2 \[ ASC \| DESC ] ] ...
> 
> \[ GROUP BY ...]
> 
> \[ HAVING ...]
> 
> \[ other ...]

select\_list is one or more expressions, including the fields of the result set. table\_expression is one or more tables or views that are the source of the fetched records. other is any additional legal SELECT clause. The clauses related to hierarchical queries such as START WITH, CONNECT BY and ORDER SIBLINGS BY are introduced in later sections.

##### Define the parent/child relationship of a record

For any given record, its parent and child nodes are determined by the CONNECT BY clause. The CONNECT BY clause must consist of 2 expressions, compared with an equal sign. In addition to this, both expressions must be prefixed with the keyword PRIOR.

The steps to determine the child nodes of a record are as follows.

1. Compute the parent\_expr on the record.

2. Compute child\_expr on the other records obtained from the computed table\_expression and then on those records.

3. If parent\_expr is equal to child\_expr, then this record is a child node of the record.

4. Repeat the above procedure for the remaining records in table\_expression. All records that satisfy the equation relationship in step 3 are children of the record.

Note: The computation process to determine whether a record is a child node occurs on each record returned by table\_expression before the WHERE clause is applied to table\_expression.

By iteratively performing this process iteratively one by one, treating the child nodes found in the previous steps as parents, we construct an upside-down tree of nodes. The process ends when the final set of child nodes have no children of their own - that is, those leaf nodes. A SELECT command with a CONNECT BY clause usually includes a START WITH clause. The START WITH clause is used to determine the record to be used as the root node. For example, the record that is initialized as the parent node depends on the application of the algorithm introduced earlier. This clause will be explained in detail in the next section.

##### Select the root node

The START WITH clause is used to identify those rows that are queried by table\_expression as the root node. All rows selected by table\_expression become the root nodes of the tree, where start\_expression is calculated as "true". Therefore, the number of potential trees in the result set is equal to the number of root nodes. Therefore, if the START WITH clause is not used, then each record returned by table\_expression is the root of its own tree.

##### The organization of the tree in the sample program

Consider the emp table in the example program. The records in table emp form a hierarchy based on the mgr column containing the employee number of the supervisor manager. Each employee has at most one manager. KING is the president of the company and he does not have a supervisory manager. As a result, the mgr column with the record KING is empty. At the same time, there is a possibility that an employee can be a manager of more than one employee. As shown below, this relationship forms a typical tree structure diagram.

![image-20201029103213511](../image/oracle_compatibal2.jpg)

To form a hierarchical query based on this relationship, the SELECT command must include the clause CONNECT BY PRIOR empno = mgr. For example, for the company's chairman, employee number 7839, KING, any employee whose mgr column is 7839 reports directly to KING. (This includes JONES, BLAKE, and CLARK, which are all children of KING). Similarly, for employee JONES, any other employee with mgr column 7566 is a child of JONES - including SCOTT and FORD in this example.

The top level of the organization chart is KING, so there is only one root node in this tree. The START WITH mgr IS NULL clause selects only KING as the initial root node.

The complete SELECT command is displayed as follows.

```
SELECT ename, empno, mgr
FROM emp
START WITH mgr IS NULL
CONNECT BY PRIOR empno = mgr;
```

The query output records traversing each branch from the root node to the leaf node in top-to-bottom, left-to-right order. The following is the output of this query.The following is the output of this query.

```
 ENAME  | EMPNO | MGR  
--------+-------+------
 KING   |  7839 |     
 CLARK  |  7782 | 7839
 BLAKE  |  7698 | 7839
 JONES  |  7566 | 7839
 MILLER |  7934 | 7782
 JAMES  |  7900 | 7698
 TURNER |  7844 | 7698
 MARTIN |  7654 | 7698
 WARD   |  7521 | 7698
 ALLEN  |  7499 | 7698
 FORD   |  7902 | 7566
 SCOTT  |  7788 | 7566
 SMITH  |  7369 | 7902
 ADAMS  |  7876 | 7788
(14 rows)
```