Extension and and in sql, PL-SQL Programming

Extension and AND in SQL

The theory book gives the following simple example of relational extension in Tutorial D:

EXTEND IS_CALLED ADD ( FirstLetter ( Name ) AS Initial )

Assuming the user-defined operator FirstLetter is available to the SQL user, this can be expressed easily in SQL but there is a strange quirk in the grammar at play here:

SELECT IC.*, FirstLetter ( Name ) AS Initial

FROM IS_CALLED AS IC

Note very carefully that we have to qualify the * using the range variable, IC, which in this case ranges over the rows of the current value of IS_CALLED. When we use a SELECT clause to "add columns" to the table on which it operates, it seems obvious to write * to specify that every column of that operand table is required and to follow it with a commalist of expressions denoting the additional columns. For some reason the official SQL grammar does not allow additional columns to accompany an unadorned *. When the FROM clause contains several entries, pure extension becomes more difficult to express in SQL. For example, if the FROM clause defines range variables T1, T2, and T3, we could write SELECT T1.*, T2.*, T3.* ..., but that would defeat the purpose. To be able to write just a single * to denote all the columns of the FROM table, we would have to resort to something like

SELECT T.*, ...

FROM (SELECT * FROM T1, T2, T3 WHERE ... ) AS T

Posted Date: 1/18/2013 5:25:56 AM | Location : United States







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

Write discussion on Extension and and in sql
Your posts are moderated
Related Questions
Complete the following steps to create a procedure to calculate the tax on an order. The BB_TAX table contains the states that require taxes to be submitted for Internet sales. If

Effect of Anonymous Columns Now, recall that a VALUES expression denotes a table with undefined column names. If an initial value is to be specified when a base table is creat

Keyword and Parameter Description: label_name: This is an undeclared identifier which optionally labels the PL/SQL block. When used, label_name should be enclosed by the do

Bulk Binds advantages In the Embedded Oracle RDBMS, the PL/SQL engines accept any valid PL/SQL subprogram or block. As the figure shows, the PL/SQL engine executes all procedur

Use Native Dynamic SQL A few programs (a normal-purpose report writer for illustration) should build and process a variety of SQL statements at run time. Therefore, their full

Substitution and Instantiation - SQL It shows how NULL might appear in substitution for a parameter of a predicate and how it might thus participate in instantiation of that p

3CX PBX sync Windows Service Project Description: !! You require access to a commercial version of the 3CX PBX system in order to be able to program the API !! !! You requ

Declaring Cursor Variables Once a REF CURSOR type is define by you, and then you can declare the cursor variables of that type in any PL/SQL block or subprogram. In the exampl

Ensuring Backward Compatibility   The PL/SQL Version 2 permits some abnormal behavior which Version 8 disallows. Particularly, Version 2 permits you to (i) Make the forw

heap sort program in pl/sql