-- Start of DDL Script for View SD_HOSPITAL.VW_ZS_JBZDZD_DZBL
-- Generated 2021-04-15 15:43:37 from SD_HOSPITAL@FW2

CREATE OR REPLACE VIEW vw_zs_jbzdzd_dzbl (
   zdid00,
   jbmc00,
   cjr000,
   cjks00,
   pysm00,
   wbsm00,
   yxfw00,
   yxbz00,
   icd900,
   bz0000,
   sfbz00,
   sfcrb0,
   colour,
   sfyx00,
   xtbz00,
   sfmxb0,
   crbmc0,
   sfdbz0 )
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','丙类传染病','4','其他传染病') 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 --是否单病种
       --decode((select sign(count(1)) from BM_DBZZDB b where   (B.ZDBM00 = z.ICD100 or (instr(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','丙类传染病','4','其他传染病') 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
       --decode((select sign(count(1)) from BM_DBZZDB b where   (B.ZDBM00 = i.ICD900 or (instr(ICD900, B.ZDBM00 )>0))),1,'是','否') SFDBZ0 --是否单病种
from   XT_ICD900 i where  SFYX00 = '1' and icd900 not like 'M%/%'  and SYBZ00='1'
and substr(ICD900,1,1) not in ('V','W','X','Y','w','v','x','y')
/


-- End of DDL Script for View SD_HOSPITAL.VW_ZS_JBZDZD_DZBL