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
Pass the nulls to a dynamic SQL: Passing Nulls: Assume that you want to pass the nulls to a dynamic SQL statement. For illustration, you may write the EXECUTE IMMEDIATE

Using ROLLBACK The ROLLBACK statements end the present transaction and undo any change made during the transaction. The Rolling back is helpful for two reasons. Firstly, if yo

Advantages of Packages The benefits of the Packages are as shown below: Modularity The Packages encapsulate logically associated items, types, and subprograms in the

UPDATE Statement   The UPDATE statement transforms the values of the specified columns in one or more rows in the table or view. Syntax:

Packages The package is a schema object which groups logically associated to the PL/SQL items, types, and subprograms. The Packages have 2 sections: the specification & the bod

Understanding Nested Tables Within the database, the nested tables can be considered as one-column database tables. The Oracle stores the rows of a nested table in no specific o

Using NOT NULL Besides assigning an initial value, the declarations can impose the NOT NULL constraint, as the example below shows: acct_id INTEGER(4) NOT NULL := 9999; You ca

Explicit Cursor Attributes The cursor variable or each cursor has four attributes: %FOUND, %ISOPEN, %ROWCOUNT, and %NOTFOUND. When appended to the cursor or cursor variable, th

Using Pragma RESTRICT_REFERENCES: The function called from the SQL statements should obey certain rules meant to control the side effects. To check for violation of the rules,

Initializing Records The illustration below shows that you can initialize a record in its type definition. Whenever you declare a record of the type TimeRec, its 3 fields supp