Unnest operator in sql, PL-SQL Programming

UNNEST operator in SQL

The inverse operator of GROUP is UNGROUP. SQL has an operator, UNNEST, that can be used for similar purposes, but its method of invocation is somewhat peculiar, as Example shows, and it can be used only to specify a FROM clause element.

Example: Inverse operator of GROUP by using UNNEST

SELECT DISTINCT * FROM C_ER2, UNNEST ( ExamResult ) AS M

The name C_ER2 could be defined using a WITH clause. Notice how the second element of the FROM clause has to be reevaluated for each row of C_ER2, whereas each FROM clause element is normally evaluated just once because its value does not vary from row to row of previous elements. The column reference ExamResult is a reference to the column of that name in C_ER2 and is permitted only because C_ER2 is specified before UNNEST (ExamResult) in the FROM clause-a switching of these two FROM clause elements would result in a syntax error.

Posted Date: 1/18/2013 6:44:04 AM | Location : United States







Related Discussions:- Unnest operator in sql, Assignment Help, Ask Question on Unnest operator in sql, Get Answer, Expert's Help, Unnest operator in sql Discussions

Write discussion on Unnest operator in sql
Your posts are moderated
Related Questions
Create a Oracle procedure to produce vertical output format when selecting rows from a database table.

Using %ROWTYPE The %ROWTYPE attribute gives a record type which represents a row in a table (or view). The record can store the whole row of data selected from the table or fetc

Table Literals - SQL One might expect SQL to support table literals in the manner illustrated in Example 2.2, but in fact that is not a legal SQL expression. Example: Not a

DELETE Statement The DELETE statement eliminates whole rows of data from the specified table or view. Syntax:

Assigning and Comparing Collections One collection can be assigned to other by an SELECT, INSERT, UPDATE, or FETCH statement, an assignment statement, or by a subprogram call. A

SQL Operators The PL/SQL uses all the SQL set, comparison, and row operators in the SQL statements. This part briefly describes some of these operators.  1. Comparison Opera

How Transactions Guard Your Database The transaction is a sequence of SQL data manipulation statements which does a logical unit of work. The Oracle treats the sequence of SQL

At times, customers make mistakes in submitting their orders and call to cancel the order. Brewbean’s wants to create a trigger that automatically updates the stock level of all pr

Data Types in SQL - Interval, Boolean INTERVAL for values denoting, not intervals (!) but durations in time, such as 5 years, 3 days, 2 minutes, and so on. BOOLEAN, con

Consider the following set of database tables (same tables from Assignment 6-1). Please take note of foreign keys (most of them carry the same names as the corresponding primary ke