--%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%% PROMPT 130.创建传染病已报告过的病人信息视图 view VW_YS_MZCRDJ_YBG create or replace view sd_hospital.VW_YS_MZCRDJ_YBG ( DJID00, MZID00, GHH000, BRXM00, HEJZXM, BRZJBH, BRXB00, BRCSRQ, CSRQN0, CSRQY0, CSRQR0, TYDWMC, BRLXDH, BRLS00, DZSF00, DZDJS0, DZXQ00, DZXZJD, DZC000, DZMPH0, BRLXDZ, BRZY00, FBRQ00, FBRQ01, ZDRQ00, ZDRQ01, ZDSJ00, ZDSJ01, SWRQ00, BLFL01, BLFL02, TKYS00, TKYSXM, TKRQ00, TKRQ01, JBMC00, JCZCR0, BZ0000, CZY000, CZRQ00, CZSJ00, SFDJ00, JZKS00, DQKSMC, HY0000, WHCD00, CRLY00, NL0000, SFSW00, JZRQ00, SFQR00, QRR000, QRRXM0, SFYYE0, JZYSXM, JZYS00, BRNL00, CRB001, CRB002, CRB003, CRB004, CRB005, CRB006, CRB007, CRB008, CRB009, CRB010, CRB011, CRB012, CRB013, CRB014, CRB015, CRB016, CRB017, CRB018, CRB019, CRB020, CRB021, CRB022, CRB023, CRB024, CRB025, CRB026, CRB027, CRB028, CRB029, CRB030, CRB031, CRB032, CRB033, CRB034, CRB035, CRB036, CRB037, CRB038, CRB039, CRB040, CRB041, CRB042, CRB043, CRB044, CRB045, CRB046, CRB047, CRB048, CRB049, CRB050, CRB051, CRB052, CRB053, CRB054, CRB055, CRB056, CRB057, CRB058, CRB059, CRB060, CRB061, CRB062, CRB063, CRB064, SFDY01, SFDY02, SFDY03, SFDY04, SFDY05, SFDY06, SFDY07, SFDY08, SFDY09, SFDY10, SFDY11, SFDY12, SFDY13, SFDY14, SFDY15, SFDY16, SFDY17, SFDY18, SFDY19, SFDY20, SFDY21, SFDY22, SFDY23, SFDY24, SFDY25, SFDY26, SFDY27, SFDY28, SFDY29, SFDY30, SFDY31, SFDY32, SFDY33, SFDY34, SFDY35, SFDY36, SFDY37, SFDY38, SFDY39, SFDY40, SFDY41, SFDY42, SFDY43, SFDY44, SFDY45, SFDY46, SFDY47, SFDY48, SFDY49, SFDY50, SFDY51, SFDY52, SFDY53, SFDY54, SFDY55, SFDY56, SFDY57, SFDY58, SFDY59, SFDY60, SFDY61, SFDY62, SFDY63, SFDY64, SFZH00, SEX_M , SEX_F , CSRQ00, BRLS01, BRLS02, BRLS03, BRLS04, BRLS05, BRLS06, BRZY01, BRZY02, BRZY03, BRZY04, BRZY05, BRZY06, BRZY07, BRZY08, BRZY09, BRZY10, BRZY11, BRZY12, BRZY13, BRZY14, BRZY15, BRZY16, BRZY17, BRZY18, BRZY19, BRZY20, BLFL11, BLFL12, BLFL13, BLFL14, BLFL15, BLFL21, BLFL22, KPBH00, KPLX00, QTCRB0, DZJBMC, TKYY00, KPLX01, KPLX02, ZDXXID, TKSJ00, SFCRB0, GHZD00, FBMC00, BRNL0S, CRBLB0, CRB065, CRB066, CRB067, CRB068, CRB069, CRB070, CRB071, CRB072, CRB073, CRB074, CRB075, SFDY65, SFDY66, SFDY67, SFDY68, SFDY69, SFDY70, SFDY71, SFDY72, BRSZNL, SFDY73, SFDY74, SFDY75, CRB076, CRB077, CRB078, CRB079, CRB080, CRB081, CRB082, CRB083, SFDY76, SFDY77, SFDY78, SFDY79, SFDY80, SFDY81, SFDY82, SFDY83, SFZF00, CRB088, CRB089, CRB090, SFDY88, SFDY89, SFDY90 ) as -- MODIFICATION HISTORY -- Person Date Comments -- zhangwz 2012.03.19 create by MZYS-20120319-001 视图写法来自 VW_YS_MZCRDJ_ALL 仅仅去掉外连接 A.GHID00=B.MZID00(+) -->A.GHID00=B.MZID00. -- linyj 2017.05.31 增加字段SFZF00 是否作废 For MZYS9-20170527-002 -- cyhong 2019.05.13 增加字段CRB088,CRB089,CRB090 For MZYSSJ5-20190428-001 select decode(B.CZRQ00, null, '', B.DJID00) DJID00, --登记ID A.GHID00 MZID00, --挂号ID A.GHH000, --挂号号 decode(B.CZRQ00, null, C.BRXM00, B.HZXM00) BRXM00, --患者姓名 B.HEJZXM, --患儿家长姓名 decode(B.CZRQ00, null, C.BRZJBH, B.SFZH00) BRZJBH, --身份证号 decode(B.CZRQ00, null, C.BRXB00, B.BRXB00) BRXB00, --性别 decode(B.CZRQ00, null, C.BRCSRQ, B.BRCSRQ) BRCSRQ, --病人出生日期 substr(b.brcsrq,1,4) csrqN0, --病人出生日期年 substr(b.brcsrq,5,2) csrqY0, --病人出生日期月 substr(b.brcsrq,7,2) csrqR0, --病人出生日期日 decode(B.CZRQ00, null, C.TYDWMC, B.TYDWMC) TYDWMC, --病人工作单位 decode(B.CZRQ00, null, C.BRLXDH, B.BRLXDH) BRLXDH, --病人联系电话 B.BRLS00, --病人隶属： --本县区 本市其它县区 本省其它地市 其他省 港澳台 外籍 B.DZSF00, --地址省份 B.DZDJS0, --地址地级市 B.DZXQ00, --地址县区 B.DZXZJD, --地址乡镇街道 B.DZC000, --地址村 B.DZMPH0, --地址门牌号 decode(trim(nvl(b.brlxdz,'')),'', C.BRJTDZ, B.BRLXDZ) BRLXDZ, --病人联系地址 decode(B.CZRQ00, null, C.BRZY00, B.BRZY00) BRZY00, --病人职业 nvl(B.FBRQ00,nvl((select f.FBRQ00 from YS_BRBQB0 f where a.GHID00=f.GHID00 and rownum = 1 ),to_char(sysdate,'YYYYMMDD'))) FBRQ00, --发病日期 nvl(to_date(B.FBRQ00,'YYYYMMDD'),SYSDATE) FBRQ01, --发病日期01 decode(B.CZRQ00, null, A.JZRQ00, B.ZDRQ00) ZDRQ00, --诊断日期 to_date(decode(B.CZRQ00, null, A.JZRQ00, B.ZDRQ00),'YYYYMMDD') ZDRQ01, --诊断日期01 decode(B.CZRQ00, null, A.JZSJ00, B.ZDSJ00) ZDSJ00, --诊断时间 to_date(decode(B.CZRQ00, null, A.JZSJ00, B.ZDSJ00),'HH24:MI:SS') ZDSJ01, --诊断时间01 decode(B.SWRQ00,' ',substr(B.SWRQ00,1,8)) SWRQ00, --死亡日期 B.BLFL01, --病例分类01 B.BLFL02, --病例分类02 B.TKYS00, --填卡医生 (select zwxm00 from bm_ygbm00 where ygbh00(+) = B.TKYS00) TKYSXM, --填卡医生姓名 B.TKRQ00, --填卡日期 nvl(to_date(B.TKRQ00,'YYYYMMDD'),SYSDATE) TKRQ01, --填卡日期01 decode(B.CZRQ00, null,d.zdmc00, B.JBMC00) JBMC00, --疾病名称 B.JCZCR0, --密切接触者有无相同症状 0:无 1：有 B.BZ0000, --备注 B.CZY000, --操作员 B.CZRQ00, --操作日期 B.CZSJ00, --操作时间 decode(B.CZRQ00,null,'未登记','已登记') SFDJ00, --是否已登记 A.JZKS00, --当前科室 (select bmmc00 from bm_bmbm00 where bmbh00(+) = a.JZks00) DQKSMC, --当前科室名称 B.HY0000, --婚姻 B.WHCD00, --文化程度 B.CRLY00, --传染来源 substr(trim(SF_XT_CSRQTONL(B.BRCSRQ,A.JZRQ00)),1,8) NL0000, nvl(B.SFSW00,'N') SFSW00, --是否死亡 A.JZRQ00, --就诊日期 nvl(B.SFQR00,'N') SFQR00, --是否确认 B.QRR000, --确认人 (select zwxm00 from bm_ygbm00 where ygbh00(+) = B.QRR000) QRRXM0, --确认人姓名 decode((select count(1) from BQ_JSXX00 where BRid00=A.BRid00),0,'0','1') SFYYE0, --是否有婴儿 0：无 1：有 d.JZYSXM, --诊断医生 d.JZYS00, SF_BQ_CSRQTONL(decode(B.CZRQ00, null, C.BRCSRQ, B.BRCSRQ)) BRNL00, CRB001,CRB002,CRB003,CRB004,CRB005,CRB006,CRB007,CRB008,CRB009,CRB010, CRB011,CRB012,CRB013,CRB014,CRB015,CRB016,CRB017,CRB018,CRB019,CRB020, CRB021,CRB022,CRB023,CRB024,CRB025,CRB026,CRB027,CRB028,CRB029,CRB030, CRB031,CRB032,CRB033,CRB034,CRB035,CRB036,CRB037,CRB038,CRB039,CRB040, CRB041,CRB042,CRB043,CRB044,CRB045,CRB046,CRB047,CRB048,CRB049,CRB050, CRB051,CRB052,CRB053,CRB054,CRB055,CRB056,CRB057,CRB058,CRB059,CRB060, CRB061,CRB062,CRB063,CRB064, decode(CRB001,'-1','√','') SFDY01,decode(CRB002,'-1','√','') SFDY02,decode(CRB003,'-1','√','') SFDY03,decode(CRB004,'-1','√','') SFDY04,decode(CRB005,'-1','√','') SFDY05, decode(CRB006,'-1','√','') SFDY06,decode(CRB007,'-1','√','') SFDY07,decode(CRB008,'-1','√','') SFDY08,decode(CRB009,'-1','√','') SFDY09,decode(CRB010,'-1','√','') SFDY10, decode(CRB011,'-1','√','') SFDY11,decode(CRB012,'-1','√','') SFDY12,decode(CRB013,'-1','√','') SFDY13,decode(CRB014,'-1','√','') SFDY14,decode(CRB015,'-1','√','') SFDY15, decode(CRB016,'-1','√','') SFDY16,decode(CRB017,'-1','√','') SFDY17,decode(CRB018,'-1','√','') SFDY18,decode(CRB019,'-1','√','') SFDY19,decode(CRB020,'-1','√','') SFDY20, decode(CRB021,'-1','√','') SFDY21,decode(CRB022,'-1','√','') SFDY22,decode(CRB023,'-1','√','') SFDY23,decode(CRB024,'-1','√','') SFDY24,decode(CRB025,'-1','√','') SFDY25, decode(CRB026,'-1','√','') SFDY26,decode(CRB027,'-1','√','') SFDY27,decode(CRB028,'-1','√','') SFDY28,decode(CRB029,'-1','√','') SFDY29,decode(CRB030,'-1','√','') SFDY30, decode(CRB031,'-1','√','') SFDY31,decode(CRB032,'-1','√','') SFDY32,decode(CRB033,'-1','√','') SFDY33,decode(CRB034,'-1','√','') SFDY34,decode(CRB035,'-1','√','') SFDY35, decode(CRB036,'-1','√','') SFDY36,decode(CRB037,'-1','√','') SFDY37,decode(CRB038,'-1','√','') SFDY38,decode(CRB039,'-1','√','') SFDY39,decode(CRB040,'-1','√','') SFDY40, decode(CRB041,'-1','√','') SFDY41,decode(CRB042,'-1','√','') SFDY42,decode(CRB043,'-1','√','') SFDY43,decode(CRB044,'-1','√','') SFDY44,decode(CRB045,'-1','√','') SFDY45, decode(CRB046,'-1','√','') SFDY46,decode(CRB047,'-1','√','') SFDY47,decode(CRB048,'-1','√','') SFDY48,decode(CRB049,'-1','√','') SFDY49,decode(CRB050,'-1','√','') SFDY50, decode(CRB051,'-1','√','') SFDY51,decode(CRB052,'-1','√','') SFDY52,decode(CRB053,'-1','√','') SFDY53,decode(CRB054,'-1','√','') SFDY54,decode(CRB055,'-1','√','') SFDY55, decode(CRB056,'-1','√','') SFDY56,decode(CRB057,'-1','√','') SFDY57,decode(CRB058,'-1','√','') SFDY58,decode(CRB059,'-1','√','') SFDY59,decode(CRB060,'-1','√','') SFDY60, decode(CRB061,'-1','√','') SFDY61,decode(CRB062,'-1','√','') SFDY62,decode(CRB063,'-1','√','') SFDY63, decode(CRB064,'-1','√','') SFDY64, to_multi_byte(decode(B.CZRQ00, null, C.BRZJBH, B.SFZH00)) SFZH00, decode(decode(B.CZRQ00, null, C.BRXB00, B.BRXB00),'男','√','') SEX_M, decode(decode(B.CZRQ00, null, C.BRXB00, B.BRXB00),'女','√','') SEX_F, substr(decode(B.CZRQ00, null, C.BRCSRQ, decode(B.BRCSRQ,'18991231',' ',B.BRCSRQ)),1,4)||'年'||substr(decode(B.CZRQ00, null, C.BRCSRQ, decode(B.BRCSRQ,'18991231',' ',B.BRCSRQ)),5,2)||'月'||substr(decode(B.CZRQ00, null, C.BRCSRQ, decode(B.BRCSRQ,'18991231',' ',B.BRCSRQ)),7,2)||'日' CSRQ00, decode(BRLS00,'本县区','√','') BRLS01, decode(BRLS00,'本市其它县区','√','') BRLS02, decode(BRLS00,'本省其它地市','√','') BRLS03, decode(BRLS00,'外省','√','') BRLS04, decode(BRLS00,'港澳台','√','') BRLS05, decode(BRLS00,'外籍','√','') BRLS06, decode(decode(B.CZRQ00,null,C.BRZY00,B.BRZY00),'幼托儿童','√','') BRZY01, decode(decode(B.CZRQ00,null,C.BRZY00,B.BRZY00),'散居儿童','√','') BRZY02, decode(decode(B.CZRQ00,null,C.BRZY00,B.BRZY00),'学生','√','') BRZY03, decode(decode(B.CZRQ00,null,C.BRZY00,B.BRZY00),'教师','√','') BRZY04, decode(decode(B.CZRQ00,null,C.BRZY00,B.BRZY00),'保育员及保姆','√','') BRZY05, decode(decode(B.CZRQ00,null,C.BRZY00,B.BRZY00),'餐饮食品业','√','') BRZY06, decode(decode(B.CZRQ00,null,C.BRZY00,B.BRZY00),'公共场所服务员','√','') BRZY07, decode(decode(B.CZRQ00,null,C.BRZY00,B.BRZY00),'商业服务','√','') BRZY08, decode(decode(B.CZRQ00,null,C.BRZY00,B.BRZY00),'医务人员','√','') BRZY09, decode(decode(B.CZRQ00,null,C.BRZY00,B.BRZY00),'工人','√','') BRZY10, decode(decode(B.CZRQ00,null,C.BRZY00,B.BRZY00),'民工','√','') BRZY11, decode(decode(B.CZRQ00,null,C.BRZY00,B.BRZY00),'农民','√','') BRZY12, decode(decode(B.CZRQ00,null,C.BRZY00,B.BRZY00),'牧民','√','') BRZY13, decode(decode(B.CZRQ00,null,C.BRZY00,B.BRZY00),'渔(船)民','√','') BRZY14, decode(decode(B.CZRQ00,null,C.BRZY00,B.BRZY00),'海员及长途驾驶员','√','') BRZY15, decode(decode(B.CZRQ00,null,C.BRZY00,B.BRZY00),'干部职员','√','') BRZY16, decode(decode(B.CZRQ00,null,C.BRZY00,B.BRZY00),'离退人员','√','') BRZY17, decode(decode(B.CZRQ00,null,C.BRZY00,B.BRZY00),'家务及待业','√','') BRZY18, decode(decode(B.CZRQ00,null,C.BRZY00,B.BRZY00),'不详','√','') BRZY19, decode(decode(B.CZRQ00,null,C.BRZY00,B.BRZY00),'其他','√','') BRZY20, decode(BLFL01,'临床诊断病例','√','') BLFL11, decode(BLFL01,'实验室确诊病例','√','') BLFL12, decode(BLFL01,'疑似病例','√','') BLFL13, decode(BLFL01,'病原携带者','√','') BLFL14, decode(BLFL01,'阳性检测','√','') BLFL15, decode(BLFL02,'急性','√','') BLFL21, decode(BLFL02,'慢性','√','') BLFL22, KPBH00, KPLX00, QTCRB0, DZJBMC, TKYY00, decode(TRIM(KPLX00),'1.初步报告','√','') KPLX01, decode(TRIM(KPLX00),'2.订正报告','√','') KPLX02, B.ZDXXID, B.TKSJ00, e.sfcrb0, D.ZDMC00 GHZD00, (select fbmc00 from bm_brfbb0 where fbbh00=c.fbbh00) fbmc00, substr(SF_BQ_CSRQTONL(decode(B.CZRQ00, null, C.BRCSRQ, B.BRCSRQ)),1,8) AS BRNL0S, decode(e.SFCRB0,'1','甲类','2','乙类','3','丙类','4','其它',' ') CRBLB0, CRB065, CRB066, CRB067, CRB068, CRB069, CRB070, CRB071, CRB072, CRB073, CRB074, CRB075, decode(CRB065,'-1','√','') SFDY65,decode(CRB066,'-1','√','') SFDY66,decode(CRB067,'-1','√','') SFDY67, decode(CRB068,'-1','√','') SFDY68,decode(CRB069,'-1','√','') SFDY69,decode(CRB070,'-1','√','') SFDY70, decode(CRB071,'-1','√','') SFDY71,decode(CRB072,'-1','√','') SFDY72, B.BRSZNL BRSZNL, --病人实足年龄 decode(CRB073,'-1','√','') SFDY73, --实足年龄单位(年) decode(CRB074,'-1','√','') SFDY74, --实足年龄单位(年) decode(CRB075,'-1','√','') SFDY75, --实足年龄单位(年) CRB076, CRB077, CRB078, CRB079, CRB080, CRB081, CRB082, CRB083, decode(CRB076,'-1','√','') SFDY76,decode(CRB077,'-1','√','') SFDY77, decode(CRB078,'-1','√','') SFDY78,decode(CRB079,'-1','√','') SFDY79,decode(CRB080,'-1','√','') SFDY80, decode(CRB081,'-1','√','') SFDY81,decode(CRB082,'-1','√','') SFDY82,decode(CRB083,'-1','√','') SFDY83, nvl(B.SFZF00,'N') SFZF00, --是否作废 CRB088, CRB089, CRB090, decode(CRB088,'-1','√','') SFDY88,decode(CRB089,'-1','√','') SFDY89,decode(CRB090,'-1','√','') SFDY90 from SF_BRXXB0 A, YS_CRBDJ0 B, BM_BRXXB0 C, ys_brzdxx d, xt_icd900 e where A.GHID00=B.MZID00 and A.BRID00=C.BRID00 and d.ICD900=e.ICD900 and d.ghid00=a.ghid00 and d.ZDMC00=e.JBMC00 and instr(d.zdmc00,'复诊')<=0 union select decode(B.CZRQ00, null, '', B.DJID00) DJID00, --登记ID A.GHID00 MZID00, --挂号ID A.GHH000, --挂号号 decode(B.CZRQ00, null, C.BRXM00, B.HZXM00) BRXM00, --患者姓名 B.HEJZXM, --患儿家长姓名 decode(B.CZRQ00, null, C.BRZJBH, B.SFZH00) BRZJBH, --身份证号 decode(B.CZRQ00, null, C.BRXB00, B.BRXB00) BRXB00, --性别 decode(B.CZRQ00, null, C.BRCSRQ, B.BRCSRQ) BRCSRQ, --病人出生日期 substr(b.brcsrq,1,4) csrqN0, --病人出生日期年 substr(b.brcsrq,5,2) csrqY0, --病人出生日期月 substr(b.brcsrq,7,2) csrqR0, --病人出生日期日 decode(B.CZRQ00, null, C.TYDWMC, B.TYDWMC) TYDWMC, --病人工作单位 decode(B.CZRQ00, null, C.BRLXDH, B.BRLXDH) BRLXDH, --病人联系电话 B.BRLS00, --病人隶属： --本县区 本市其它县区 本省其它地市 其他省 港澳台 外籍 B.DZSF00, --地址省份 B.DZDJS0, --地址地级市 B.DZXQ00, --地址县区 B.DZXZJD, --地址乡镇街道 B.DZC000, --地址村 B.DZMPH0, --地址门牌号 decode(trim(nvl(b.brlxdz,'')),'', C.BRJTDZ, B.BRLXDZ) BRLXDZ, --病人联系地址 decode(B.CZRQ00, null, C.BRZY00, B.BRZY00) BRZY00, --病人职业 nvl(B.FBRQ00,nvl((select f.FBRQ00 from YS_BRBQB0 f where a.GHID00=f.GHID00 and rownum = 1 ),to_char(sysdate,'YYYYMMDD'))) FBRQ00, --发病日期 nvl(to_date(B.FBRQ00,'YYYYMMDD'),SYSDATE) FBRQ01, --发病日期01 decode(B.CZRQ00, null, A.JZRQ00, B.ZDRQ00) ZDRQ00, --诊断日期 to_date(decode(B.CZRQ00, null, A.JZRQ00, B.ZDRQ00),'YYYYMMDD') ZDRQ01, --诊断日期01 decode(B.CZRQ00, null, A.JZSJ00, B.ZDSJ00) ZDSJ00, --诊断时间 to_date(decode(B.CZRQ00, null, A.JZSJ00, B.ZDSJ00),'HH24:MI:SS') ZDSJ01, --诊断时间01 decode(B.SWRQ00,' ',substr(B.SWRQ00,1,8)) SWRQ00, --死亡日期 B.BLFL01, --病例分类01 B.BLFL02, --病例分类02 B.TKYS00, --填卡医生 (select zwxm00 from bm_ygbm00 where ygbh00(+) = B.TKYS00) TKYSXM, --填卡医生姓名 B.TKRQ00, --填卡日期 nvl(to_date(B.TKRQ00,'YYYYMMDD'),SYSDATE) TKRQ01, --填卡日期01 decode(B.CZRQ00, null,E.JBMC00, B.JBMC00) JBMC00, --疾病名称 B.JCZCR0, --密切接触者有无相同症状 0:无 1：有 B.BZ0000, --备注 B.CZY000, --操作员 B.CZRQ00, --操作日期 B.CZSJ00, --操作时间 decode(B.CZRQ00,null,'未登记','已登记') SFDJ00, --是否已登记 A.JZKS00, --当前科室 (select bmmc00 from bm_bmbm00 where bmbh00(+) = a.JZks00) DQKSMC, --当前科室名称 B.HY0000, --婚姻 B.WHCD00, --文化程度 B.CRLY00, --传染来源 substr(Trim(SF_XT_CSRQTONL(B.BRCSRQ,A.JZRQ00)),1,8) NL0000, nvl(B.SFSW00,'N') SFSW00, --是否死亡 A.JZRQ00, --就诊日期 nvl(B.SFQR00,'N') SFQR00, --是否确认 B.QRR000, --确认人 (select zwxm00 from bm_ygbm00 where ygbh00(+) = B.QRR000) QRRXM0, --确认人姓名 decode((select count(1) from BQ_JSXX00 where BRid00=A.BRid00),0,'0','1') SFYYE0, --是否有婴儿 0：无 1：有 d.JZYSXM, --诊断医生 d.JZYS00, SF_BQ_CSRQTONL(decode(B.CZRQ00, null, C.BRCSRQ, B.BRCSRQ)) BRNL00, CRB001,CRB002,CRB003,CRB004,CRB005,CRB006,CRB007,CRB008,CRB009,CRB010, CRB011,CRB012,CRB013,CRB014,CRB015,CRB016,CRB017,CRB018,CRB019,CRB020, CRB021,CRB022,CRB023,CRB024,CRB025,CRB026,CRB027,CRB028,CRB029,CRB030, CRB031,CRB032,CRB033,CRB034,CRB035,CRB036,CRB037,CRB038,CRB039,CRB040, CRB041,CRB042,CRB043,CRB044,CRB045,CRB046,CRB047,CRB048,CRB049,CRB050, CRB051,CRB052,CRB053,CRB054,CRB055,CRB056,CRB057,CRB058,CRB059,CRB060, CRB061,CRB062,CRB063,CRB064, decode(CRB001,'-1','√','') SFDY01,decode(CRB002,'-1','√','') SFDY02,decode(CRB003,'-1','√','') SFDY03,decode(CRB004,'-1','√','') SFDY04,decode(CRB005,'-1','√','') SFDY05, decode(CRB006,'-1','√','') SFDY06,decode(CRB007,'-1','√','') SFDY07,decode(CRB008,'-1','√','') SFDY08,decode(CRB009,'-1','√','') SFDY09,decode(CRB010,'-1','√','') SFDY10, decode(CRB011,'-1','√','') SFDY11,decode(CRB012,'-1','√','') SFDY12,decode(CRB013,'-1','√','') SFDY13,decode(CRB014,'-1','√','') SFDY14,decode(CRB015,'-1','√','') SFDY15, decode(CRB016,'-1','√','') SFDY16,decode(CRB017,'-1','√','') SFDY17,decode(CRB018,'-1','√','') SFDY18,decode(CRB019,'-1','√','') SFDY19,decode(CRB020,'-1','√','') SFDY20, decode(CRB021,'-1','√','') SFDY21,decode(CRB022,'-1','√','') SFDY22,decode(CRB023,'-1','√','') SFDY23,decode(CRB024,'-1','√','') SFDY24,decode(CRB025,'-1','√','') SFDY25, decode(CRB026,'-1','√','') SFDY26,decode(CRB027,'-1','√','') SFDY27,decode(CRB028,'-1','√','') SFDY28,decode(CRB029,'-1','√','') SFDY29,decode(CRB030,'-1','√','') SFDY30, decode(CRB031,'-1','√','') SFDY31,decode(CRB032,'-1','√','') SFDY32,decode(CRB033,'-1','√','') SFDY33,decode(CRB034,'-1','√','') SFDY34,decode(CRB035,'-1','√','') SFDY35, decode(CRB036,'-1','√','') SFDY36,decode(CRB037,'-1','√','') SFDY37,decode(CRB038,'-1','√','') SFDY38,decode(CRB039,'-1','√','') SFDY39,decode(CRB040,'-1','√','') SFDY40, decode(CRB041,'-1','√','') SFDY41,decode(CRB042,'-1','√','') SFDY42,decode(CRB043,'-1','√','') SFDY43,decode(CRB044,'-1','√','') SFDY44,decode(CRB045,'-1','√','') SFDY45, decode(CRB046,'-1','√','') SFDY46,decode(CRB047,'-1','√','') SFDY47,decode(CRB048,'-1','√','') SFDY48,decode(CRB049,'-1','√','') SFDY49,decode(CRB050,'-1','√','') SFDY50, decode(CRB051,'-1','√','') SFDY51,decode(CRB052,'-1','√','') SFDY52,decode(CRB053,'-1','√','') SFDY53,decode(CRB054,'-1','√','') SFDY54,decode(CRB055,'-1','√','') SFDY55, decode(CRB056,'-1','√','') SFDY56,decode(CRB057,'-1','√','') SFDY57,decode(CRB058,'-1','√','') SFDY58,decode(CRB059,'-1','√','') SFDY59,decode(CRB060,'-1','√','') SFDY60, decode(CRB061,'-1','√','') SFDY61,decode(CRB062,'-1','√','') SFDY62,decode(CRB063,'-1','√','') SFDY63, decode(CRB064,'-1','√','') SFDY64, to_multi_byte(decode(B.CZRQ00, null, C.BRZJBH, B.SFZH00)) SFZH00, decode(decode(B.CZRQ00, null, C.BRXB00, B.BRXB00),'男','√','') SEX_M, decode(decode(B.CZRQ00, null, C.BRXB00, B.BRXB00),'女','√','') SEX_F, substr(decode(B.CZRQ00, null, C.BRCSRQ, decode(B.BRCSRQ,'18991231',' ',B.BRCSRQ)),1,4)||'年'||substr(decode(B.CZRQ00, null, C.BRCSRQ, decode(B.BRCSRQ,'18991231',' ',B.BRCSRQ)),5,2)||'月'||substr(decode(B.CZRQ00, null, C.BRCSRQ, decode(B.BRCSRQ,'18991231',' ',B.BRCSRQ)),7,2)||'日' CSRQ00, decode(BRLS00,'本县区','√','') BRLS01, decode(BRLS00,'本市其它县区','√','') BRLS02, decode(BRLS00,'本省其它地市','√','') BRLS03, decode(BRLS00,'外省','√','') BRLS04, decode(BRLS00,'港澳台','√','') BRLS05, decode(BRLS00,'外籍','√','') BRLS06, decode(decode(B.CZRQ00,null,C.BRZY00,B.BRZY00),'幼托儿童','√','') BRZY01, decode(decode(B.CZRQ00,null,C.BRZY00,B.BRZY00),'散居儿童','√','') BRZY02, decode(decode(B.CZRQ00,null,C.BRZY00,B.BRZY00),'学生','√','') BRZY03, decode(decode(B.CZRQ00,null,C.BRZY00,B.BRZY00),'教师','√','') BRZY04, decode(decode(B.CZRQ00,null,C.BRZY00,B.BRZY00),'保育员及保姆','√','') BRZY05, decode(decode(B.CZRQ00,null,C.BRZY00,B.BRZY00),'餐饮食品业','√','') BRZY06, decode(decode(B.CZRQ00,null,C.BRZY00,B.BRZY00),'公共场所服务员','√','') BRZY07, decode(decode(B.CZRQ00,null,C.BRZY00,B.BRZY00),'商业服务','√','') BRZY08, decode(decode(B.CZRQ00,null,C.BRZY00,B.BRZY00),'医务人员','√','') BRZY09, decode(decode(B.CZRQ00,null,C.BRZY00,B.BRZY00),'工人','√','') BRZY10, decode(decode(B.CZRQ00,null,C.BRZY00,B.BRZY00),'民工','√','') BRZY11, decode(decode(B.CZRQ00,null,C.BRZY00,B.BRZY00),'农民','√','') BRZY12, decode(decode(B.CZRQ00,null,C.BRZY00,B.BRZY00),'牧民','√','') BRZY13, decode(decode(B.CZRQ00,null,C.BRZY00,B.BRZY00),'渔(船)民','√','') BRZY14, decode(decode(B.CZRQ00,null,C.BRZY00,B.BRZY00),'海员及长途驾驶员','√','') BRZY15, decode(decode(B.CZRQ00,null,C.BRZY00,B.BRZY00),'干部职员','√','') BRZY16, decode(decode(B.CZRQ00,null,C.BRZY00,B.BRZY00),'离退人员','√','') BRZY17, decode(decode(B.CZRQ00,null,C.BRZY00,B.BRZY00),'家务及待业','√','') BRZY18, decode(decode(B.CZRQ00,null,C.BRZY00,B.BRZY00),'不详','√','') BRZY19, decode(decode(B.CZRQ00,null,C.BRZY00,B.BRZY00),'其他','√','') BRZY20, decode(BLFL01,'临床诊断病例','√','') BLFL11, decode(BLFL01,'实验室确诊病例','√','') BLFL12, decode(BLFL01,'疑似病例','√','') BLFL13, decode(BLFL01,'病原携带者','√','') BLFL14, decode(BLFL01,'阳性检测','√','') BLFL15, decode(BLFL02,'急性','√','') BLFL21, decode(BLFL02,'慢性','√','') BLFL22, KPBH00, KPLX00, QTCRB0, DZJBMC, TKYY00, decode(TRIM(KPLX00),'1.初步报告','√','') KPLX01, decode(TRIM(KPLX00),'2.订正报告','√','') KPLX02, B.ZDXXID, B.TKSJ00, e.sfcrb0, D.ZDMC00 GHZD00, (select fbmc00 from bm_brfbb0 where fbbh00=c.fbbh00) fbmc00, substr(SF_BQ_CSRQTONL(decode(B.CZRQ00, null, C.BRCSRQ, B.BRCSRQ)),1,8) AS BRNL0S, decode(e.SFCRB0,'1','甲类','2','乙类','3','丙类','4','其它',' ') CRBLB0, CRB065, CRB066, CRB067, CRB068, CRB069, CRB070, CRB071, CRB072, CRB073, CRB074, CRB075, decode(CRB065,'-1','√','') SFDY65,decode(CRB066,'-1','√','') SFDY66,decode(CRB067,'-1','√','') SFDY67, decode(CRB068,'-1','√','') SFDY68,decode(CRB069,'-1','√','') SFDY69,decode(CRB070,'-1','√','') SFDY70, decode(CRB071,'-1','√','') SFDY71,decode(CRB072,'-1','√','') SFDY72, B.BRSZNL BRSZNL, --病人实足年龄 decode(CRB073,'-1','√','') SFDY73, --实足年龄单位(年) decode(CRB074,'-1','√','') SFDY74, --实足年龄单位(年) decode(CRB075,'-1','√','') SFDY75, --实足年龄单位(年) CRB076, CRB077, CRB078, CRB079, CRB080, CRB081, CRB082, CRB083, decode(CRB076,'-1','√','') SFDY76,decode(CRB077,'-1','√','') SFDY77, decode(CRB078,'-1','√','') SFDY78,decode(CRB079,'-1','√','') SFDY79,decode(CRB080,'-1','√','') SFDY80, decode(CRB081,'-1','√','') SFDY81,decode(CRB082,'-1','√','') SFDY82,decode(CRB083,'-1','√','') SFDY83, nvl(B.SFZF00,'N') SFZF00, --是否作废 CRB088, CRB089, CRB090, decode(CRB088,'-1','√','') SFDY88,decode(CRB089,'-1','√','') SFDY89,decode(CRB090,'-1','√','') SFDY90 from SF_BRXXB0 A, YS_CRBDJ0 B, BM_BRXXB0 C, ys_brzdxx d, xt_icd900 e,BM_TYZD00 F where A.GHID00=B.MZID00 and A.BRID00=C.BRID00 and a.ghid00=d.ghid00 and instr(d.zdmc00,'复诊')<=0 and ((D.ICD900 is null) or (exists (select 1 from XT_ICD900 where ICD900=d.ICD900 and JBMC00<>d.ZDMC00))) and F.ZDMC00='门诊自定义诊断传染病字眼' and F.BH0000 is not null and F.YXBZ00='1' and instr(D.ZDMC00,F.MC0000)>0 and rpad(F.BH0000,10)=E.ICD900 / --%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%