sql语句如下:
select
nvl(sum(decode(INSR_DETAIL_CODE,'2',decode(PERS_TYPE,'1',ROW_PERS,0),0)),0) as row_zz
from
(select b.corp_type_code as corp_code,c.corp_type_name as corp_type_name,d.pers_type as pers_type,e.pers_name as pers_name,f.insr_detail_code as insr_detail_code,nvl(count(*),0) as row_pers
from bs_corp_pres a,bs_corp b,bs_corp_type c,bs_insured d,bs_person_type e,bs_pres_insur f
where a.corp_id = b.corp_id
and d.indi_id = f.indi_id
and b.corp_type_code = c.corp_type_code
and a.indi_id = d.indi_id and d.pers_type = e.pers_type
and to_char(f.BEGIN_DATE,'yyyy-mm') <= '2007-08'
and f.indi_join_sta = '1' and d.pers_type = '1' and b.corp_type_code in ('30','40','91','93')
group by b.corp_type_code,c.corp_type_name,d.pers_type,e.pers_name,f.insr_detail_code) t
group by t.pers_name;说明:子查询查出来的数据为空,我现在想这样处理,如果子查询的结果为空,则row_zz赋值为0,而现在查询出来的row_zz为null.
select
nvl(sum(decode(INSR_DETAIL_CODE,'2',decode(PERS_TYPE,'1',ROW_PERS,0),0)),0) as row_zz
from
(select b.corp_type_code as corp_code,c.corp_type_name as corp_type_name,d.pers_type as pers_type,e.pers_name as pers_name,f.insr_detail_code as insr_detail_code,nvl(count(*),0) as row_pers
from bs_corp_pres a,bs_corp b,bs_corp_type c,bs_insured d,bs_person_type e,bs_pres_insur f
where a.corp_id = b.corp_id
and d.indi_id = f.indi_id
and b.corp_type_code = c.corp_type_code
and a.indi_id = d.indi_id and d.pers_type = e.pers_type
and to_char(f.BEGIN_DATE,'yyyy-mm') <= '2007-08'
and f.indi_join_sta = '1' and d.pers_type = '1' and b.corp_type_code in ('30','40','91','93')
group by b.corp_type_code,c.corp_type_name,d.pers_type,e.pers_name,f.insr_detail_code) t
group by t.pers_name;说明:子查询查出来的数据为空,我现在想这样处理,如果子查询的结果为空,则row_zz赋值为0,而现在查询出来的row_zz为null.
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货