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
339 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 500 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 48

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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Trying to get a Linked Server to Oracle DB working 21 79
ER Diagram 3 42
Procedure syntax 5 50
How to find the cost of a stored procedure in Oracle and optimize it ?? 2 36
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

749 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