-- Start of DDL Script for View SD_HOSPITAL.VW_YS_MZCRDJ_ALL_BAK -- Generated 14-九月-2011 15:38:38 from SD_HOSPITAL@SMFYTEST CREATE OR REPLACE VIEW vw_ys_mzcrdj_all ( 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, jzsj00, 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, 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, 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, zzdid0, zzdmc0, fzzdid, fzzdmc, csrq01, xzzdid, xzzdmc, cfz000 ) AS SELECT DECODE(B.CZRQ00, NULL, '', B.DJID00) DJID00, --登记ID A.GHID00 MZID00, --挂号ID A.GHH000, --挂号号 ---=====*********修改 --DECODE(B.CZRQ00, NULL, C.BRXM00, B.HZXM00) C.BRXM00 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, --死亡日期 -- nvl(b.SWRQ00,SYSDATE,TO_DATE(B.SWRQ00,'YYYYMMDD')) SWRQ01, --死亡日期01 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 --B.JBMC00, --疾病名称 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_YF_CSRQTONL(B.BRCSRQ,A.JZRQ00)),1,8) NL0000, NVL(B.SFSW00,'N') SFSW00, --是否死亡 A.JZRQ00, --就诊日期 A.JZSJ00, --就诊时间 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, 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, 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, B.BRCSRQ),1,4)||'年'||SUBSTR(DECODE(B.CZRQ00, NULL, C.BRCSRQ, B.BRCSRQ),5,2)||'月'||SUBSTR(DECODE(B.CZRQ00, NULL, C.BRCSRQ, 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, --=====******修改 --decode(e.sfcrb0,1,'Y','N') sfcrb0 , decode(e.sfcrb0,'1','Y','2','Y','3','Y',decode((select sfcrb0 from xt_icd900 where icd900=a.xzzdid),'1','Y','2','Y','3','Y','N')) 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 , (select nvl(e.ICD900,'') from XT_ICD900 where d.ICD900=ICD900 and d.ZZDBZ0='3' and rownum=1) as ZZDID0, (select nvl(e.JBMC00,'') from XT_ICD900 where d.ICD900=ICD900 and d.ZZDBZ0='3' and rownum=1) as ZZDMC0, (select nvl(e.ICD900,'') from XT_ICD900 where d.ICD900=ICD900 and d.ZZDBZ0<>'3' and rownum=1) as FZZDID, (select nvl(e.JBMC00,'') from XT_ICD900 where d.ICD900=ICD900 and d.ZZDBZ0<>'3' and rownum=1) as FZZDMC, TO_DATE(DECODE(B.CZRQ00, NULL, C.BRCSRQ, B.BRCSRQ),'YYYYMMDD') CSRQ01, --病人出生日期01 A.XZZDID , -- decode((select sfcrb0 from xt_icd900 where icd900=a.xzzdid),'1','Y','2','Y','3','Y','N') xzcrb0, A.XZZDMC , decode(A.SFCZ00,'1','复诊','初诊') CFZ000 --初复诊 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 instr(d.zdmc00,'复诊')<=0 / -- End of DDL Script for View SD_HOSPITAL.VW_YS_MZCRDJ_ALL_BAK