我都已经用nvl函数了，为何还是有ora-01400错误呢？

[骨骨]

insert into /*+ append */ tab_test1 nologging
select a.subscription_id,a.account_id,a.customer_id,a.subs_status,a.acct_type,a.service_num,
a.service_type,a.service_status,a.rate_class,a.status_chg_date,a.active_date,a.create_date,a.region_id,
(select nvl(rele_office_id,'0') from tab_test3 where account_id=a.account_id) rele_office_id,
a.operator_id,a.reseller_id,
(select nvl(pay_type,0) from tab_test3 where account_id=a.account_id) pay_type,
a.last_oper_type,
(select nvl(county_id,0) from tab_test3 where account_id=a.account_id) county_id,
a.inactive_date
from tab_test2 a

ORA-01400: cannot insert NULL into ("OSS"."tab_test1"."RELE_OFFICE_ID")

请问这是什么原因？

[bluelamb]

nvl((select rele_office_id from tab_test3 where account_id=a.account_id),0)

[骨骨]

找到解决办法了，谢谢！
(select nvl(rele_office_id,'0') from tab_test3 where account_id=a.account_id) rele_office_id,
改为
nvl((select rele_office_id from tab_test3 where account_id=a.account_id),'0') rele_office_id,
即可！

[lialin]

你看这一字段对应的是你select 的哪个，肯定是这个字段有空值了，看一下就知道了！

[liuyi8903]

按这个方式来加nvl
nvl((select nvl(rele_office_id,'0') from tab_test3 where account_id=a.account_id),'0') rele_office_id,

[liuyi8903]

如:
select nvl((select '' from dual), 'asd') from dual

[bzszp]

12:18:34 SQL> select nvl('a','is null') from dual where 1=2;

未选定行

已用时间: 00: 00: 00.78

12:19:40 SQL> select nvl((select 'a' from dual where 1=2),'is null') from dual;

NVL((SE
-------
is null

已用时间: 00: 00: 00.62
12:19:56 SQL>