6 Understanding Business Views

This chapter contains the following topic:

6.1 Business Views

A business view is a selection of data items from one or more tables. After you create a table, use Oracle's JD Edwards EnterpriseOne Business View Design to create a business view that contains only the data items that are required for the application. Oracle's JD Edwards EnterpriseOne uses the business view that you create to generate the appropriate SQL statements that are necessary to retrieve data from the database. After you define a business view, you can attach it to a:

  • Form that updates data in an interactive application.

  • Report that displays data.

Business views increase performance by moving less data across the network because you include only those data items that an application requires.

Business views are required for creating applications and generating reports. Business views:

  • Link a JD Edwards EnterpriseOne application to one or more tables.

  • Contain all or a subset of data items from one or more tables.

  • Can use table joins to join multiple tables on common fields.

  • Are building blocks for text search indexes that enable full-text searching of data.

    To make a business view available for full-text searching, select the Text Search option on the properties form. Do not select this option if you do not plan to use the business view for full-text searching; doing so can negatively affect performance.

6.1.1 Table Joins

Use the table join feature to join multiple tables in a business view. Joining tables enables you to combine fields from different tables for each record of the primary table. Perform the join using fields that are common to the tables. Define the joined fields to satisfy a join condition, such as when the records, or rows, have the same value in the key fields. The primary table is the table where you initiate the join (usually the table on the left in Oracle's JD Edwards EnterpriseOne Table Design Aid) and the secondary table is the table where you conclude the join (usually the table on the right in JD Edwards EnterpriseOne Table Design Aid). Several types of joins exist, including:

Join Type Description
Simple join, also known as inner join. Includes only rows that match both the primary and secondary tables.
Right outer join. Includes rows that are common to both the primary and secondary tables, and unmatched rows from the secondary table.
Left outer join. Includes rows that are common to both the primary and secondary tables, and unmatched rows from the primary table.
SQL 92 left outer join. Includes rows that are common to both the primary and secondary tables, unmatched rows from the primary table, and any rows with null values from the secondary table—regardless of any Where clause against the fields from the secondary table.

This diagram illustrates a simple table join:

Figure 6-1 Simple join

Description of Figure 6-1 follows
Description of "Figure 6-1 Simple join"

This diagram illustrates a right outer join:

Figure 6-2 Right outer join

Description of Figure 6-2 follows
Description of "Figure 6-2 Right outer join"

This diagram illustrates a left outer join:

Figure 6-3 Left outer join

Description of Figure 6-3 follows
Description of "Figure 6-3 Left outer join"

6.1.2 Table Union

A table union joins entire tables. The system first checks for rows from the primary table, and then for rows with corresponding columns from the secondary table. If the rows from the two tables contain identical data, then only one of the records is retrieved in the union. Unions include rows from the primary table and corresponding columns from the secondary table.

This diagram illustrates a union:

Figure 6-4 Table union

Description of Figure 6-4 follows
Description of "Figure 6-4 Table union"

6.1.3 Select Distinct

If a business view includes the primary key fields of the primary table, every row of the business view query is unique. The primary key field has a different value in each row, or record, of the primary table. If the business view does not contain all primary key fields of the primary table, then duplicate rows can occur during the business view query. You can eliminate the duplicate rows in the output by using the Select Distinct feature when designing the business view.

6.1.4 Primary Key Fields

The fields that are included in the primary index of a table are displayed as key fields in JD Edwards EnterpriseOne Business View Design Aid. These fields include a key icon next to the field name. The primary key fields of a table are always included in the business view. Business views then carry information from the table to an application. To carry forward additional information other than the primary key fields, select additional fields to include in the business view.