In this lesson, you create a view that you want company employees
to access through a directory service on the corporate intranet. Some of the
columns in the EMPLOYEE table contain sensitive information, such as the ABC_SALARY
column, so you create the view to exclude those columns.
To create a simple view from a SELECT statement that is created
with the SQL builder:
- In the Data Project Explorer, expand the tree First
Model.dbm>New Database>SQL Statements.
Right-click the SQL Statements folder and select New
SQL Statement.
- In the New SQL Statement window, follow these
steps:
- In the Statement name field, type EMP_VIEW.
When the statement appears in the SQL Statements folder,
it will have this name.
- In the Statement template field, ensure
that SELECT is selected.
- Ensure that the SQL builder radio button
is selected.
- Click OK.
The SQL builder opens as a new view in Classic Data Architect.
Figure 1. The SQL builder
The view appears in the space that the physical data model editor
occupied. Both views have a tab at the top. The tab for the SQL builder has
the name of the statement that you are creating.
The top part of the
SQL builder
- Add the EMPLOYEE table to the SELECT statement by clicking and
dragging the EMPLOYEE table from the Data Project Explorer into the middle
pane of the SQL builder.
Tip: You can also
add the table by right-clicking the middle pane of the SQL builder and selecting Add
Table.
The SQL builder should now look like this:
Figure 2. The SQL builder with the EMPLOYEE table added
- In the EMPLOYEE table in the middle pane of the SQL builder, select
the following columns:
- ABC_ENAME
- ABC_PHONE
- ABC_MAILID
- ABC_DEPARTMENT
Notice that the names of the columns now appear
on the Columns page of the SQL builder.
Figure 3. Columns page of the
SQL builder
Your SELECT statement should look like this:
SELECT ABC_ENAME, ABC_PHONE, ABC_MAILID, ABC_DEPARTMENT
FROM SCHEMA.EMPLOYEE
- Test the SELECT statement before you create a view from it:
- Right-click the top pane of the SQL builder and select Run
SQL.
- In the Connection Selection window, select the Use
an existing connection radio button. The connection
to your data server is selected automatically.
- Click Finish.
If you look in the Data Output view, you should see that
the statement ran successfully. The returned rows appear in the Results table.
Figure 4. The Data Output view after a successful test run of the SELECT statement
Now that you know that the SELECT statement works, you can create
a view from it.
- Save the physical data model by clicking on the First Model.dbm
tab and typing CRTL+S.
- In the SQL Statements folder in the Data
Project Explorer, right-click EMP_VIEW and select Generate > View.
A view appears under the SCHEMA object
in the First model physical data model:
Figure 5. VIEW1 in the Data Project
Explorer
- Change the view name. Slowly double-click VIEW1 and
type EMP_VIEW.
Now, you are ready to generate the DDL for the view and to create
the view on the data server.