update gz_jbqk set (c30,c46,c47,c48,c49)=(select sum,bingjia,shijia,kuangg,tingzhi from kqb where
kqb.eno=gz_jbqk.c1 and to_char(gz_jbqk.c45,'yyyy-mm')=kqb.date1);
我的目的是:从考勤表中根据时间和工号,更新工资库中的数据.
如果某人这个月没考勤,则在工资库中相应的字段为零.
谢谢
kqb.eno=gz_jbqk.c1 and to_char(gz_jbqk.c45,'yyyy-mm')=kqb.date1);
我的目的是:从考勤表中根据时间和工号,更新工资库中的数据.
如果某人这个月没考勤,则在工资库中相应的字段为零.
谢谢
SET nb = ISNULL((SELECT na FROM ta), 0),
cb = ISNULL((SELECT ca FROM ta), '0')
set c30=isnull(sum,0),c46=isnull(bingjia,0),c47=isnull(shijia,0),c48=isnull(kuangg,0),c49=isnull(tingzhi,0)
from kqb
where kqb.eno=gz_jbqk.c1 and to_char(gz_jbqk.c45,'yyyy-mm')=kqb.date1);
from kqb
提示在这里出错
(select sum,bingjia,shijia,kuangg,tingzhi from
(select kqb.eno,kqb.sum,kqb.bingjia,kqb.shijia,kqb.kuangg,kqb.tingzhi
from kqb,gz_jbqk where gz_jbqk.c1=kqb.eno
union select gz_jbqk.gh,0 as sum,0 as bingjia,0 as shijia,0 as kuangg,0 as tingzhi from gz_jbqk where gz_jbqk.c1 not
in (select eno from kqb)) b where b.eno=gz_jbqk.c1)
*
ERROR 位于第3行:
ORA-01790: 表达式必须具有与对应表达式相同的数据类型
(select sum,bingjia,shijia,kuangg,tingzhi from
(select kqb.eno,kqb.sum,kqb.bingjia,kqb.shijia,kqb.kuangg,kqb.tingzhi
from kqb,gz_jbqk where gz_jbqk.c1=kqb.eno
union select gz_jbqk.gh as eno,0 as sum,0 as bingjia,0 as shijia,0 as kuangg,0 as tingzhi from gz_jbqk where gz_jbqk.c1 not
in (select eno from kqb)) b where b.eno=gz_jbqk.c1)
(select sum,bingjia,shijia,kuangg,tingzhi from
(select kqb.eno,kqb.sum,kqb.bingjia,kqb.shijia,kqb.kuangg,kqb.tingzhi
from kqb,gz_jbqk where gz_jbqk.c1=kqb.eno
union select gz_jbqk.c1 as eno,0 as sum,0 as bingjia,0 as shijia,0 as kuangg,0 as tingzhi from gz_jbqk where gz_jbqk.c1 not
in (select eno from kqb)) b where b.eno=gz_jbqk.c1)
to_char(gz_jbqk.c45,'yyyy-mm')=kqb.date1
to_char把日期转换成字符了,而后面是不是日期格式的啊?
所以是不是直接gz_jbqk.c45=kqb.date1这样就行了
create or replace trigger bi_gz_jbqk
before update on gz_jbqk
for each row
begin
if :new.c30 is null then
:new.c30:=0;
end if; if :new.c46 is null then
:new.c46:=0;
end if;
if :new.c47 is null then
:new.c47:=0;
end if;
if :new.c48 is null then
:new.c48:=0;
end if;
if :new.c49 is null then
:new.c49:=0;
end if;
end;