Chapter 14 – View Functions
"Be the change that you want to see in the world."
-Mahatma Gandhi
The Fundamentals of Views
View Fundamentals
A view is a virtual table.
A view may define a subset of columns
A view can even define a subset of rows if it has a WHERE clause
A view never duplicates data or stores the data separately
View definitions are stored in the Data Dictionary, not the user’s space Views provide security
View Advantages
An additional level of security is provided.
Helps the business user not miss join conditions.
Help control read and update privileges.
Unaffected when new columns are added to a table.
Unaffected when a column is dropped unless its referenced in the view.
View Recommendations
Only utilities, such as FastLoad, MultiLoad, TPump, and TPT should ever have direct access to tables during the ETL process.
Create one view per base table.
The above is designed to introduce View fundamentals, View advantages and View recommendations.
Creating a Simple View to Restrict Sensitive Columns
CREATE View Employee_V AS
SELECT Employee_No
,First_Name
,Last_Name
,Dept_No
FROM Employee_Table ;
The purposes of views is to restrict access to certain columns, derive columns or Join Tables, and to restrict access to certain rows (if a WHERE clause is used). This view does not allow the user to see the column salary.
Creating a Simple View to Restrict Rows
CREATE VIEW Employee_View
AS
SELECT First_Name
,Last_Name
,Dept_No
,Salary
FROM Employee_Table
WHERE Dept_No IN (300, 400) ;
The purposes of views is to restrict access to certain columns, derive columns or Join Tables, and to restrict access to certain rows (if a WHERE clause is used). This view does not allow the user to see information about rows unless the rows have a Dept_No of either 300 or 400.
Basic Rules for Views
No ORDER BY inside the View CREATE (exceptions exist)
All Aggregation needs to have an ALIAS
Any Derived columns (such as Math) needs an ALIAS
Above are the basic rules of Views with excellent examples.
How to Modify a View
The REPLACE Keyword will allow a user to change a view.
Two Exceptions to the ORDER BY Rule inside a View
There are EXCEPTIONS to the ORDER BY rule. The TOP command allows a view to work with an ORDER BY inside. ANSI OLAP statements also work inside a View.
How to Get HELP with a View
HELP View Employee_V ;
The Help View command do little but show you the columns.
Views sometimes CREATED for Formatting or Row Security
CREATE VIEW empl_200_v AS
SELECT Employee_No AS Emp_No
,Last_Name AS Last
,salary/12 (format '$$$$,$$9.99') AS Mnth_Sal
FROM Employee_Table
WHERE Dept_No = 200 ;
SELECT *
FROM Empl_200_v
ORDER BY Mnth_Sal ;
Views are designed to do many things. In the example above, this view formats and derives data, limits columns, and also limits the rows coming back with a WHERE.
Creating a View to Join Tables Together
This view is designed to join two tables together. By creating a view, we have now made it easier for the user community to join these tables by merely selecting the columns you want from the view. The view exists now in the database sql_views and accesses the tables in sql_class.
You Select From a View
Once the view is created, then users can query them with a SELECT statement. Above, we have queried the view we created to join the employee_table to the department_table (created on previous page). Users can select all columns with an asterisk, or they can choose individual columns (separated by a comma). Above, we selected all columns from the view.
Another Way to Alias Columns in a View CREATE
SELECT *
FROM E_View
ORDER BY Mnth_Sal ;
Will this View CREATE work or will it error? It works fine because it’s aliased above!
The Standard Way Most Aliasing is Done
The ALIAS for Salary / 12 that’ll be used in this example is Sal_Monthly and this form of aliasing is most often used.
What Happens When Both Aliasing Options Are Present
SELECT *
FROM Emp_v3
ORDER BY 3 ;
The ALIAS for Salary / 12 that’ll be used in this example is Mnth_Sal. It came first at the top, even though it is aliased in the SELECT list also.
Resolving Aliasing Problems in a View CREATE
SELECT *
FROM Emp_v3
ORDER BY Sal_Mnth ;
What happens when this query runs?
What will happen in the above query?
Answer to Resolving Aliasing Problems in a View CREATE
SELECT *
FROM Emp_v3
ORDER BY Sal_Mnth ;
What happens when this query runs?
The query above errors because Sal_Mnth is an unrecognized alias. That is because we did our aliasing at the top so this makes the alias right after Salary/12 non-valid for use when querying the view.
Aggregates on View Aggregates
CREATE VIEW Aggreg_Order_v AS
SELECT Customer_Number
,Order_Date/100+190000 (format '9999-99') AS Yr_Mth_Orders
,COUNT(Order_Total) AS Order_Cnt
,SUM(Order_Total) AS Order_Sum
,AVG(Order_Total) AS Order_Avg
FROM Order_Table
GROUP BY Customer_Number, Yr_Mth_Orders ;
The examples above show how we put a SUM on the aggregate Order_Sum!
Locking Row for Access
SELECT * FROM Emp_HR_v;
The Employee_Table used above will automatically use an ACCESS Lock, which allows ACCESS during UPDATES or table loads.
Most views utilize the Locking row for ACCESS command. This is because they want to be able to read while a table is being updated and loaded into. If the user knows a dirty read won’t have a huge effect on their job, why not make a view lock with an Access Lock, thus preventing unnecessary waiting? Teradata will automatically lock the entire table with an Access Lock if the query isn't eligible for locking at the row level.
Altering A Table
This view will run after the table has added an additional column!
Altering A Table After a View has been Created
This view runs after the table has added an additional column, but it won’t include Mgr_No in the view results even though there is a SELECT * in the view. The View includes only the columns present when the view was CREATED.
A View that Errors After An ALTER
This view will NOT run after the table has dropped a column referenced in the view.
Troubleshooting a View
CREATE VIEW Emp_HR_v6 AS
SELECT *
FROM Employee_Table6 ;
Altering the actual Table
This view will NOT run after the table has dropped a column referenced in the view even though the View was CREATED with a SELECT *. At View CREATE Time, the columns present were the only ones the view considered responsible for, and Dept_No was one of those columns. Once Dept_No was dropped, the view no longer works.
Updating Data in a Table through a View
CREATE VIEW Emp_HR_v8 AS
SELECT *
FROM Employee_Table8;
Updating the table through the View
UPDATE Emp_HR_V8
SET Salary = 88888.88
WHERE Employee_No = 2000000;
Will the View still run?
SELECT *
FROM Employee_Table8
WHERE Employee_No = 2000000;
You can UPDATE a table through a View if you have the RIGHTS to do so.
Loading Data through a View
You can actually utilize a view to load data.
Maintenance Restrictions on a Table through a View
A view cannot be used for maintenance if it:
Performs a join operation – more than one table
Selects the same column twice – wouldn’t know which one to use
Derives data – because it does not undo the math or calculation
Performs aggregation – because this eliminates detail data
Uses OLAP functions – because OLAP data is calculated
Uses a DISTINCT or GROUP BY – eliminates duplicate rows
Perform maintenance on a table through a view, but see the restrictions above first.