• USA : +1 973 910 5725
  • INDIA: +91 905 291 3388
  • info@tekslate.com
  • Login

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 store 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 on 
Teradata Training in Hyderabad to become a pro in Teradata.

Screenshot_82

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

2Create view VW party2 As(Select* From party)

2Show view var _party2

2Select* From VW-Party2

2Replace view vw- Party2 As(Select party id, party name, party in come from party)

2update vw- party2 set party income=7777 where party id=1

2Read only view(Join view and AGGREGATE View):Here we can perform any operations.

Join Views

2Create 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);

2Show view vw- party3

2Select* from vw- party3

2Replacing 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

2An INDEX Cannot be created on a view

2A view cannot contain an ORDER BY CLAUSE

2Derived and aggregate column must be assigned as name

A view cannot used for update operations if it contains:

2Data from more than one table(i.e Join views)

2The same column specified twice

2Derived columns(i.e  Salary- Account/12)

2A DISTINCT CLAUSE

2A 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

  1. IN: input value to the procedure
  2. 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 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

Screenshot_83

For Indepth knowledge on Teradata click on:

Summary
Review Date
Reviewed Item
Views in Teradata
Author Rating
5

“At TekSlate, we are trying to create high quality tutorials and articles, if you think any information is incorrect or want to add anything to the article, please feel free to get in touch with us at info@tekslate.com, we will update the article in 24 hours.”

0 Responses on Views in Teradata"

Leave a Message

Your email address will not be published. Required fields are marked *

Site Disclaimer, Copyright © 2016 - All Rights Reserved.