Building and Executing the Order Information View
To obtain information on the order and customer, you use the ds_orders data source which contains:
• Order information in the orders table.
• Details of each order in the orderdetails table.
• Customer information in the customers table.
You will include the tables orders, orderdetails, and customers in this view.
To build and execute the order information view
1. Right-click Shared > sources.
2. Select New View.
3. Type ViewOrder as the name for the view.
4. Click OK.
When this view is added to the folder, the view editor opens for your use in the right pane of the Modeler.
The editor has the following tabs:
Tab | Description |
Model | Use to assemble the tables. |
Grid | Use to specify query constraints and the columns to include in the output when you execute your view. |
SQL | Use to display the SQL for the view when you design the view in the Model and Grid panels. You can also type SQL in the SQL tab. |
Columns | Use to list the columns to select for projection in the view execution result. |
Indexes | Use to view reports on the index in the system tables, if the view is published. |
Foreign Keys | Use to define foreign keys. |
Caching | Use to access the cache setting panel to configure the caching. |
Cardinality Statistics | This panel provides an entry point to access the statistics setting panel where you can configure the statistics for the cached view. |
Test Identity | This panel is provided for the row-based security feature. |
Info | Use to provide annotations on the view. |
5. From the ds_orders data source drag the following tables into the Model panel in the editor:
— customers
— orderdetails
— orders
6. Click OrderID in orders and drag it onto OrderID in orderdetails.
A line appears representing the inner join between the two tables.
7. Join CustomerID in customers with CustomerID in orders.
For details on joins, see the TDV User Guide.
8. Select the Grid panel.
The asterisk in the first cell under Column indicates that all the columns in all the tables are selected for retrieval in the result set when the view is executed.
9. To limit the columns in the result set:
a. Click the first cell under Column.
b. Select orderdetails.orderid in the drop-down list.
c. Click more rows and select the following columns as you did in the preceding step:
— orderdetails.status
— orderdetails.productid
— orderdetails.discount
— orders.orderdate
— customers.companyname
— customers.contactfirstname
— customers.contactlastname
— customers.phonenumber
10. In the Alias column, click the cell next to customers.contactfirstname.
11. Type the alias CustomerContactFirstName.
12. Press the Enter key after typing the alias.
An alias makes a column name unique, which avoids conflicts with columns from other data sources having the same name.
13. Type the alias for each of the following columns.
Column | Alias to type |
customers.contactlastname | CustomerContactLastName |
customers.phonenumber | CustomerContactPhone |
The asterisks next to the View Order indicates that you have made changes but have not saved them.
14. Save the view.
15. Optionally, you can view the SQL for this view by selecting the SQL tab. After you view the SQL, return to the modeling area by selecting the Model tab.
Typing or editing the SQL in the SQL panel invalidates the design made in the Model and Grid panels.
16. Execute the view by clicking the Execute button.
The Result panel opens and displays the result of the view’s SQL execution.
17. In the result displayed in the Result panel, identify the row for OrderID = 24, which has the following data:
OrderID: 24
Status: open
ProductID: 23
Discount: 0.05
OrderDate: 2003-02-06
CompanyName: Landmark Systems
CustomerContactFirstName: Joyce
CustomerContactLastName: Landers
CustomerPhone: (212) 333-1000
This result set contains the basic information for the Order department about the order identification, order date, product identification, and customer that are all relevant for the current example.
The following screen shows the view execution results displayed in the Result panel.
18. Close the ViewOrder view tab.
