1) As others mentioned,"day_date = day_date + 1;" has missing the correct assignment operator ":=".
2) The "EXECUTE..." part is not required here. Why are You using it?
3) What is your goal? The current structure looks "weird". The first loop statement has no control of the flow, only the inner one has, but its loop iterations is only based on the employees count, not the dates.
4) Is the update statement correct? I mean the "set empshift.<..>. I doubt, he has an attribute named "01/04/2017".
Created an example,:
declare
l_day_date date:=to_date('01/04/2017','DD/MM/YYYY');
l_res varchar2(400);
begin
loop
for l_emp in (select emp_id from empshift_test_v)
loop
dbms_output.put_line('the emp_id is :'||l_emp.emp_id);
--update empshift_test_v etv
--set etv.empshift_code = (
select
nvl((select
sct.sch_id ||'-'|| sct.shifts shift_code
from
SCHEDULEEMPLOYEES_TEST_V sct,
empshift_test_v etv1
where
sct.day_date = l_day_date and
sct.emp_id = etv1.emp_id and
etv1.emp_id = l_emp.emp_id),'no_info')
into
l_res
from
empshift_test_v etv
where
etv.emp_id = l_emp.emp_id;
dbms_output.put_line('day_date is :'||to_char(l_day_date,'DD/MM/YYYY'));
dbms_output.put_line('l_res is :'||l_res);
end loop;
l_day_date := l_day_date + 1;
exit when l_day_date >to_date('30/04/2017','DD/MM/YYYY');
end loop;
end;
WHERE views "EMPSHIFT_TEST_V" and "SCHEDULEEMPLOYEES_TEST_V" has info like:
enter image description here
Hope it helps.
UPDATE:
Modified it according to you data.
declare
l_day_date date:=to_date('01/04/2017','DD/MM/YYYY');
l_res number;
l_stmt varchar2(4000);
begin
loop
for l_emp in (select emp_id from empshift_test)
loop
dbms_output.put_line('the emp_id is :'||l_emp.emp_id);
begin
select
sct.shift
into
l_res
from
SCHEDULEEMPLOYEES_TEST sct,
empshift_test etv
where
sct.daydate = l_day_date and
sct.emp_id = etv.emp_id and
etv.emp_id = l_emp.emp_id;
exception
when NO_DATA_FOUND then
l_res := 0;
end;
dbms_output.put_line('day_date is :'||to_char(l_day_date,'DD/MM/YYYY'));
dbms_output.put_line('l_res is :'||l_res);
if l_res > 0 then
l_stmt := 'update empshift_test emp
set emp."'||to_char(l_day_date,'DD/MM/YYYY')||'" = '||l_res||'
where emp.emp_id = '||l_emp.emp_id||';';
dbms_output.put_line('l_stmt is :'||l_stmt);
execute immediate l_stmt;
end if;
end loop;
l_day_date := l_day_date + 1;
exit when l_day_date >to_date('30/04/2017','DD/MM/YYYY');
end loop;
end;
But there is a catch: if you run the DML statement manually - it works, but with execute immediate - it throws error ora-00933. He cant read the number column for some reason.
Another good stack question to solve:)
So the plan is:
1) Change the table structure; or
2) Solve the problem, when calling a attribute, named as number (with symbols like "/") using execute immediate.
execute immediate
. Just print it and then try debug it. – XINGday_date = day_date + 1;
you are missing a:
; in||'-'||
you need to double the quotes; it seems thatEMPSHIFT
has both anEmpId
and anEMPLOYEEID
column, is this right? The Query you build uses the date without quoting or ( better) using ato_date
,... – Aleksej