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
This is a Customer Management project. Customer data is presented in a text file. The program will load this text data into its DB columns. The data mapping is user definable. User

Blocks: The fundamental program unit in the PL/SQL is the block. The PL/SQL block is defined by the keywords BEGIN, DECLARE, EXCEPTION, and END. These keywords partition the b

Case Sensitivity Similar to all the identifiers, the variables, the names of constants, and parameters are not case sensitive. For illustration, PL/SQL considers the following n

EXECUTE IMMEDIATE Statement   The EXECUTE IMMEDIATE statement prepare (parses) and instantly executes a dynamic SQL statement or an anonymous PL/SQL block. Syntax:

Using LOCK TABLE You use the LOCK TABLE statement to lock the whole database tables in the specified lock mode so that you can share or deny the access to them. For illustrati

UTL_FILE: The Package UTL_FILE permits your PL/SQL programs to read & write operating system (OS) text files. It gives a restricted version of the standard OS stream file I/O,

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

Positional and Named Notation You can write the actual parameters when calling a subprogram, using either positional or named notation. That is, you can point to the relationsh

Task 2 [12 marks] Write the package body for the following package specification (the detailed description of each function and procedure is provided in the appendix below). Place

Need for Dynamic SQL: You need dynamic SQL in the situations as follows: 1) You would like to execute a SQL data definition statement (like CREATE), a data control statemen