pseudocolumns

Thumbnail image for Flatten Hierarchical Data using a Single SQL

Flatten Hierarchical Data using a Single SQL

Oracle has some neat ways of handling hierarchical data. A while back I wrote about how 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 […]

Read the Full Article →
Thumbnail image for The Magic Of ROWNUM

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 →
Thumbnail image for 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: Even or odd rows are 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 query to […]

Read the Full Article →
Thumbnail image for 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 →
Thumbnail image for Hierarchical Queries: A QuickStart Guide

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 →