#将lwproject的Currency的总数除以projectid的个数 将结果赋值给lwcustomer的field20表提示ORA-00936 missing expression 错误.....
update lwcustomer a set a.field20=select sum(p.currency) from lwproject p where p.status='4'/select count(s.projectid) from lwproject s ,a.oler='-1' where a.customername='AIM'
update lwcustomer a set a.field20=select sum(p.currency) from lwproject p where p.status='4'/select count(s.projectid) from lwproject s ,a.oler='-1' where a.customername='AIM'
我的意思是:把某个客户做的项目全部的金额 除以项目的个数 放在lwcustomer客户表中的一个字段
(其他好多查询条件没加上 这是简写的,怕别人看着烦)
报错?
你看看
p.currency 这个字段是字符型的?如果是的话p.currency要改成to_number(p.currency)
count(case oler when '-1' then p.projectid end) from lwproject p)
where a.customername='AIM'
还是报这个inconsistent datatypes:expected number got char错误
set a.field20=
(select sum(case p.status when '4' then to_number(p.currency) end)
/
count(case oler when '-1' then p.projectid end) from wproject p)
where a.customername='AIM'先判断分母是否为零,最后将分子,分母赋值给临时变量:临时变量1=(select sum(case p.status when '4' then to_number(p.currency) end) ;临时变量2=count(case oler when '-1' then p.projectid end) from wproject p)
where a.customername='AIM'
最后:update lwcustomer a
set a.field20= 临时变量1 / 临时变量2