drop function informix getdepstaff integer date CREATE function inform

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
drop function "informix".getdepstaff(integer,date);
CREATE function "informix".getdepstaff(tab_no int,dt date) RETURNING int;
DEFINE stat, id_staff int;
let id_staff = -1;
select worker.status into stat
from worker
where tab_no= worker_no and dt between dt_beg and dt_last and worker.actual in (0,1) and source = 20;
if stat is null then
return id_staff;
end if;
if stat=3 then
select staff_id into id_staff
from staff
where tab_no= staff.worker_no and
dt between staff .dt_beg and staff.dt_last
and staff.actual in (0,1)
and source = 20;
if id_staff is null then
let id_staff = -1;
end if;
else
select staff_id into id_staff
from staff s
where s. worker_no = tab_no
and s.source = 20
and s.actual in (0,1)
and s.dt_last =
(select max(p.dt_last)
from staff p
where p.worker_no = s.worker_no
and p.source = 20
and p.actual in (0,1)
and p.dt_last < dt);
if id_staff is null then
let id_staff = -1;
end if;
end if;
return id_staff;
END function;
grant execute on function "informix".getdepstaff(integer,date) to skr_user as informix;