ORA-00904：“GETEMPLOYEE_MASSIONDAYS” ： - "％s：无效标识符"

问题描述

我创建函数来累计我的表中的日子，我希望今天为每个员工在选择查询中显示。



我尝试过：



这是函数： -

创建或替换
功能GETEMPLOYEE_MassionDays （EMPLOYEE_ID_P NUMBER，month_P number，year_P number）
 RETURN decimal 
 IS VALUE_P decimal; 
 BEGIN 
 SELECT SUM（TO_DATE（PROF_TASKS.TASK_END_DATE） -  TO_DATE（PROF_TASKS.TASK_START_DATE）+ 1）进入VALUE_P FROM PROF_TASKS 
其中EMPLOYEE_ID = EMPLOYEE_ID_P 
和提取（月份来自TASK_START_DATE ）= month_P 
和摘录（年份从TASK_START_DATE）= year_P 
按TASK_START_DATE订购
; 
返回VALUE_P; 
 END; 

这是选择查询： -

 SELECT PROF_TASKS.EMP_CODE，PROF_EMPLOYEE_DATA.EMP_NAME_AR，PROF_TREE_DETAILS.TREE_NAME 
，nvl（GETEMPLOYEE_MassionDays（PROF_EMPLOYEE_DATA.EMPLOYEE_ID，摘录）（月份来自to_date（'01 / 10/2016'，'dd / mm / yyyy'）），提取（年份来自to_date（'01 / 10/2016'，'dd / mm / yyyy'））），0）as MissionDays 
 FROM PROF_COMPANY_DETAILS 
 RIGHT JOIN PROF_BRANCHES_DETAILS ON PROF_COMPANY_DETAILS.COMPANY_ID = PROF_BRANCHES_DETAILS.COMPANY_ID 
 RIGHT JOIN PROF_GENERAL_DEPARTMENT ON PROF_BRANCHES_DETAILS.BRANCH_ID = PROF_GENERAL_DEPARTMENT.BRANCH_ID 
 RIGHT JOIN PROF_COMPANY_DEPARTMENTS ON PROF_GENERAL_DEPARTMENT.GENERAL_DEPT_ID = PROF_COMPANY_DEPARTMENTS.GENERAL_DEPT_ID 
 RIGHT JOIN PROF_TREE_DETAILS ON PROF_COMPANY_DEPARTMENTS.COMPANY_DEPT_ID = PROF_TREE_DETAILS .COMPANY_DEPT_ID 
 RIGHT JOIN PROF_EMPLOYEE_DATA ON PROF_EMPLOYEE_DATA.TREE_ID = PROF_TREE_DETAILS.TREE_ID 
 LEFT JOIN PROF_JOBS_DETAILS ON PROF_JOBS_DETAILS.JOB_ID = PROF_EMPLOYEE_DATA.JOB_ID 
内部JOIN PROF_TASKS ON PROF_EMPLOYEE_DATA.EMPLOYEE_ID = PROF_TASKS.EMPLOYEE_ID 
其中PROF_BRANCHES_DETAILS.BRANCH_ID = 15且prof_general_department.general_dept_id = 163 
和PROF_COMPANY_DEPARTMENTS.COMPANY_DEPT_ID = 333 
 AND PROF_TREE_DETAILS.TREE_ID = 571 
 AND（PROF_EMPLOYEE_DATA.EMP_NAME_AR不为空）AND（PROF_EMPLOYEE_DATA.SHIFT_ID不为空）和PROF_EMPLOYEE_DATA.action_id = 1 
 AND PROF_EMPLOYEE_DATA.TREE_ID不为空
 AND PROF_EMPLOYEE_DATA.IS_DELETED = 0 

GETEMPLOYEE_MASSIONDAYS： - ％s：无效标识符

解决方案

I create function to sum days in my table and i want display this days for every employee in select query.

What I have tried:

this is the function :-

create or replace
FUNCTION "GETEMPLOYEE_MassionDays" (EMPLOYEE_ID_P NUMBER,month_P number,year_P number)
RETURN decimal
  IS VALUE_P decimal;
BEGIN
SELECT SUM(TO_DATE(PROF_TASKS.TASK_END_DATE) - TO_DATE(PROF_TASKS.TASK_START_DATE) + 1) into VALUE_P FROM PROF_TASKS
where EMPLOYEE_ID=EMPLOYEE_ID_P
and extract(month from TASK_START_DATE)=month_P
and extract(year from TASK_START_DATE)=year_P
order by TASK_START_DATE
;
RETURN VALUE_P;
END;

and this is select query :-

SELECT PROF_TASKS.EMP_CODE ,PROF_EMPLOYEE_DATA.EMP_NAME_AR ,PROF_TREE_DETAILS.TREE_NAME
,nvl(GETEMPLOYEE_MassionDays(PROF_EMPLOYEE_DATA.EMPLOYEE_ID,extract(month from to_date('01/10/2016','dd/mm/yyyy')),extract(year from to_date('01/10/2016','dd/mm/yyyy'))),0) as MissionDays
FROM PROF_COMPANY_DETAILS
 RIGHT JOIN PROF_BRANCHES_DETAILS ON PROF_COMPANY_DETAILS.COMPANY_ID = PROF_BRANCHES_DETAILS.COMPANY_ID
RIGHT JOIN PROF_GENERAL_DEPARTMENT ON PROF_BRANCHES_DETAILS.BRANCH_ID = PROF_GENERAL_DEPARTMENT.BRANCH_ID
RIGHT JOIN PROF_COMPANY_DEPARTMENTS ON PROF_GENERAL_DEPARTMENT.GENERAL_DEPT_ID = PROF_COMPANY_DEPARTMENTS.GENERAL_DEPT_ID
RIGHT JOIN PROF_TREE_DETAILS ON PROF_COMPANY_DEPARTMENTS.COMPANY_DEPT_ID = PROF_TREE_DETAILS.COMPANY_DEPT_ID
RIGHT JOIN PROF_EMPLOYEE_DATA ON PROF_EMPLOYEE_DATA.TREE_ID = PROF_TREE_DETAILS.TREE_ID
LEFT JOIN PROF_JOBS_DETAILS ON PROF_JOBS_DETAILS.JOB_ID = PROF_EMPLOYEE_DATA.JOB_ID
inner JOIN PROF_TASKS ON PROF_EMPLOYEE_DATA.EMPLOYEE_ID = PROF_TASKS.EMPLOYEE_ID
where PROF_BRANCHES_DETAILS.BRANCH_ID = 15 and prof_general_department.general_dept_id = 163
and PROF_COMPANY_DEPARTMENTS.COMPANY_DEPT_ID = 333
AND PROF_TREE_DETAILS.TREE_ID = 571
AND (PROF_EMPLOYEE_DATA.EMP_NAME_AR IS NOT NULL) AND (PROF_EMPLOYEE_DATA.SHIFT_ID IS NOT NULL) and PROF_EMPLOYEE_DATA.action_id=1
AND PROF_EMPLOYEE_DATA.TREE_ID is not null
AND PROF_EMPLOYEE_DATA.IS_DELETED=0

"GETEMPLOYEE_MASSIONDAYS" :- "%s: invalid identifier"

解决方案

这篇关于ORA-00904：“GETEMPLOYEE_MASSIONDAYS” ： - "％s：无效标识符"的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！