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
Object Type: The object type is a user-defined composite datatype which encapsulates a data structure along with the functions and procedures required to manipulate the data

Query: SELECT * FROM EMPLOYEE1; Select 5 columns and all rows from one table Query: SELECT C_ID, COMPANY, BUILDING, DEPARTMENT, BRANCH FROM CONTRACT;

%ISOPEN The %ISOPEN yields TRUE if its cursor or cursor variable is open; or else, the %ISOPEN yields FALSE. In the illustration, you use the %ISOPEN to select an action:

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

%NOTFOUND The %NOTFOUND is the logical opposite of the %FOUND. The %NOTFOUND yields TRUE when an INSERT, UPDATE, or DELETE statement affected no rows, or the SELECT INTO state

Conditionals - SQL At first sight SQL does not appear to have a single operator for expressing logical implication. In this respect it would be in common with most programming

Write SQL queries to solve the following specifications. Include the query AND THE OUTPUT.  A screen dump of the output is acceptable. Show as many rows as you can. A screen dump i

Parameter and Keyword Description: type_name: This identifies a user-defined type specifier that is used in the subsequent declarations of the objects. AUTHID Clause:

Overloading The PL/SQL overloads the subprogram names. That is, you can use similar name for few different subprograms as long as their formal parameters differ in the number

SQLs counterpart of the key words: The text from the opening parenthesis to the end of the fourth line specifies the declared type of the table, meaning that every table ever