Pseudocolumns

A pseudocolumn behaves like a table column but is not actually one. ROWNUM, SYSDATE, USER are a few examples of pseudocolumns.

Hierarchical Data in a Relational Table

Flatten Hierarchical Data using a Single SQL

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 […]

Read the Full Article →
ROWNUM in Oracle

The Magic Of ROWNUM

The “ROWNUM greater than” query never fails to have an eye-popping effect  the first time anyone sees it. If you haven’t worked with ROWNUM much before, be prepared! First things first. What is ROWNUM? ROWNUM is a pseudocolumn, assigning a number to every row returned by a query. The numbers follow the sequence 1, 2, […]

Read the Full Article →
Selecting ODD or EVEN rows from a table

Selecting ODD or EVEN rows from a table

Question: How can I select only the even/odd rows from an Oracle table? Answer: Talking of “even or odd rows” is meaningless in Oracle until you have ordered the rows. Oracle does not store rows in a specific order – the order has to come from the query. Once the order is specified, then the […]

Read the Full Article →
LEVEL Pseudocolumn in Hierarchical Queries

LEVEL Pseudocolumn in Hierarchical Queries

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:

Read the Full Article →
Hierarchical Queries in Oracle

Hierarchical Queries: A QuickStart Guide

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 […]

Read the Full Article →