SQL JOIN

Use this method when some data is not stored in the Invoices Line Item table. The following scenario describes how use JOIN to combine rows from two or more tables based on a common column between them.

Scenario

You need to find the "unit of measure" name for each line item.

Solution

This appears to be in the table SYS_DATA_OPTION.

Note: Replace "unifier" with your table prefix. See Review Unifier Database Table Structure for information about finding a table name.

LEFT JOIN SYS_DATA_OPTION sdo

ON inv_li.UGENUNITOFMEASUREPD = sdo.OPTION_VALUE

AND (sdo.DATA_NAME = 'Unit of Measure'

AND sdo.REGISTRY = 'unifier')

  1. Grab the following column from the table: sdo.OPTION_NAME AS inv_li_uom and add them to your SELECT. The goal is to be able to access the cost code number and description for each line item, which can be found in the table unifier_budgetitem:

    LEFT JOIN unifier_budgetitem bi

    ON inv_li.BITEMID = bi.BITEMID

    AND inv_li.BUDGETID = bi.BUDGETID

  2. Grab the following columns from the table: bi.CODE & bi.ITEM and add them to your SELECT.


Legal Notices
Copyright © 2016, 2018, Oracle and/or its affiliates. All rights reserved.

Last Published Wednesday, March 28, 2018