Pseudocolumns

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

Thumbnail image for JSON_TABLE Options: Error Handling, Nested Path

JSON_TABLE Options: Error Handling, Nested Path

Options that can be used with JSON_TABLE query operator to select JSON data as relational rows and columns: ERROR ON ERROR, FOR ORDINALITY, NESTED PATH etc.

Read the Full Article →
Hierarchical Data in a Relational Table

Flatten Hierarchical Data using a Single SQL

Single Oracle SQL to find and flatten hierarchical data stored in a table, using a combination of SYS_CONNECT_BY_PATH and regular expressions.

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

The Magic Of ROWNUM

Pseudocolumn ROWNUM in Oracle, which assigns a number to every row returned by a query, has properties so interesting it almost looks like magic!

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: Talking of “even or odd rows” is meaningless in Oracle until you have ordered the rows.

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.

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. Oracle provides specialized keywords that work with hierarchical queries, such as START WITH…CONNECT BY, PRIOR and SYS_CONNECT_BY_PATH.

Read the Full Article →