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
I need to write one function and one procedure to query a Oracle 10.1 DB using PL SQL. I have the schema and exact queries...along with work Ive started and a template to put the a

LOOP Statements The LOOP statements execute a series of statements at multiple times. The loops enclose the series of statements that is to be repeated. The PL/SQL provides typ

Using DELETE This process has three forms. The DELETE removes all elements from the collection. DELETE(n) removes the nth element from the nested table. When n is null, then D

Data Types in SQL - XML, Array, Row BINARY LARGE OBJECT for arbitrarily large bit strings. XML for XML documents and fragments. ARRAY types for arrays.

How Exceptions Propagate ? Whenever an exception is raised, and if the PL/SQL cannot find a handler for it in the present subprogram or block, the exception propagates. That is

Inserting Objects: You can use the INSERT statement to add objects to an object table. In the illustration below, you insert a Person object into the object table persons:

Controlling Autonomous Transactions The first SQL statement in an autonomous routine starts a transaction. Whenever one transaction ends, the next SQL statement starts the oth

Built-In Functions The PL/SQL provides a lot of powerful functions to help you to manipulate the data. These built-in functions fall into the categories as shown below: error r

I need SQL to infopath data connection Project Description: Want data retrieval connection from SQL to SharePoint infopath Skills required are Sharepoint, SQL

IN Mode An IN parameter pass the values to the subprogram being called. Within the subprogram, an IN parameter acts like a constant. And hence, it cannot be assigned a value.