Views in Teradata

 

Views

• There are windows to tables, A view nothing but a logical collection of an attribute from the 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 on the table etc.
• Based on the functionality, there are 3 types of views

1. Read-only view

2. Updatable view

3. 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 storage for view and it stores the calculated data.
• This storage and calculate data are useful to retrieve the data FASTER
• Generally, we go for a materialized view at the time of frequent analytical and AGGREGATE operations.

Inclined to build a profession as Teradata Developer? Then here is the blog post on, explore Teradata Training

Materialized View & Updatable view

If you create a view based on a single table, then it will be an 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 income from the 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 joining 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 joining 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

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 via the view if the resulting rows fall outside the view

Create view dept – budget as

Select party id party name from the 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.

Teradata Interview Questions

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 the 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

The view contains only one select statement, view returns the result set

Difference  MACROS And PROCEDURES

Macros	Procedures
Collection of statements for implementing simple TASKS	Precompiled collection of statements for implementing compile TASKS Ex: 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 IN: input value to the procedure Out: out value from the procedure 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 bandwidth
We can not execute procedure control statement [loops, If, while…]	Supports
It supports a maximum of one DDL statement	Procedure no limit
This is a reference by using “exec” (or) “execute” command	These are reference by using call common

For In-depth knowledge on Teradata click on:

• BTEQ Features in Teradata
• Secondary Index in TeraData
• Memory Management in TeraData
• Types of OLAP’s in TeraData