Create a custom view to join multiple tables

About this task

A custom view can be created to join multiple Oracle and DB2® tables. This approach can be used for either out-of-the-box or custom tables.

Product APIs are then used to query from the custom views. Custom queries are not required.

Procedure

  1. Create custom table(s), if required.
  2. Write a View entity, using the example below as a guideline.

    The db framework generates db classes and APIs on the view to retrieve records.

    Restrictions: Classes or APIs generated in this way support the get and list API functions only, and cannot be used to modify data.

    dbverify does not process or handle views. Any change made in the xml file for a view is not processed by dbverify.

  3. Customize the appropriate console to invoke the database APIs corresponding to the view.
  4. Build and deploy your customization.

Example: inventory item search

A business case necessitates an inventory item search (inventory console) based on extended attributes of yfs_item. Such a search requires the yfs_item and yfs_inventory_item tables to be joined in a view, but yfs_inventory_item is not extensible.

The view entity is defined as shown in the ExtnCatalogSearchConfigProperties.xml file below, and the inventory console is customized to invoke the database APIs corresponding to the view.

The following is the sample ExtnCatalogSearchConfigProperties.xml file:

<DBSchema>
  <Entities>
    <Entity TableName="EXTN_INV_ITEM_VW"
            Description="This view joins YFS_ITEM and YFS_INVENTORY_ITEM 
        tables to enable querying based on webclass and subclass 
        attributes"
            View="true"
            EntityType="VIEW"
            HasHistory="False"
            AuditRequired="N"
            ApiNeeded="Y"
            Prefix="EXTN" >
      <Attributes>
        <Attribute ColumnName="ITEM_INV_VW_KEY"
                   DataType="Key"
                   DefaultValue="' '"
                   Description="Primary key for this view."
                   Nullable="false"
                   XMLName="ItemInVwKey"/>
        <Attribute ColumnName="ITEM_ID"
                   DataType="ItemID"
                   Description="Identifer for this inventory item."
                   Name="Item_Id"
                   Nullable="false"
                   XMLName="ItemID"
                   DefaultValue="' '" />
        <Attribute ColumnName="UOM"
                   DataType="UOM"
                   Description="Unit of measure for this inventory item."
                   Name="Uom"
                   Nullable="false"
                   XMLName="UnitOfMeasure"
                   DefaultValue="' '" />
        <Attribute ColumnName="EXTN_SUBCLASS"
                   DataType="VARCHAR2-24"
                   Type="VARCHAR2"
                   Size="24"
                   DefaultValue="' '"
                   Nullable="false"
                   XMLName="ExtnSubclass"/>
        <Attribute ColumnName="DEFAULT_PRODUCT_CLASS"
                   DataType="ProductClass"
                   DefaultValue="' '"
                   Description="Default product class of an item."
                   Name="Default_Product_Class" 
                   Nullable="false"
                   XMLName="DefaultProductClass"/>
        <Attribute ColumnName="PRODUCT_CLASS"
                   DataType="ProductClass"
                   DefaultValue="' '"
                   Description="Product class for the item of this
                     inventory audit."
                   Name="Product_Class"
                   Nullable="false"/>
        <Attribute ColumnName="DESCRIPTION"
                   DataType="ItemDesc"
                   DefaultValue="' '"
                   Description="Description of the item."
                   Name="Description"
                   Nullable="false"
                   XMLName="Description"/>
      </Attributes>
      <PrimaryKey>
        <Attribute ColumnName="ITEM_INV_VW_KEY"
                   Name="Item_Inv_Vw_Key"/>
      </PrimaryKey>
    </Entity>
  </Entities>
</DBSchema>