Ad
Oracle PL/SQL Error - Store Procedure - ORA-00979 And ORA-06512
I am using following store procedure to insert data to a table.
create or replace PROCEDURE PM
(
date1 in varchar2
,date2 in varchar2
,date3 in varchar2
) AS
cursor cur_cd is
(
select to_date(date1,'DD-MON-YY') as date1
,trim(t.DEPT_CODE) DEPT_CODE
,count(t.DEPT_CODE) count_dept
,sum(t.amount) amount
from department t
where t.date >= to_date(date2,'DD-MON-YY')
and t.date <= to_date(date2,'DD-MON-YY')
and t.dept_name like 'finance%'
and (trim(t.DT_code)='TR_01' or t.DT_file like 'DTF_20%')
and t.DEPT_CODE not in ('HR','ADMIN','ACADEMIC')
group by t.DEPT_CODE
);
Type rec_set is table of dept_file%rowtype;
v_rec_set record_set;
begin
open cur_cd;
loop
fetch cur_cd
bulk collect into v_rec_set limit 100;
exit when v_rec_set.count()=0;
begin
forall i in v_rec_set.first..v_rec_set.last
insert into dept_file
values v_rec_set(i);
end;
end loop;
close cur_cd;
exception when others then raise;
end PM;
It's giving me a runtime error when execute procedure. But the query execute without error manually.
ORA-000979 : not a GROUP BY expression
ORA-006512 : at "ABS.PM", line 9
Also, when hard code the parameters (date1, date2 and date3) the procedure working without error.
Can you please help me to resolve this error?
Ad
Answer
All non-aggregated columns must be specified in the GROUP BY
clause. Therefore:
GROUP BY TO_DATE (date1, 'DD-MON-YY'), TRIM (t.dept_code)
By the way, are you really storing date values as strings? Why do I ask? Because you're using TO_DATE
functions all over the code. If you are, then try not to do it in the future. Oracle offers DATE
datatype, you should use it.
Ad
source: stackoverflow.com
Related Questions
- → I can't do a foreign key, constraint error
- → How to implement DbDongle::convertTimestamps as workaround of invalid timestamps with MySql strict
- → MySQL error "Foreign key constraint is incorrectly formed"
- → Eloquent Multitable query
- → "Laravel 5.1" add user and project with userId
- → Database backup with custom code in laravel 5 and get the data upto 10 rows from per table in database
- → Laravel 5.1 QueryException when trying to delete a project
- → Using Array in '->where()' for Laravel Query Building
- → Chaining "Count of Columns" of a Method to Single Query Builder
- → Laravel Eloquent Joining Strange query
- → convert time using mysql laravel 5
- → How to update a column after an expiration date in MySQL?
- → Foreign key constraint fails on existing key
Ad