Mimer SQL User's Manual TOC PREV NEXT INDEX

Mimer Developer Site

www.mimer.com/developer


Creating Views


A view is a logical subset of one or more base tables or views where columns are chosen by naming them and rows are chosen through specified conditions relating to column values.

Views are created, for example, so that users who need not see all the data in a single table are shown only the parts of the table that interest them (restriction views). Views can also be created as a combination of a number of columns from several different tables (join views).

Operations on views are actually performed on the underlying base tables. Certain view definitions do not allow data to be changed in the view (read-only views). See Updatable Views for further details.

View names can be up to 128 characters long. Views are defined in terms of a SELECT statement; the result of the SELECT statement forms the contents of the view. There are no restrictions on which select statements that can be used in a view definition.

The example database does not contain any view definitions. Two examples are given below.

Creating a View

Create a restriction view of the BOOK_GUEST table called RECEPTION containing limited information for the hotel reception (reservation number, customer name, check-in date and room number):
 CREATE VIEW RECEPTION (RESERVATION, FNAME, LNAME, DATE, ROOM)
        AS   SELECT RESERVATION, GUEST_FNAME, GUEST_LNAME,
             CHECKIN, ROOMNO
        FROM BOOK_GUEST;
Result:

 RESERVATION
 FNAME
 LNAME
 DATE
 ROOM
 1348
 STEN
 JOHANSEN
 1997-08-23
 LAP205
 1349
 STEFAN
 HANSEN
 1997-08-23
 LAP206
 1350
 SALLY
 WEBERT
 1997-08-06
 SKY124
 1351
 ANNA 
 ALBERTSON
 1997-08-06
 SKY125
 1352
 MARK 
 FRANCIS
 1997-08-14
 WINS103
 1353
 ALFRED
 FIMPLEY
 1997-09-03
 SKY110
  ...
 ...
 
 ...
 ...
  ...
 ...
 
 ...
 ...

Create a join view listing the billing details for each reservation:
 CREATE VIEW CHARGE_DESCRIPTION
     AS SELECT  RESERVATION, COST, DESCRIPTION
        FROM    BILL, CHARGES
        WHERE   BILL.CHARGE_CODE = CHARGES.CHARGE_CODE;
 

If the view definition does not include a list of column names, the columns in the view will be named after the columns listed in the SELECT clause.

Result:

 RESERVATION 
 COST 
 DESCRIPTION 
 1348 
 100
 LODGING     
  ...   
 ...    
 ...        

Check Options

Check options can be used in updatable view definitions to limit the data that can be inserted into the view. If a check option is specified, data which does not fulfill the definition of the view cannot be inserted into the view.

 CREATE VIEW GUEST_VIEW
     AS SELECT  RESERVATION, HOTELCODE, GUEST_FNAME, GUEST_LNAME,
                 CHECKIN, ROOMNO
        FROM    BOOK_GUEST
        WHERE   HOTELCODE = 'STG' OR HOTELCODE = 'WINS'
        WITH    CHECK OPTION;
Result:

 RESERVATION 
 HOTELCODE 
 GUEST_FNAME
 GUEST_LNAME
 CHECKIN
 ROOMNO  
 1355 
 STG       
 INGER
 SVENSON     
 1997-09-01
 STG111  
 1363 
 WINS      
 PAULE
 LE FEVRE 
 1997-08-20
 WINS117
 1364 
 STG       
 LARS
 HOLLSTEN     
 1997-09-01
 STG116
 1367 
 WINS      
 EARNST
 JOHNSSON   
 1997-09-06
 WINS109 
 1371 
 STG       
 MARY
 TENMAR       
 1997-08-29
 STG010 
 1382 
 WINS      
 JULIO
 PEREZ       
 1997-09-29
 WINS119 
 1383 
 STG       
 ROBERT
 LIND       
 1997-08-31
 STG142 
 1384 
 WINS      
 SIGWARD
 PERSSON   
 1997-09-25
 WINS120 
 1385 
 WINS      
 RUNE
 NYQVIST      
 1997-09-25
 WINS121 
 1398 
 STG       
 LENNART
 RYDELL    
 1997-09-30 
 STG1421 
 1401 
 STG       
 JAN
 BLOM          
 1997-09-23 
 STG001  
 1408 
 STG       
 EINAR
 SUNDMAN     
 1997-09-20 
 STG117  
 1412 
 WINS      
 JOHAN
 TORP        
 1997-09-30 
 WINS119 

The check option in the view definition (WITH CHECK OPTION) means that no new rows may be inserted into the view if the value for the HOTELCODE column is not STG or WINS.

If there is an instead of trigger defined for the view, the WITH CHECK OPTION does not have any effect.

Creating Views Based on Other Views

Views can be based on other views. When a view is created based upon another view or views, the original view's limitations are carried over to the new view.

 CREATE VIEW NEW_VIEW
     AS SELECT  RESERVATION, HOTELCODE, GUEST_FNAME, GUEST_LNAME
        FROM    GUEST_VIEW
        WHERE   RESERVATION > 1385;


Upright Database Technology AB
Voice: +46 18 780 92 00
Fax: +46 18 780 92 40
dbtechnology@upright.se
Mimer SQL User's Manual TOC PREV NEXT INDEX