Chapter 13 – View Functions
"A bird does not sing because it has the answers, it sings because it has a song."
Anonymous
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
A view may not contain:
1.A name that contains 27 characters
2.An ORDER BY – rows are not ordered in a table, nor in a view
3.Indices – however, any index on underlying tables may be used
4.Column names must use valid characters
5.Aggregates must be assigned an alias due to ( )
6.Derived data with mathematics symbols must have an alias
Above are the basic rules of Views with excellent examples.
Views sometimes CREATED for Formatting or Row Security
CREATE VIEW empl_200_v AS
SELECT
Employee_NumberAS Emp_No
,Last_NameAS "Last Name"
,to_char(salary/12,'$99,999.99') as Monthly_Salary
FROMEmployee_Table
WHERE Dept_No = 200 ;
SELECTING from A View
SELECT *
FROM Empl_200_v
ORDER BY Monthly_Salary ;
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
SELECT *
FROM Empl_200_v2
ORDER BY Monthly_Salary ;
Will this View CREATE Error? No! It won't error because it's Aliased above!
Resolving Aliasing Problems in a View CREATE
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_v4 (Emp_Nbr, LastN, Monthly_Salary)
AS SELECT Employee_Number
,Last_Name
,to_char(Salary/12,'$99,999.99') as Sal_Monthly
FROM Employee_Table
WHERE Dept_No = 200 ;
SELECT *
FROM Empl_200_v4
ORDER BY Sal_Monthly ;
What will happen in the above query?
Resolving Aliasing Problems in a View CREATE
CREATE VIEW empl_200_v4 (Emp_Nbr, LastN, Monthly_Salary)
AS SELECT Employee_Number
,Last_Name
,to_char(Salary/12,'$99,999.99') as Sal_Monthly
FROM Employee_Table
WHERE Dept_No = 200 ;
ERROR –
Sal_Monthly not found
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_NameAS Customer
,Order_Number
,to_char(Order_Total, '$99,999.99') AS Total_Amount
FROMCustomer_TableAS Cust
,Order_TableAS Ord
WHERECust.Customer_Number = Ord.Customer_Number ;
SELECT * FROM Customer_Order_v
ORDER BY 1 ;
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
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.
Using a WHERE Clause When Selecting From a View
SELECT Customer_Name
,Yr_Mth_Orders
,Order_Sum
FROM Customer_Table AS cust
INNER JOIN
Aggreg_Order_v AS v
ON cust.customer_number = v.customer_number
WHERE customer_name LIKE 'Bill%' ;
The above SELECT joins a view to a normal table to request a single customer’s information:
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, although 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
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.