The following scenario describes how use JOIN to combine rows from two or more tables based on a common column between them.
Scenario
Information is missing in your Invoice report and you cannot find the information in the Invoices table. Furthermore, you have a column called CREATOR_ID, but the column contains a number pointing to an ID in another table where all the User information is stored. You also have a column called PROJECT_ID which points to another table where all the Project Information is stored.
Solution
To find the table, follow these steps:
Note: You can use other SQL Joins to conduct the following.
Use a LEFT JOIN since this option returns all the rows from the left table (for example, Invoices) even if there are no matches in the right table (for example, User info table, Project info table, etc.).
To add the Invoice creator’s name, match your CREATOR_ID column from the Invoices table to you Users table (unifier_sys_user_info_view). You can do so by using a LEFT JOIN on the USERID column of the User table. Assign an alias (sysuser) to add the information to the end of your statement:
Example
LEFT JOIN unifier_sys_user_info_view sysuser
ON (inv.CREATOR_ID = sysuser.USERID)
Look at the ER Views (System Models View) to see what options are available in your sysuser table.
Grab the following columns from the table: sysuser.FULLNAME
, sysuser.EMAIL
, and sysuser.WORKPHONE
.
Add the columns to the end of the SELECT section of your query. Each column specified in the SELECT portion must have a comma after it, except for the last one.
Access the information about the Project or Shell (name, number, start/end dates, etc.). You can find this information in the table unifier_us_p, with the column proj.PID
that is used to join the two tables.
Example
LEFT JOIN unifier_us_p proj
ON (inv.PROJECT_ID = proj.PID)
Grab the following columns from the table. and add the columns to the end of the SELECT section, before the FROM, of your query. Each column specified in the SELECT portion must have a comma after it, except for the last one.
Example
proj.UGENPROJECTNAME
--> AS ProjectName,
proj.UGENPROJECTNUMBER
--> AS ProjectNumber,
proj.UUU_PROJECT_START_DATE
--> AS ProjectStartDate,
proj.UGENPROJENDDATEDO
--> AS ProjectEndDate,
proj.UGENINITBUDGETCA
--> AS InitialBudget,
proj.UGENREVBUDGETCA
--> AS RevisedBudget
Ensure that you see the following codes:
Example
SELECT inv.PROJECT_ID,
inv.ID AS inv_ID,
inv.RECORD_NO AS inv_record_no,
inv.TITLE AS inv_title,
inv.STATUS AS inv_status,
inv.UVEVENDORNAMETB50 AS vendor,
inv.AMOUNT AS inv_amount,
sysuser.FULLNAME,
sysuser.EMAIL,
sysuser.WORKPHONE,
proj.UGENPROJECTNAME --> AS ProjectName,
proj.UGENPROJECTNUMBER --> AS ProjectNumber,
proj.UUU_PROJECT_START_DATE --> AS ProjectStartDate,
proj.UGENPROJENDDATEDO --> AS ProjectEndDate,
proj.UGENINITBUDGETCA --> AS InitialBudget,
proj.UGENREVBUDGETCA --> AS RevisedBudget
FROM unifier_ui inv
LEFT JOIN unifier_sys_user_info_view sysuser
ON (inv.CREATOR_ID = sysuser.USERID)
LEFT JOIN unifier_us_p proj
ON (inv.PROJECT_ID = proj.PID)
Legal Notices
Copyright © 2016, 2018,
Oracle and/or its affiliates. All rights reserved.
Last Published Wednesday, March 28, 2018