create or replace view sd_hospital.vw_zs_jbzdzd_dzbl as
select to_number(Z.ZDID00) ZDID00,Z.ZDMC00 JBMC00,Z.CJR000,CJKS00,Z.PYSM00,
       Z.WBSM00,Z.YXFW00,Z.YXBZ00,Z.ICD100 AS ICD900,Z.BZ0000,
       Z.SFBZ00,Z.SFCRB0, decode(Z.SFCRB0,'0','0','1','$0000FF') COLOUR,
       YXBZ00 SFYX00,'0' XTBZ00,decode((select sign(count(1)) from XT_ICD900 where Icd900=Z.ICD100 and Sfmxb0='1'),1,'1','0') SFMXB0,
       decode(z.SFCRB0,'1','甲类传染病','2','乙类传染病','3','丙类传染病',' ') crbmc0,  --传染病
       decode((select sign(count(1)) from BM_DBZZDB b where   ((WHLB00 = '0' and B.ZDBM00 = trim(z.ICD100)) or (WHLB00 = '1' and INSTR(z.ICD100,B.ZDBM00)>0)) ),1,'是','否') SFDBZ0 --是否单病种
from   VW_BM_XYZDBM_EFF z where YXBZ00='1'
union all
select 0,JBMC00,0,0,PYSM00,
       WBSM00,'2','Y',ICD900,'',
       'Y',SFCRB0, decode(I.SFCRB0,'0','0','1','$0000FF') COLOUR,
       SFYX00,XTBZ00,sfmxb0,
       decode(i.SFCRB0,'1','甲类传染病','2','乙类传染病','3','丙类传染病',' ') CRBMC0,  --传染病
       decode((select sign(count(1)) from BM_DBZZDB b where   ((WHLB00 = '0' and B.ZDBM00 = trim(i.ICD900)) or (WHLB00 = '1' and INSTR(i.ICD900,B.ZDBM00)>0)) ),1,'是','否') SFDBZ0 --是否单病种
from   XT_ICD900 i where  SFYX00 = '1' and icd900 not like 'M%/%'
and substr(icd900,1,1) not in ('V','W','X','Y')