?
Solved

How to calculate a field pulling data from a different table based on a value in the main table

Posted on 2013-06-25
3
Medium Priority
?
347 Views
Last Modified: 2013-06-25
I have a complicated SQL that joins multiple tables.  The main table is a history table that contains IDs that link to other tables.  One of the IDs is for Manager Name which is pulled from 2 different tables depending on the value of the ManagerID in the History table.  If it is positive the Manager Name is created by using the LastName || ', ' || FirstName from the Employee Table.  If the ID is negative, the Manager Name needs to be pulled from another table.  This is the part I need help with.  I don't know how to create the Manager Name based on the ID in the History table.  Here is my SQL:

SELECT EffectiveDate, ReasonDesc, TitleDesc, LastName || ', ' || FirstName As ManagerName, Division, Department, GroupDesc, FunctionDesc, LocationDesc, SubLocationDesc
FROM EMP_OWNER.EMP_EmployeeHistory 
LEFT JOIN EMP_OWNER.EMP_Title ON EMP_Title.TitleID = EMP_EmployeeHistory.TitleID
LEFT JOIN EMP_OWNER.EMP_Employee ON EMP_Employee.EmployeeID = EMP_EmployeeHistory.ManagerSupervisorID
LEFT JOIN EMP_OWNER.EMP_OpenTeam ON EMP_OpenTeam.OpenTeamID = EMP_EmployeeHistory.ManagerSupervisorID
LEFT JOIN EMP_OWNER.EMP_Division ON EMP_Division.DivisionID = EMP_EmployeeHistory.DivisionID
LEFT JOIN EMP_OWNER.EMP_Department ON EMP_Department.DepartmentID = EMP_EmployeeHistory.DepartmentID
LEFT JOIN EMP_OWNER.EMP_Group ON EMP_Group.GroupID = EMP_EmployeeHistory.GroupID
LEFT JOIN EMP_OWNER.EMP_Function ON EMP_Function.FunctionID = EMP_EmployeeHistory.FunctionID
LEFT JOIN EMP_OWNER.EMP_Location ON EMP_Location.LocationID = EMP_EmployeeHistory.LocationID
LEFT JOIN EMP_OWNER.EMP_SubLocation ON EMP_SubLocation.SubLocationID = EMP_EmployeeHistory.SubLocationID
LEFT JOIN EMP_OWNER.EMP_Reason ON EMP_Reason.ReasonID = EMP_EmployeeHistory.ReasonID
WHERE EMP_EmployeeHistory.EmployeeID = 776
ORDER BY EMP_EmployeeHistory.EmployeeHistoryID DESC

Open in new window


How do I pull the Manager Name from the Employee Table when ManagerSupervisorID is positive and from the OpenTeam Table when the ManagerSupervisorID is negative?  Any help is greatly appreciated.
0
Comment
Question by:dyarosh
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 16

Accepted Solution

by:
Walter Ritzel earned 2000 total points
ID: 39274492
Please try this:
SELECT EffectiveDate, ReasonDesc, TitleDesc, case when EMP_EmployeeHistory.ManagerSupervisorID > 0 then EMP_Employee.LastName || ', ' || EMP_Employee.FirstName else  EMP_OpenTeam.LastName || ', ' || EMP_OpenTeam.FirstName end As ManagerName, Division, Department, GroupDesc, FunctionDesc, LocationDesc, SubLocationDesc
FROM EMP_OWNER.EMP_EmployeeHistory 
LEFT JOIN EMP_OWNER.EMP_Title ON EMP_Title.TitleID = EMP_EmployeeHistory.TitleID
LEFT JOIN EMP_OWNER.EMP_Employee ON EMP_Employee.EmployeeID = EMP_EmployeeHistory.ManagerSupervisorID
LEFT JOIN EMP_OWNER.EMP_OpenTeam ON EMP_OpenTeam.OpenTeamID = EMP_EmployeeHistory.ManagerSupervisorID
LEFT JOIN EMP_OWNER.EMP_Division ON EMP_Division.DivisionID = EMP_EmployeeHistory.DivisionID
LEFT JOIN EMP_OWNER.EMP_Department ON EMP_Department.DepartmentID = EMP_EmployeeHistory.DepartmentID
LEFT JOIN EMP_OWNER.EMP_Group ON EMP_Group.GroupID = EMP_EmployeeHistory.GroupID
LEFT JOIN EMP_OWNER.EMP_Function ON EMP_Function.FunctionID = EMP_EmployeeHistory.FunctionID
LEFT JOIN EMP_OWNER.EMP_Location ON EMP_Location.LocationID = EMP_EmployeeHistory.LocationID
LEFT JOIN EMP_OWNER.EMP_SubLocation ON EMP_SubLocation.SubLocationID = EMP_EmployeeHistory.SubLocationID
LEFT JOIN EMP_OWNER.EMP_Reason ON EMP_Reason.ReasonID = EMP_EmployeeHistory.ReasonID
WHERE EMP_EmployeeHistory.EmployeeID = 776
ORDER BY EMP_EmployeeHistory.EmployeeHistoryID DESC

Open in new window

0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39274512
:) yep, use "case when ... then ... when ... then ... end"
but too late.

see:
http://docs.oracle.com/cd/B19306_01/server.102/b14200/expressions004.htm

note, should you need the word 'IF' to explain the requirement, good chance it will be solved by a case expression

NO points please
0
 

Author Closing Comment

by:dyarosh
ID: 39274515
Thank you.  You saved me hours trying to figure it out!
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question