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
Using COMMIT The COMMIT statements end the present transaction and make permanent any changes made during that transaction. Till you commit the changes, other users cannot acc

Restriction in SQL Syntax: SELECT DISTINCT StudentId FROM IS_CALLED WHERE Name = 'Boris' The WHERE clause operates on the result of the FROM clause in analogo

Controlling Cursor Variables You use 3 statements to control the cursor variable: OPEN-FOR, FETCH, & CLOSE. At First, you OPEN a cursor variable FOR a multi-row query. Then, y

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

Example of Using Aggregation on Nested Tables Example: How many students sat each exam WITH C_ER AS (SELECT CourseId, CAST (TABLE (SELECT DISTINCT StudentId, Mark FROM EXAM

Passing Cursor Parameters You use the OPEN statement to pass the parameters to a cursor. Unless you want to accept the default values, each proper parameter in the cursor decl

Creating a Table Syantax: CREATE TABLE ENROLMENT (StudentId SID, Name   VARCHAR (30) NOT NULL, CourseId CID, PRIMARY KEY (StudentId, CourseId)) ; Explan

DBMS_OUTPUT: The Package DBMS_OUTPUT enables you to display output from the PL/SQL subprograms and blocks, that makes it easier to test and debug them. The procedure put_ line

FOR-LOOP While the number of iterations through a WHILE loop is unknown till the loop completes, then the number of iterations through a FOR loop is known before the loop is ent

I want someone to write a TSQL function that returns the name of the ODBC DSN. I will use the queries below, to get information about the connection, but none of these return th