“Cowards die many times before their deaths; the valiant never taste of death but once.”
- William Shakespeare
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 load utilities 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.
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.
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.
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.
There are EXCEPTIONS to the ORDER BY rule as an ANSI OLAP statements also works inside a View.
CREATE VIEW Order_View AS
SELECT
Order_Number AS Ord_No
,Customer_Number AS Cust_No
,to_char (Order_Date, 'DD Mon YYYY') as Ord_Date
,Order_Total
FROM Order_Table ;
SELECT *
FROM Order_View
ORDER BY Ord_Date ;
Views are designed to do many things. In the example above, this view formats data.
CREATE VIEW OrdCust_V AS
SELECT Order_Number AS Ord_No
,O.Customer_Number AS Cust_No
,to_char (Order_Date, 'DD Mon YYYY') as Ord_Date
,Order_Total, Customer_Name
FROM Order_Table as O INNER JOIN Customer_Table as C
ON O.Customer_Number = C.Customer_Number ;
SELECT *
FROM OrdCust_V
ORDER BY Ord_Date ;
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.
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 ALIAS for Salary / 12 that’ll be used in this example is Sal_Monthly. This form of aliasing is most often used.
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.
SELECT *
FROM Emp_v3
ORDER BY Sal_Mnth ;
What happens when this query runs?
What will happen in the above query?
SELECT *
FROM Emp_v3
ORDER BY Sal_Mnth ;
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.
CREATE VIEW Aggreg_Order_v AS
SELECT Customer_Number
,Order_DateAS Yr_Mth_Orders
,COUNT(Order_Total)AS Order_Cnt
,SUM(Order_Total)AS Order_Sum
,AVG(Order_Total)AS Order_Avg
FROMOrder_Table
GROUP BY Customer_Number, Order_Date ;
The example above show how we put a SUM on the aggregate Order_Sum!