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?
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.
- → 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