Teradata Learning - VIEWS (02/24/2013)

• Views are used to provide customized access to base tables by


1. Restrict columns from the base table(s) by explicitly listing the desired column names from the base table(s).


2. Restrict rows to be accessed by using the WHERE clause.


• CREATE VIEW statement is used to create view


CREATE VIEW view1
AS
SELECT * from employee_table;


• Join View:


JOIN view consists of columns from more than one table.


CREATE VIEW emp_dtl
AS
SELECT employee_name, dept_name
From employee
JOIN
Department
ON employee.deptno = department.deptno;


• Renaming columns:


While creating views we can rename the column names to provide better names.


CREATE VIEW view1(empname,deptname)
AS
Select name,dname from employee;


• Replacing views:


We can use the REPLACE VIEW command to change the view definition.


REPLACE VIEW emp_dtl
AS
SELECT employee_name, dept_name
From employee
JOIN
Department
ON employee.deptno = department.deptno;


• Formatting and providing titles to columns:


While defining views we can use FORMAT clause to change the way columns values are being displayed.


REPLACE VIEW emp_dtl
AS
SELECT
emp_no (FORMAT 'ZZZZ9.99'),
employee_name (TITLE 'EMPNAME'),
dept_name
From employee
JOIN
Department
ON employee.deptno = department.deptno;




• With  CHECK OPTION


Views can be used for inserting or updating rows in the underlying tables.
WITH CHECK OPTION limits the abilities of users with update and insert privileges on the view.
If a view has a where clause , a user may not insert or update a row if the resulting row will violate the constraints of the WHERE clause.


Example:
CREATE VIEW emp_view
AS
SELECT * FROM employee
Where salary < 10000
WITH CHECK OPTION;


With the above CHECK OPTION in place we cannot insert any row in the table with salary > 10000
Or we cant update any row to have salary > 10000.


INSERT INTO emp_view VALUES ('James','Bond',20000);


Above insert query will fail with below error


 **** Failure 3564 Range constraint:






Rules for using VIEWS:


1. A View cannot contain an ORDER BY clause.


2. Derived and aggregated columns must be assigned a name using


• NAMED clause
• AS clause
• Creating a list of assigned column names in the CREATE VIEW definition.


3. A view cannot be used to UPDATE if it contains


• A data is selected from one or more tables using JOINS or UNIONS.
• If same column is select twice.
• If columns are derived.( calculated)
• If the select query contains DISTINCT clause.
• If select query contains GROUP BY clause.