Force View
A view can be created even if the defining query of the view cannot be executed, as long as the CREATE VIEW command has no syntax errors. We call such a view a view with errors. For example, if a view refers to a non-existent table or an invalid column of an existing table, or if the owner of the view does not have the required privileges, then the view can still be created and entered into the data dictionary. You can only create a view with errors by using the FORCE option of the CREATE VIEW command:
CREATE FORCE VIEW AS ...;
CREATE FORCE VIEW AS ...;
When a view is created with errors, Oracle returns a message and leaves the status of the view as INVALID. If conditions later change so that the query of an invalid view can be executed, then the view can be recompiled and become valid.
Inline View
An inline view is created by placing a subquery in the FROM clause and giving that subquery an alias. The subquery defines a data source that can be referenced in the main query. In the following example, the inline view b returns the details of all department numbers and the maximum salary for each department from the EMPLOYEES table.The WHERE a.department_id = b.department_id
AND a.salary < b.maxsal clause of the main query displays employee names, salaries,
department numbers, and maximum salaries for all the employees who earn less than the maximum salary in their department.
AND a.salary < b.maxsal clause of the main query displays employee names, salaries,
department numbers, and maximum salaries for all the employees who earn less than the maximum salary in their department.
SELECT a.last_name, a.salary, a.department_id, b.maxsal
FROM employees a, (SELECT department_id, max(salary) maxsal
FROM employees
GROUP BY department_id) b
WHERE a.department_id = b.department_id
AND a.salary < b.maxsal;
Related Post:-
0 comments:
Post a Comment