Views in Teradata
Views
There are windows to tables, A view nothing but logical collection of attribute from table on tables, we use views mainly for
Taking requires rows and columns from the tables
Reduce net bond width
Providing better binding between table and view data
Providing security one the table etc.
Based on the functionality, there are 3 types of views
Read-only view
Updatable view
Materialize view
Materialize View
This concept not available in Teradata, but we can implements through aggregate, join index (or) Global temporary tables
In this view, it will have a storage for view and it stores the calculate data.
This storage and calculate data are useful to retrieve the data FASTER
Generally, we go for materialized view at the time of frequent analytical and AGGREGATE operations.
Desired to gain proficiency on Teradata? Explore the blog post onTeradata Training in Hyderabad to become a pro in Teradata.
Materialized View & Updatable view
If you create view based on a single table, then it will be updatable view here we can perform INSERT UPDATE And DELETE operations on views so that table will be effected
Create view VW party2 As(Select* From party)
Show view var _party2
Select* From VW-Party2
Replace view vw- Party2 As(Select party id, party name, party in come from party)
update vw- party2 set party income=7777 where party id=1
Read only view(Join view and AGGREGATE View):Here we can perform any operations.
Join Views
Create view vw- party3 As(Select P. party id, t. party name from party p
Left after join test t On p. party id=party id);
Show view vw- party3
Select* from vw- party3
Replacing view vw- party3 As(Select t. party name from party p Left after join test t on p. party id= t. party id);
Aggregate Views
Aggregate Views are Views which contain Aggregate expressions or derived columns it is always necessary to assign a name to there columns so that they may be referenced within the view
An Optional Locking
For Access clause may be added to the query which permits the table row to be read even in the event that another read or write lock is held on the table
Create view party sales as
Locking table party for access
Select party code as department
Sum(party income) As salary- total
Avg(Party income) As salary- Average
max(Party income) As salary- max
min (Party income) As salary- min
From party
Group by party code
Views with Check Option
The with check option prevents rows from being inserted (or) updated viq the view if the resulting rows fall out side the view
Create view dept – budget as
Select party id party name from party
Where party income<=100000 WITH CHEQ option;
Select *from party
Insert into dept – budget(party id, party income)values(603,700000);
*Failure 3564 Range constraint CHECK Error in field party name Department party income, Inserted or updated view this view may not exceed $ 1,00,000.
Learn more about Teradata Interview Questions in this blog post.
Restrictions on Views
An INDEX Cannot be created on a view
A view cannot contain an ORDER BY CLAUSE
Derived and aggregate column must be assigned as name
A view cannot use for update operations if it contains:
Data from more than one table(i.e. Join views)
The same column specified twice
Derived columns(i.e Salary- Account/12)
A DISTINCT CLAUSE
A Group by CLAUSE
Note
View contains only one select statement, view return the result set
Difference MACROS And PROCEDURES
| Macros | Procedures |
| Collection of statements for implementing simple TASKS | Precompiled collection of statements for implementing compile TASKSEx:a. Error Handling b. Working with multiple DML statements c. Knowing memory requirement etc.. |
| In real time for simple and frequent required we go for macro | For rare request and complex operations, we go for in real time |
| Runs at client side | Runs at server side |
| Execution results are cached so it gives faster response | Always runs at server side |
| Takes only i/p parameter | It takes
Inout: taking input value and passing output value |
| All statements inside MARCO executed like a transaction | Not applicable |
| Security less | Security high |
| Network bandwidth less | More band width |
| We can not execute procedure control statement [loops, If, while…] | Supports |
| It supports maximum one DDL statement | Procedure no limit |
| This are reference by using “exec” (or) “execute” command | These are reference by using call comman |
For In-depth knowledge on Teradata click on: