A view is a virtual table that does not exist physically in the database.  You can use views as security mechanisms by granting permission on a view but not on underlying tables. Also the views can perform complicated joins on base tables giving the end user an easier way to look at all the data. The syntax for creating a view is:

CREATE VIEW [owner.]view_name[(column_name [, column_name]...)]

[WITH ENCRYPTION] AS

select_statement

[WITH CHECK OPTION]

where

view_name:Is the name of the view. View names must follow the rules for identifiers.

column_name:Is the name to be used for a column in a view. Naming a column in CREATE VIEW is always legal but only necessary when a column is derived from an arithmetic expression, a function, or a constant, when two or more columns could otherwise have the same name (usually because of a join), or when you want to give a column in a view a name different from the column from which it is derived. Column names can also be assigned in the SELECT statement.

ENCRYPTION:Encrypts the syscomments entries that contain the text of the CREATE VIEW statement. This command also hides the view syntax from the user

AS select_statement:Is the SELECT statement that defines the view. It can use more than one table and other views. You must have permission to select from the objects referenced in the SELECT clause of a view you are creating.

WITH CHECK OPTION:Forces all data modification statements executed against the view to adhere to the criteria set within the select_statement defining the view. When a row is modified through a view, the WITH CHECK OPTION guarantees that the data will remain visible through the view after the modification has been committed.

Example 1:

Lets us look at the following table mrm_patient with five fields and one primary key (sysno_patient)

mrm_patient

The second table we are interested in is mrm_condensed_history with five fields one primary key (sysno_porder) and one foreign key (sysno_patient)

mrm_condensed_history

If we want to look up information on the patients and their respective exams, we can create a view called mrm_exam. This way the users will not be required to perform a join on the table instead they can just call up the view. Here is the code for mrm_exam

CREATE VIEW mrm_exam AS

SELECT

p.Last_Name,p.First_Name,c.exam_status_ind,c.exam_date,c.sysno_trans

FROM mrm_patient p

JOIN mrm_condensed_history c

ON p.sysno_patient=c.sysno_patient

When the user types the following command

SELECT * FROM mrm_exam

we will give the following result

As you can see, these tables are connected together by a set of primary and foreign keys. The lines connecting the tables show the inner joins we need to create to bring all the data together. For an explanation of the join, please look at SQL Joins section of the website. After a few tries, we were successful in creating the following view:

CREATE VIEW mrm_data AS

SELECT

p.med_rec_id_patient "mp_med_rec_id_patient",

p.last_name_patient "mp_last_name_patient",

p.first_name_patient "mp_first_name_patient",

p.dob_patient "mp_dob_patient",

p.date_update "mp_date_update",

g.gui_etype_desc "mget_gui_etype_desc",

g.sysno_etype "mget_sysno_etype",

c.sysno_porder "mch_sysno_porder",

c.comp_date "mch_comp_date",

r.sysno_trans "mr_sysno_trans"

r.report_text "mr_report_text"

FROM mrm_patient p

JOIN mrm_condensed_history c

ON p.sysno_patient=c.sysno_patient

JOIN mrm_gui_exam_type g

ON c.sysno_etype=g.sysno_etype

JOIN mrm_report r

ON c.sysno_trans=r.sysno_trans

REMARKS: