在 Oracle ADF 中显示管理器名称而不是管理器 ID

Question

员工表包含 -

Employee_Id,
Manager_Id,
First_Name,
Last_Name

我想显示经理名称而不是 ID

无法使用 LOV

VO 中的当前查询：

SELECT 
Employees.EMPLOYEE_ID, 
Employees.FIRST_NAME, 
Employees.LAST_NAME, 
JobObject.JOB_TITLE, 
Employees.COMMISSION_PCT, 
Departments.DEPARTMENT_NAME, 
Departments.DEPARTMENT_ID, 
JobObject.JOB_ID, 
(First_Name||' '||Last_Name) AS VIEW_ATTR, 
Employees.SALARY, 
Employees.MANAGER_ID
FROM EMPLOYEES Employees, 
DEPARTMENTS Departments, 
JOBS JobObject
WHERE Employees.DEPARTMENT_ID = Departments.DEPARTMENT_ID

Answer 1

我假设您想显示经理姓名以及员工详细信息。是这样，那么以下查询将有所帮助。

SELECT emp.EMPLOYEE_ID,
emp.FIRST_NAME,
emp.LAST_NAME,
JobObject.JOB_TITLE,
emp.COMMISSION_PCT,
dept.DEPARTMENT_NAME,
dept.DEPARTMENT_ID,
JobObject.JOB_ID,
(emp_manager.First_Name || ' ' || emp_manager.Last_Name) AS manager_name,
emp.SALARY
FROM EMPLOYEES emp
JOIN DEPARTMENTS dept ON (emp.department_id = dept.department_id)
JOIN JOBS JobObject ON (emp.job_id = JobObject.job_id)
JOIN EMPLOYEES emp_manager ON( emp.manager_id = emp_manager.employee_id  )

Answer 2

如果我理解正确，你什么也得不到的原因与join有关，当你在FROM子句中使用逗号时，你可能会遇到问题。 对于经理名称，如果你有一个经理表，你必须与它内部连接或尝试这个：

SELECT Employees.EMPLOYEE_ID, 
Employees.FIRST_NAME, 
Employees.LAST_NAME, 
JobObject.JOB_TITLE, 
Employees.COMMISSION_PCT, 
Departments.DEPARTMENT_NAME, 
Departments.DEPARTMENT_ID, 
JobObject.JOB_ID, 
(Employees.First_Name+' '+Employees.Last_Name) AS ManagerName, 
Employees.SALARY, 
Employees.MANAGER_ID
FROM EMPLOYEES Employees,
inner join
DEPARTMENTS Departments on Employees.DEPARTMENT_ID = Departments.DEPARTMENT_ID
left outer join 
JOBS JobObject on JobObject.Job_Id=Employees.Job_Id
where Employees.MANAGER_ID is not null and Employees.MANAGER_ID in (EMPLOYEE_ID from employees)