1. Business View Design Guide
  2. Example: Select Distinct Feature

Example: Select Distinct Feature

The business view that is used for this example, Event Detail Business View (V98EVDTL), uses the primary index of the primary table, F89EVDTL (Event Detail File), by default. The primary index of a JD Edwards EnterpriseOne table must be unique. A unique primary index ensures that the system does not return duplicate values when the business view query is generated. However, JD Edwards EnterpriseOne Business View Design Aid enables you to select any other index of the primary table when you process the business view.

These steps illustrate how the Select Distinct feature works:

  1. In JD Edwards EnterpriseOne Business View Design Aid, select the primary table.

  2. From the Table menu, select Change Index to change the primary table index.

    Change Index is available only for the primary table.

    The system displays a warning indicating that the selected column list will be changed.

  3. Click Yes to continue.

    The Available Indices form appears. The first edit field on the form displays the current index of the table that is used by the business view. The default is the primary index.

  4. For this example, select Key by Formtyp, Evtype, Obj from Available Indices, and click OK.

    The Table Joins form and the Selected Columns form reflect the keys of the new index.

  5. Save the changes and quit JD Edwards EnterpriseOne Business View Design Aid.

    If you run an application that uses the V98EVDTL business view with Select Distinct disabled and the changed business view index (Key by Formtyp, Evtyp, Obj), the generated SQL statement is:

    SELECT EDOBJTYPE, EDEVTYPE, EDFORMTYPE FROM PVC. F98EVDTL

    Using this example, you might now have 281 rows of data from table F98EVDTL.

  6. Reopen the V98EVDTL business view.

  7. From the File menu, select Select Distinct.

  8. Select Change Index to select the Key by Formtyp, Evtyp, Obj index from Available Indices, and then click OK.

  9. Save the business view, and quit JD Edwards EnterpriseOne Business View Design Aid.

    You might need to quit the software and sign in again. The software stores the business view in cache memory. Even though you change a business view, the previous business view runs until it is cleared from cache.

    Generate and rerun the same application using the V98EVDTL business view with Select Distinct activated. The generated SQL statement is now:

    SELECT DISTINCT EDOBJTYPE, EDEVTYPE, EDFORMTYPE FROM PVC. F98EVDTL_Continue2

    Using this example, you might now have only 53 rows of data from table F98EVDTL.