Examples Of Creating Views

Examples of Creating Views

Creating view returning the JOB_CODE and JOB_TITLE columns only for those jobs where MAX_SALARY is less than $15,000.
```
CREATE VIEW ENTRY_LEVEL_JOBS AS
SELECT JOB_CODE, JOB_TITLE
FROM JOB
WHERE MAX_SALARY < 15000;
```
Creating a view returning the JOB_CODE and JOB_TITLE columns only for those jobs where MAX_SALARY is less than $15,000.Whenever a new record is inserted or an existing record is updated, the MAX_SALARY < 15000 condition will be checked.If the condition is not true, the insert/update operation will be rejected.
```
CREATE VIEW ENTRY_LEVEL_JOBS AS
SELECT JOB_CODE, JOB_TITLE
FROM JOB
WHERE MAX_SALARY < 15000
WITH CHECK OPTION;
```

Creating a view with an explicit column list.

CREATE VIEW PRICE_WITH_MARKUP (
  CODE_PRICE,
  COST,
  COST_WITH_MARKUP
) AS
SELECT
  CODE_PRICE,
  COST,
  COST * 1.1
FROM PRICE;

Creating a view with the help of aliases for fields in the SELECT statement (the same result as in Example 3).
```
CREATE VIEW PRICE_WITH_MARKUP AS
SELECT
  CODE_PRICE,
  COST,
  COST * 1.1 AS COST_WITH_MARKUP
FROM PRICE;
```

Creating a read-only view based on two tables and a stored procedure.

CREATE VIEW GOODS_PRICE AS
SELECT
  goods.name AS goodsname,
  price.cost AS cost,
  b.quantity AS quantity
FROM
  goods
  JOIN price ON goods.code_goods = price.code_goods
  LEFT JOIN sp_get_balance(goods.code_goods) b ON 1 = 1;

`ALTER VIEW`

Alters a view

Available in

DSQL

Syntax

ALTER VIEW viewname [<full_column_list>]
    AS <select_statement>
    [WITH CHECK OPTION]

<full_column_list> ::= (colname [, colname ...])

Table 1. `ALTER VIEW` Statement Parameters
Parameter	Description
viewname	Name of an existing view
select_statement	SELECT statement
full_column_list	The list of columns in the view
colname	View column name.Duplicate column names are not allowed.

Use the ALTER VIEW statement for changing the definition of an existing view.Privileges for views remain intact and dependencies are not affected.

The syntax of the ALTER VIEW statement corresponds with that of CREATE VIEW.

Caution

Be careful when you change the number of columns in a view.Existing application code and PSQL modules that access the view may become invalid.For information on how to detect this kind of problem in stored procedures and trigger, see The RDB$VALID_BLR Field in the Appendix.