How do I use the COLUMN_VALUE pseudocolumn? - PolarDB - Alibaba Cloud Documentation Center

You can use the COLUMN_VALUE pseudocolumn in a TABLE expression to retrieve data from a collection such as a nested table. A virtual table that has a single column named COLUMN_VALUE is returned, which contains the elements of the collection.

Usage

You can use the COLUMN_VALUE pseudocolumn in various clauses in a TABLE expression. The clauses include SELECT LIST, WHERE, HAVING, ORDER BY, and GROUP BY.

Examples

Create a nested table collection named user_names. Use the COLUMN_VALUE pseudocolumn in a TABLE expression to retrieve all user names in the user_names collection.

CREATE TYPE user_names IS TABLE OF varchar2(100);
SELECT column_value FROM TABLE(user_names('Alice', 'Bob', 'Charlie'));

Sample result:

 column_value 
--------------
 Alice
 Bob
 Charlie
(3 rows)

Use the COLUMN_VALUE pseudocolumn in the SELECT LIST, WHERE, HAVING, ORDER BY, and GROUP BY clauses to retrieve specific data in the user_names collection.

CREATE TYPE user_names IS TABLE OF varchar2(100);
SELECT column_value FROM TABLE(user_names('Alice', 'Bob', 'Charlie', 'Alice', 'Alice', 'Bob', 'Charlie')) WHERE column_value = 'Alice';

Sample result:

 column_value
--------------
 Alice
 Alice
 Alice
(3 rows)

SELECT column_value FROM TABLE(user_names('Alice', 'Bob', 'Charlie', 'Alice', 'Alice', 'Bob', 'Charlie')) GROUP BY column_value HAVING column_value = 'Alice' ORDER BY column_value;

Sample result:

 column_value
--------------
 Alice
(1 row)