Creating Views - Firebird

The DDL statement for defining the query specification that will be transformed into a view object is CREATE VIEW. Although it defines a table (albeit a virtual one) and optionally allows custom names to be declared for columns, the syntax does not include any data definitions for the columns. Its structure is formed around the column list of a SELECT statement and the tables specified in the FROM and, optionally, JOIN clauses of the statement.

All styles of joined and union sets that are supported in queries are supported for view definitions. However, it is not possible to define a view that is derived from the output set of a stored procedure and it is not legal to include an ORDER BY clause.

The CREATE VIEW Statement

The syntax pattern for CREATE VIEW is

CREATE VIEW view-name [(view-column-name [, view-column-name [,...]])] AS <select-specification> [WITH CHECK OPTION];

View Name

The view name uniquely identifies the view as an object in the database. The name cannot be the same as the name of any other view, table, or stored procedure.

Specifying View Column Names

Specifying the list of column names for the view is optional if there are no duplicate names in the column list. The names of the underlying columns will be used by default.

In the case where a join would result in duplicates, it becomes mandatory to use a list and rename one of the columns.

This rather ugly example demonstrates how duplication of column names could occur:

CREATE VIEW VJOB_LISTING AS SELECT E.*, J.JOB_CODE, J.JOB_TITLE FROM EMPLOYEE E JOIN JOB J ON E.JOB_CODE = J.JOB_CODE ; ISC ERROR CODE:335544351 unsuccessful metadata update STORE RDB$RELATION_FIELDS failed attempt to store duplicate value (visible to active transactions) in unique index "RDB$INDEX_15"

Index RDB$INDEX_15 is a \ \unique index on the relation name and the field name. The JOB_CODE column from \ the EMPLOYEE table was already stored for VJOB_LISTING, hence the exception.

It is necessary to name \ all of the columns in this view:

CREATE VIEW VJOB_LISTING ( EMP_NO, FIRST_NAME, LAST_NAME, PHONE_EXT, HIRE_DATE, DEPT_NO, EMP_JOB_CODE, /* alternative name */ JOB_GRADE, JOB_COUNTRY, SALARY, FULL_NAME, JOB_JOB_CODE, /* alternative name */ JOB_TITLE) AS SELECT E.*, J.JOB_CODE, J.JOB_TITLE FROM EMPLOYEE E JOIN JOB J ON E.JOB_CODE = J.JOB_CODE ;

A list is mandatory also if the column list contains any fields created from expressions. For example, this fails:

CREATE VIEW VJOB_ALTNAMES AS SELECT JOB_CODE || 'for ' || JOB_TITLE AS ALTNAME FROM JOB;

ISC ERROR CODE:335544569

Invalid command must specify column name for view select expression

This succeeds:

CREATE VIEW VJOB_ALTNAMES (ALTNAME) AS SELECT JOB_CODE || ' for ' || JOB_TITLE FROM JOB;

The view’s column name list specification must correspond in order and number to the columns listed in the SELECT statement.

The SELECT Specification

The SELECT specification is an ordinary SELECT statement that can incorporate joins, expression fields, grouping specifications, and search conditions—but not ordering conditions.

The output list in the SELECT clause defines the types, degree, and, unless explicitly specified, the names of the view’s columns.

A SELECT DISTINCT query is valid, if required.

The FROM clause, along with any JOIN clauses or subqueries, defines the base tables of the view.

A WHERE clause can be included if you want to specify search conditions. A valid GROUP BY clause with an optional HAVING clause can also be included.

Defining Computed Columns

The same rules that apply to any expression used for defining a runtime field for a query apply to runtime columns for the view specification. The output is almost parallel to a computed column in a table. However, a computed column has its own, distinct effects in a view:

  • It forces the view column-list to become mandatory.
  • It makes the query non-updatable.

Suppose you want to create a view that assigns a hypothetical 10-percent salary increase to all employees in the company. The next example creates a read-only view that displays all of the employees and their possible new salaries:

CREATE VIEW RAISE_BY_10 (EMPLOYEE, NEW_SALARY) AS SELECT EMP_NO, SALARY * 1.1 FROM EMPLOYEE;

WITH CHECK OPTION

WITH CHECK OPTION is an optional syntax item used only in view specifications. It affects updatable views that have been defined with a WHERE clause. Its effect is to block any update operation that would result in a violation of a search condition in the WHERE clause.

Suppose you create a view that allows access to information about all departments with budgets between $10,000 and $500,000. The view, V_SUB_DEPT, could be defined as follows:

CREATE VIEW V_SUB_DEPT ( DEPT_NAME, DEPT_NO, SUB_DEPT_NO, LOW_BUDGET) AS SELECT DEPARTMENT, DEPT_NO, HEAD_DEPT, BUDGET FROM DEPARTMENT WHERE BUDGET BETWEEN 10000 AND 500000 WITH CHECK OPTION;

A user with INSERT privileges on the view can insert new data into the DEPARTMENT, DEPT_NO, HEAD_DEPT, and BUDGET columns of the base table though this view. WITH CHECK OPTION ensures that all budget values entered through the view fall within the range prescribed by the view.

The following statement inserts a new row for the Publications department through the V_SUB_DEPT view:

INSERT INTO V_SUB_DEPT ( DEPT_NAME, DEPT_NO, SUB_DEPT_NO, LOW_BUDGET) VALUES ('Publications', '999', '670', 250000);

But this statement will fail, because the value of LOW_BUDGET is outside the range prescribed for the target column, BUDGET:

INSERT INTO V_SUB_DEPT ( DEPT_NAME, DEPT_NO, SUB_DEPT_NO, LOW_BUDGET) VALUES ('Publications', '999', '670', 750000); ISC ERROR CODE:335544558 Operation violates CHECK constrainton view or table V_SUB_DEPT

A view WITH CHECK OPTION clause can be useful when you want to provide users with an updatable view, but you want to prevent them from updating certain columns. Just include a search condition for each column you want to protect. The clause’s usefulness is limited somewhat by the fact that a view cannot be defined with replaceable parameters.


All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

Firebird Topics