Creating SQL Objects › Creating a View › Example Source Member

Example Source Member

The following example shows the CREATE VIEW statement. See How to Submit SQL Statements for the steps to obtain the Source Panel and an explanation of the fields.

 =>
 =>
 =>
 
 -------------------------------------------------------------------------------
 Interactive SQL Service Facility                                       SQLMAINT
 
                                 Source Panel                               S01S
            EDIT     Member: DEPTVEW   Output Line Limit:  01000
                     Person: JONES
             Current Authid: JONES
                Description: VIEW DEPARTMENT ORGANIZATION
 -------------------------------------------------------------------------------
 ====== ========================== T O P =======================================
 000001 create view deptvew
 ......      (deptno, deptname, admdept)
 ...... as select all
 ......      deptno, deptname, admdept
 ...... from depttbl
 ...... where admdept = 'A1';
 ====== ======================= B O T T O M ====================================
 PF1=HELP         PF2=END         PF3=SPLIT         PF4=PROCESS
 PF5=TOP          PF6=BOTTOM      PF7=BACKWARD      PF8=FORWARD
 PF9=EXECUTE      PF10=LEFT       PF11=RIGHT        PF12=ALTERNATE

Before entering the statement, we inserted five additional lines on the panel by typing i5 in the line number and pressing Enter.

The statement CREATE VIEW is required and must be followed by the name of the view, which is DEPTVEW in this example.

The names of the columns in the view are listed on one line in this example. The column names must be separated by commas and enclosed in parentheses. Since the view is based on an existing table, the view columns have the same data type as the table column. The first column named for the view corresponds to the first column named in the SELECT statement, the second column for the view corresponds to the second column named in the SELECT, and so on. In the previous example, the view columns have the same names as the table columns.

The WHERE clause limits the rows in this view to those rows in the table where the value of the ADMDEPT column is equal to the literal value 'A1'.

For more information on the syntax of the CREATE VIEW statement, see CREATE VIEW.

After placing your SQL statement in the numbered line area on the Source Panel, you can perform the following:

• Enter additional SQL statements. Place a semicolon (;) after each complete statement.
• Press PF9 (EXECUTE) to execute the source member. CA Datacom Datadictionary responds with the Output Panel which shows the results of the SQL processing.
• Press PF2 (END) to obtain an SQLMAINT Panel where you can select another option.
• Press the CLEAR key to return to the Interactive SQL Service Facility Panel where you can choose another option.