Chapter 12 – 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
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
The above is designed to introduce View fundamentals, View advantages and View recommendations.
Creating a Simple View to Restrict Sensitive Columns
CREATE View Employee_V1 AS
SELECTEmployee_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.
You SELECT From a View
You SELECT from a view exactly like you SELECT from a table. A user might not know whether or not they were querying a view or a table.
Creating a Simple View to Restrict Rows
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.
A View Provides Security for Columns and Rows
CREATE VIEW Employee_V2 AS
SELECT First_Name
,Last_Name
,Dept_No
,Salary
FROMEmployee_Table
WHEREDept_No IN (300, 400) ;
A view is usually associated with protecting sensitive columns, but view that utilize a WHERE clause also protect sensitive rows from being seen. Also, notice that we did not put an ORDER BY statement in the view creation, but instead you utilize the ORDER BY statement when you query the view.
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 CREATE OR REPLACE Keywords will allow a user to change a view.
An Exception to the ORDER BY Rule inside a View
There are EXCEPTIONS to the ORDER BY rule when creating a view. ANSI OLAP statements that use the ORDER BY statement also work inside a View.
Views Are Sometimes CREATED for Formatting
Create View Order_V5 AS
SELECT Order_Number, Customer_Number
,TO_CHAR(Order_Date , 'MON, DD, YYYY') AS Order_Date
,TO_CHAR(Order_Total, '999,999.99') AS Total
FROM Order_Table ;
SELECT *
FROM Order_V5 ;
Views are designed to do many things. In the example above, this view formats some of the data.
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 or even the rows.
How to Alias Columns in a View CREATE
SELECT *
FROM E_View
ORDER BY Mnth_Sal ;
Columns in a view that are derived, such as aggregates or calculated columns, must have an alias. There are multiple ways to alias the columns and this example shows you the first option.
The Standard Way Most Aliasing is done
SELECT *
FROM E_View2
ORDER BY Sal_Monthly ;
The ALIAS for Salary / 12 is Sal_Monthly. This form of aliasing is used most of the time.
What Happens When Both Aliasing Options Are Present
SELECT *
FROM E_View3
ORDER BY 3 ;
The ALIAS for Salary / 12 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
What happens when this query runs?
What will happen in the above query?
Answer to Resolving Aliasing Problems in a View CREATE
What happens when this query runs?
Error – Sal_Mnth is unrecognized
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.
Creating a View with a Local Check
If a user tries to insert a record into the view above that has a Dept_No = 300 then the insert operation will fail because the record violates the view’s definition. Had the view not been created with the WITH LOCAL CHECK OPTION clause, the insert operation would have been successful, even though the new record would not be visible to the view that was used to add the row. The WITH LOCAL CHECK OPTION rejects any Inserts or Updates that violate the views definition.
Aggregates on View Aggregates
CREATE VIEW Aggreg_Order_v AS
SELECT Customer_Number
,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 ;
The examples above show how we put a SUM on the aggregate Order_Sum!
Altering a Table After a View Has Been Created
CREATE VIEW Emp_HR_v AS
SELECT Last_Name
,Salary
FROM Emp_Tbl3 ;
This view will run after the table has added an additional column!
A View that Errors after an ALTER
CREATE VIEW Emp_HR_v4 AS
SELECT Last_Name
,Salary
FROM Emp_Tbl4 ;
Altering the actual Table
ALTER TABLE Emp_Tbl4
Drop COLUMN Salary ;
SELECT * FROM Emp_HR_V4
ERROR [56098] [IBM][CLI Driver][DB2/NT64] SQL0727N
An error occurred during implicit system action type "3".
SELECT Command Failed.
This table will error if you try to drop a column that exists in a view.