I wrote an Oracle function on my own as bellows:
CREATE OR REPLACE FUNCTION F_FMS420R_GET_ACCUM_AMTA (S_YYY_MM IN VARCHAR2 --起始年月 ,E_YYY_MM IN VARCHAR2 --結束年月 ,ASP IN VARCHAR2 --性質別 ) RETURN NUMBER IS AMT_A NUMBER(20, 2) := 0; BEGIN SELECT AMT_A INTO AMT_A FROM (SELECT YEAR, ASP, SUM(AMT_A) AS AMT_A FROM FMS420FA WHERE (YYY_MM BETWEEN S_YYY_MM AND E_YYY_MM) AND ASP = ASP GROUP BY YEAR, ASP); RETURN (AMT_A); END F_FMS420R_GET_ACCUM_AMTA;
After I test it, it show this error message as bellows:
ORA-01422: exact fetch returns more than requested number of rows (精確擷取傳回的列數超過所要求的列數)
ORA-06512: 在 "AP_PSR.F_FMS420R_GET_ACCUM_AMTA", line 9
ORA-06512: 在 line 11
But this select SQL statement must return one record, if I provide all search criteria
SELECT YEAR, ASP, SUM(B.AMT_A) AS AMT_A FROM FMS420FA B WHERE B.YYY_MM BETWEEN :S_YYY_MM AND :E_YYY_MM AND B.ASP = :ASP GROUP BY YEAR, ASP
Root cause
This error result from the parameter name cannot be equal to database column name. If yes, Oracle will not know what I am doing and ignore this search criteria. That's why it will have ORA-01422 error.
Solution
Just rename input parameter name from ASP to INPUT_ASP, then this problem can be resolved.
CREATE OR REPLACE FUNCTION F_FMS420R_GET_ACCUM_AMTA (S_YYY_MM IN VARCHAR2 --起始年月 ,E_YYY_MM IN VARCHAR2 --結束年月 ,INPUT_ASP IN VARCHAR2 --性質別 ) RETURN NUMBER IS AMT_A NUMBER(20, 2) := 0; BEGIN SELECT AMT_A INTO AMT_A FROM (SELECT YEAR, ASP, SUM(AMT_A) AS AMT_A FROM FMS420FA WHERE (YYY_MM BETWEEN S_YYY_MM AND E_YYY_MM) AND ASP = INPUT_ASP GROUP BY YEAR, ASP); RETURN (AMT_A); END F_FMS420R_GET_ACCUM_AMTA;
No comments:
Post a Comment