Hi,
I've four tables as below:
SQL> desc per_absence_attendances
Name Null? Type
----------------------------------------- -------- ----------------------------
ABSENCE_ATTENDANCE_ID NOT NULL NUMBER(10)
BUSINESS_GROUP_ID NOT NULL NUMBER(15)
ABSENCE_ATTENDANCE_TYPE_ID NOT NULL NUMBER(9)
ABS_ATTENDANCE_REASON_ID NUMBER(9)
PERSON_ID NOT NULL NUMBER(10)
AUTHORISING_PERSON_ID NUMBER(10)
REPLACEMENT_PERSON_ID NUMBER(10)
PERIOD_OF_INCAPACITY_ID NUMBER(9)
ABSENCE_DAYS NUMBER(9,4)
SQL> desc per_absence_attendance_types
Name Null? Type
----------------------------------------- -------- ----------------------------
ABSENCE_ATTENDANCE_TYPE_ID NOT NULL NUMBER(9)
BUSINESS_GROUP_ID NUMBER(15)
INPUT_VALUE_ID NUMBER(9)
DATE_EFFECTIVE NOT NULL DATE
NAME NOT NULL VARCHAR2(30)
ABSENCE_CATEGORY VARCHAR2(30)
SQL> desc per_all_people_f
Name Null? Type
----------------------------------------- -------- ----------------------------
PERSON_ID NOT NULL NUMBER(10)
EFFECTIVE_START_DATE NOT NULL DATE
EFFECTIVE_END_DATE NOT NULL DATE
BUSINESS_GROUP_ID NOT NULL NUMBER(15)
PERSON_TYPE_ID NOT NULL NUMBER(15)
FULL_NAME VARCHAR2(240)
SQL> desc Per_all_assignments_f
Name Null? Type
----------------------------------------- -------- ----------------------------
ASSIGNMENT_ID NOT NULL NUMBER(10)
PERSON_ID NOT NULL NUMBER(10)
SUPERVISOR_ASSIGNMENT_ID NUMBER(15)
The tables are related as below:
i). per_all_people_f contains information about employee, like person_id, full names
ii). per_absence_attendances contains information for leave records for employees. This table is joined to per_all_people_f using the column PERSON_ID
iii). per_absence_attendance_types contains information about types of absences (e.g Annual Leave). Joined to per_absence_attendances on column ABSENCE_ATTENDANCE_TYPE_ID
iv). Per_all_assignments_f contains information about employee assignment (supervisor etc). Is joined to per_all_people_f using the column PERSON_ID. The coulmn SUPERVISOR_ASSIGNMENT_ID contains the person_id for the supervisor of this employee.
I would like to use SUPERVISOR_ASSIGNMENT_ID to get the Full name of the supervisor for this employee. Ultimately, I'm looking for an output like this:
FULL_NAME ABSENCE_TYPE ABSENCE_DAYS APPROVED_BY
------------ ------------------ ------------------ --------------------------
XXXXX Annual Leave 5 yyyy
Where yyyy is the full name for the supervisor of employee xxxx. Anyone with a hint on this?
Regards
dula
Edited by: user13005731 on Jul 1, 2012 10:27 PM