Views in TERADATA

Create Views in Teradata

• Share

Views in Teradata can be easily created or modified. Views are very useful and are used for several purposes. A VIEW is a virtual table and don’t need any physical space to save data. Only VIEW definition is stored in DATA DICTIONARY and every time VIEW is queried ; the SQL statement is executed and result is returned back to the user. VIEWS in Teradata can be created by using following syntax:

CREATE VIEWS IN TERADATA

CREATE VIEW VIEW_NAME (COL1,COL2,COL3) AS SELECT COLUMN1, COLUMN2, COLUMN3 FROM BASE_TABLE;

REPLACE/UPDATE/MODIFY VIEWS IN TERADATA

REPLACE VIEW VIEW_NAME (COL1,COL2,COL3,COL4) AS SELECT COLUMN1, COLUMN2, COLUMN3,COLUMN4 FROM BASE_TABLE;

**Notice there is no column selected as PRIMARY INDEX in VIEW Definition.

ADVANTAGES OF VIEWS IN TERADATA

• Adds extra level of security by avoiding access to base tables.
• Helps in giving more proper column names than the original names present in base tables.
• Allows to limit access to the records present in the base table by the use of WHERE clause.
• Allows to limit access to the columns present in the base table by SELECTing only required columns.
• Combine the result from multiple tables and provide single place to access the records.
• You can use the VIEW to insert or update the data present in the base table.

VIEWS WITH CHECK OPTION

If you are creating a VIEW with CHECK option, then it will not only user to insert or update data in the base table if it violates the data constraints applied in the VIEW definition. Let see the example below:

CREATE VIEW EMP AS SELECT EMP_ID, EMP_NAME, EMP_SAL FROM EMPLOYEE WHERE EMP_SAL>50000 WITH CHECK OPTION;
Now if you try to insert into the base table via this view any data which has EMP_SAL<50000, it will throw the following error:

****Failure 3564 Range Constraint: Check error in field …

- See more at: http://usefulfreetips.com/Teradata-SQL-Tutorial/create-views-in-teradata/#sthash.4RzLJUzk.dpuf