Chapter 27
View Functions
“It is easier to go down a hill than up it but the view is much better at the top”
Table of Contents Chapter 27 – View Functions
• Exceptions to the ORDER BY Rule inside a View
• Views sometimes CREATED for Formatting or Row Security
• Another Way to Alias Columns in a View CREATE
• Resolving Aliasing Problems in a View CREATE
• Resolving Aliasing Problems in a View CREATE
• Resolving Aliasing Problems in a View CREATE
• CREATING Views for Complex SQL such as Joins
• WHY certain columns need Aliasing in a View
• Aggregates on View Aggregates
• Altering A Table After a View has been Created
• A View that won't work after an ALTER
• Updating Data in a Table through a View
• Maintenance Restrictions on a Table through a View
Creating a Simple View
CREATE View Employee_V AS
SELECT Employee_No
,First_Name
,Last_Name
,Dept_No
FROM Employee_Table ;
The purposes of views are to restrict access to certain columns, derive columns or Join Tables, and to restrict access to certain rows (if a WHERE clause is used).
Basic Rules for Views
1. No ORDER BY inside the View CREATE (some exceptions exist)
2. All Aggregation needs to have an ALIAS
3. Any Derived columns (such as Math) needs an ALIAS
CREATE View Department_Salaries AS
SELECT Dept_No
,SUM(Salary) as SumSal
,SUM(Salary) / 12 as MonthSal
FROM Employee_Table
GROUP BY 1;
SEL *
FROM Department_Salaries
Order By 1 ;
| Dept_No | SumSal | MonthSal |
| ? | 32800.50 | 2733.38 |
| 10 | 64300.00 | 5358.33 |
| 100 | 48850.00 | 4070.83 |
| 200 | 89888.88 | 7490.74 |
| 300 | 40200.00 | 3350.00 |
| 400 | 145000.00 | 12083.33 |
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.
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 Command
HELP View Emp_View9 ;
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 Monthly_Salary
FROM Employee_Table
WHERE Dept_No = 200 ;
SELECTING from A View
SELECT *
FROM Empl_200_v
ORDER BY Monthly_Salary ;
| Emp_No | Last_Name | Monthly_Salary |
| 1324657 | Coffing | $3,490.74 |
| 1333454 | Smith | $4,000.00 |
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.
Another Way to Alias Columns in a View CREATE
SELECTING from A View
SELECT *
FROM Empl_200_v
ORDER BY Monthly_Salary ;
| Emp_No | Last_Name | Monthly_Salary |
| 1324657 | Coffing | $3,490.74 |
| 1333454 | Smith | $4,000.00 |
Will this View CREATE Error? No! It won't error because it's Aliased above!
Resolving Aliasing Problems in a View CREATE
CREATE VIEW empl_200_v (Emp_Nbr, Last, Monthly_S alary)
AS SELECT Employee_No
,Last_Name
,Salary/12 (format ‘$$$$,$$9.99’) as Sal_Monthly
FROM Employee_Table
WHERE Dept_No = 200 ;
SELECTING from A View
SELECT *
FROM Empl_200_v
ORDER BY 3 ;
The ALIAS for Salary / 12 that'll be used in this example is MONTHLY_SALARY. It came first at the top, even though it is aliased in the SELECT list also.
Resolving Aliasing Problems in a View CREATE
CREATE VIEW empl_200_v (Emp_Nbr, Last, Monthly_S alary)
AS SELECT Employee_No
,Last_Name
,Salary/12 (format ‘$$$$,$$9.99’) as Sal_Monthly
FROM Employee_Table
WHERE Dept_No = 200 ;
SELECTING from A View
SELECT *
FROM Empl_200_v
ORDER BY Sal_Monthly ;
What will happen in the above query?
Resolving Aliasing Problems in a View CREATE
CREATE VIEW empl_200_v (Emp_Nbr, Last, Monthly_Salary)
AS SELECT Employee_No
,Last_Name
,Salary/12 (format ‘$$$$,$$9.99’) as Sal_Monthly
FROM Employee_Table
WHERE Dept_No = 200 ;
ERROR
If you ALIAS at the top then that is the only ALIAS that the query can recognize.
CREATING Views for Complex SQL such as Joins
CREATE VIEW Customer_Order_v AS
SELECT Customer_Name AS Customer
,Order_Number
,Order_Total (FORMAT ‘$$$,$$9.99' ) AS Total_Amount
FROM Customer_Table AS Cust
,Order_Table AS Ord
WHERE Cust.Customer_Number = Ord.Customer_Number ;
SELECT * FROM Customer_Order_v
ORDER BY 1 ;
| Customer | Order_Number | Total_Amount |
| Ace Consulting | 123552 | $5,111.47 |
| Billy's Best Choice | 123456 | $12,346.53 |
| Billy's Best Choice | 123512 | $8,005.91 |
| Databases N-U | 123585 | $15,231.62 |
| XYZ Plumbing | 123777 | $23,454.84 |
A huge reason for Views other than security is to also make Complex SQL easy for users. This view already has the Inner Join built into it, but users just SELECT.
WHY certain columns need Aliasing in a View
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 ;
SELECT Customer_Number
,Order_Sum
FROM Aggreg_Order_v ;
| Customer_Number | Order_Sum | |
| 31323134 | 5111.47 | |
| 87323456 | 15231.62 | |
| 11111111 | 8005.91 | |
| 11111111 | 12347.53 | |
| 57896883 | 23454.84 |
When you CREATE a view you have to ALIAS any aggregation or derived data (such as math). Why? So you can SELECT it later, without having to do a SELECT *. Here we only chose two columns and used their ALIAS to retrieve them.
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 ;
SELECT Customer_Number
,Order_Sum
FROM Aggreg_Order_v ;
| Customer_Number | Order_Sum |
| 31323134 | 5111.47 |
| 87323456 | 15231.62 |
| 11111111 | 8005.91 |
| 11111111 | 12347.53 |
| 57896883 | 23454.84 |
SELECT SUM (Order_Sum)
FROM Aggreg_Order_v ;
| SUM(Order_Sum) |
| 64151.37 |
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?
Altering A Table
CREATE VIEW Emp_HR_v AS
SELECT Employee_No
,Dept_No
,Last_Name
,First_Name
FROM Employee_Table ;
This view will run after the table has added an additional column!
Altering A Table After a View has been Created
CREATE VIEW Emp_HR_v4 AS
SELECT *
FROM Employee_Table4 ;
This view runs after the table has added an additional column, but it won't include Mgr_No in the view results, although there is a SELECT * in the view. The View includes only the columns present when the view was CREATED.
A View that won't work after an ALTER
CREATE VIEW Emp_HR_v5 AS
SELECT Employee_No
,Dept_No
,Last_Name
,First_Name
FROM Employee_Table5 ;
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 ;
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
WHERE Employee_No = 2000000;
-- SELECT from the actual Table
SEL * from Employee_Table8
Where Employee_No = 2000000;
You can UPDATE a table through a View if you have the RIGHTS to do so.
Maintenance Restrictions on a Table through a View
There are a few restrictions that disallow maintenance activity on a view with an INSERT, UPDATE or DELETE request. A view cannot be used for maintenance if it:
1. Performs a join operation – more than one table
2. Selects the same column twice – wouldn't know which one to use
3. Derives data – because it does not undo the math or calculation
4. Performs aggregation – because this eliminates detail data
5. Uses OLAP functions – because OLAP data is calculated
6. Uses a DISTINCT or GROUP BY – eliminates duplicate rows
Perform maintenance on a table through a view, but see the restrictions above first.