create table SD_HOSPITAL.XT_ICDDYB_BAK as select * from SD_HOSPITAL.XT_ICDDYB; alter table SD_HOSPITAL.XT_ICDDYB add (YT0000 number(2) default 0); comment on column SD_HOSPITAL.XT_ICDDYB.YT0000 is '用途(0:公立医院绩效 1:卫统填报 2:互联互通)'; alter table SD_HOSPITAL.XT_ICDDYB add (YTMC00 varchar2(200)); comment on column SD_HOSPITAL.XT_ICDDYB.YTMC00 is '用途名称'; alter table SD_HOSPITAL.XT_ICDDYB add (SFYX00 char(1) default '1'); comment on column SD_HOSPITAL.XT_ICDDYB.SFYX00 is '0:无效 1:有效'; commit; create or replace function SF_BA_GETLCZDB41BATB -- MODIFICATION HISTORY -- Person Date Comments -- linng 20210118 ( PZYID00 in number , PZDLB00 in char , PFHLX00 in number , --返回类型 0 ZDM000 1 RYBQ00 2 ZDMC00 3 ZLXG00 PXH0000 in number ) return varchar2 is VZDMC00 BQ_BRZDXX.ZDMC00%type; --按顺序号取诊断的编码，主要是BQ_BRZDXX.PLXH00这个字段的值不靠谱，导致传接口有的时候数据传不过去 begin begin if Pfhlx00=0 then select ZDM000 into Vzdmc00 from (select rownum XH0000,a.* from (select dy.icd101 ZDM000 from bq_baszdb zd ,xt_icddyb dy where yt0000=0 and dy.icd100 = zd.zdm000 and　dy.yt0000='1' and zd.ZYID00=Pzyid00 and zd.zdm000 is not null and zd.zdm000 not in (select icd100 from xt_fjm000) and zd.ZDLB00=Pzdlb00 order by zd.PLXH00,zd.ID0000) a) where XH0000=Pxh0000; elsif Pfhlx00=1 then select decode(RYBQ00,'有','1','临床未确定','2','情况不明','3','无','4','1','1','2','2','3','3','4','4') into Vzdmc00 from (select rownum XH0000,a.* from (select RYBQ00 from bq_baszdb zd where ZYID00=Pzyid00 and zd.zdm000 not in (select icd100 from xt_fjm000) and ZDLB00=Pzdlb00 order by PLXH00,ID0000) a) where XH0000=Pxh0000; elsif Pfhlx00=2 then select substr(jbmc00,1,100) into Vzdmc00 from (select rownum XH0000,a.* from (select zd.zdmc00 jbmc00 from bq_baszdb zd where ZYID00=Pzyid00 and zd.ZDLB00=Pzdlb00 and zd.zdm000 not in (select icd100 from xt_fjm000) order by PLXH00,ID0000) a) where XH0000=Pxh0000; elsif Pfhlx00=3 then select decode(ZLXG00,'1','1','2','2','3','3','4','4','5','9','6','9','7','9','8','9') into Vzdmc00 from (select rownum XH0000,a.* from (select ZLXG00 from bq_baszdb zd where ZYID00=Pzyid00 and zd.zdm000 not in (select icd100 from xt_fjm000) and ZDLB00=Pzdlb00 order by PLXH00,ID0000) a) where XH0000=Pxh0000; elsif Pfhlx00=5 then select ZDRQ00 into Vzdmc00 from (select rownum XH0000,a.* from (select ZDRQ00 from bq_baszdb zd where ZYID00=PZYID00 and zd.zdm000 not in (select icd100 from xt_fjm000) and ZDLB00=PZDLB00 order by PLXH00,ID0000) a) where XH0000=PXH0000; elsif Pfhlx00=6 then select decode(ZLXG00,'1','1','2','2','3','3','4','4','9') into Vzdmc00 from (select rownum XH0000,a.* from (select ZLXG00 from bq_baszdb zd where ZYID00=Pzyid00 and zd.zdm000 not in (select icd100 from xt_fjm000) and ZDLB00=Pzdlb00 order by PLXH00,ID0000) a) where XH0000=Pxh0000; end if; exception when others then Vzdmc00 := ''; end; return VZDMC00; end; create or replace view vw_zybasy_wt41bz as select trim((select YYQC00 from XT_YYXX00 where rownum=1)) USERNAME, --医疗机构名称 decode(A.JFFS00,'城镇职工基本医疗保险','01','城镇居民基本医疗保险','02','社会基本医疗保险','02', '新型农村合作医疗','03','新农合医疗','03','贫困救助','04', '商业保险','05','全公费','06','公费医疗','06','全自费','07','自费医疗','07','自费','07','08') YLFKFS,--医疗付款方式 A.JKDAH0 JKKH, --健康卡号 A.ZYCS00 ZYCS,--住院次数 A.BAH000 BAH, --病案号 A.BRXM00 XM, --姓名 decode(A.BRXB00,'女','2','男','1','0') XB,--性别 A.BRCSRQ CSRQ, --出生日期 -- substr(SF_BQ_CSRQTONL_DZBL(A.BRCSRQ,A.RYRQ00),1,instr(SF_BQ_CSRQTONL_DZBL(A.BRCSRQ,A.RYRQ00),'岁')-1) NL, --年龄 A.Brnl00 NL, --年龄 nvl((select GJDM00 from XT_GJ0000 where MC0000=A.GJ0000),'CHN') GJ, --国籍 -- round(decode(A.BRNL00,null,nvl(A.BRNLMM,0)+nvl(A.BRNLDD,0)/30,0,nvl(A.BRNLMM,0)+nvl(A.BRNLDD,0)/30),2) BZYZSNL, --（年龄不足1周岁的）年龄(月) floor(decode(A.BRNL00,null,nvl(A.BRNLDD,0)/30,0,nvl(A.BRNLDD,0)/30)) BZYZSNL, --（年龄不足1周岁的）年龄(月) A.YECSTZ XSETZ, --新生儿出生体重(克) A.YERYTZ XSERYTZ, --新生儿入院体重（克） A.CSDS00 CSD, --出生地 A.JGSF00 GG,--籍贯 decode(A.MZ0000,'汉','01','畲','22',decode((select MZ.DM0000 from XT_MZ0000 MZ where MZ.MC0000=A.MZ0000),'97','99',(select MZ.DM0000 from XT_MZ0000 MZ where MZ.MC0000=A.MZ0000))) MZ, --民族 -- decode(A.SFZH00,null,'000000000000000','-','000000000000000') SFZH,--身份证号 decode( SP_SF_CHECKSFZH(upper(A.SFZH00)),'1',upper(A.SFZH00),'000000000000000') SFZH,--身份证号 -- nvl((select DM0000 from BA_ZDDY00 ZD where ZD.VALUE0=A.ZY0000 and ZDBM00='WTZY00'),90) ZY,--职业 decode(A.ZY0000,'国家公务员','11','专业技术人员','13','职员','17','企业管理人员','21','工人','24','农民','27','学生','31','现役军人','37','自由职业者','51','个体经营者','54','无业人员','70','退(离)休人员','80','其他','90','90') ZY,--职业 decode(A.HYZK00,'未婚','10','已婚','20','初婚','21','再婚','22','复婚','23','丧偶','30','离婚','40','其他','90','90') HY,--婚姻 -- case when A.XZZXC0='-' then A.XZZSF0||'省'||A.XZZDQ0||'市'||A.XZZXS0||'区' when A.XZZDQ0='-' then A.XZZSF0 else A.XZZSF0||'省'||A.XZZDQ0||'市'||A.XZZXS0||'区'||A.XZZXC0 end XZZ, --现住址 A.XZZXC0 XZZ, --现住址 A.Xzzdh0 DH, --电话 A.JTYB00 YB1, --邮编 (现住址邮政编码) -- case when A.JTDZDQ='-' then A.JTDZSF || '省' when A.JTDZ01='-' then A.JTDZSF||'省'||A.JTDZDQ||'市'||A.JTDZXS||'区' else nvl(A.JTDZSF||'省'||A.JTDZDQ||'市'||A.JTDZXS||'区'||A.JTDZ01,'-') end HKDZ, A.JTDZ00 HKDZ, --户口地址 A.JTYB00 YB2, --邮编 A.GZDW00 GZDWJDZ, --工作单位及地址 A.GZDH00 DWDH, --单位电话 A.GZYB00 YB3, --邮编 (工作邮政编码) A.LXRXM0 LXRXM, --联系人姓名 decode(A.LXRGX0,'本人或户主','0','配偶','1','子','2','女','3','孙子、孙女或外孙子、外孙女','4','父子','5','父女','5','母子','5','母女','5','父母','5','祖父母或外祖父母','6','兄、弟、姐、妹','7','兄弟','7','兄妹','7','姐妹','7','姐弟','7','其他','8','8') GX,--关系 nvl(A.LXRDZ0||A.LXRDZ1,'-') DZ,--联系人地址 A.LXRDH0 DH2,--联系人电话 DECODE(A.RYTJ00,'1','1','2','2','3','3','9','9') RYTJ,--入院途径 (substrb(A.RYRQ00,1,4)||substrb(A.RYRQ00,5,2)||substrb(A.RYRQ00,7,2)) RYSJ, --入院时间 substrb(A.RYSJ00,1,2) RYSJS,--时 --A.ZLLB00 ZLLB,--治疗类别 (select DM0000 from ZS_FOKSDY H where H.BH0000=A.RYKB00 and rownum<=1) RYKB,--入院科别 A.RYBF00 RYBF,--入院病房 (select DM0000 from ZS_FOKSDY H where H.BH0000=A.ZKKB00 and rownum<=1)ZKKB,--转科科别 substrb(A.CYRQ00,1,4)||substrb(A.CYRQ00,5,2)||substrb(A.CYRQ00,7,2) CYSJ,--出院时间 substrb(A.CYSJ00,1,2) CYSJS,--时 (select DM0000 from ZS_FOKSDY H where H.BH0000=A.CYKB00 and rownum<=1) CYKB, --出院科别 A.CYBF00 CYBF,--出院病房 A.ZYTS00 SJZYTS,--实际住院(天) SF_BA_GETLCZDB41(A.LSH000,'1',2,1) MZZD,--门(急)诊诊断(西医诊断) SF_BA_GETLCZDB41BATB(A.LSH000,'1',0,1) JBBM,--疾病编码 SF_BA_GETLCZDB41(A.LSH000,'3',2,1) ZYZD,--主要诊断 SF_BA_GETLCZDB41BATB(A.LSH000,'3',0,1) JBDM,--疾病代码 SF_BA_GETLCZDB41(A.LSH000,'3',1,1) RYBQ,--主诊断院病情 SF_BA_GETLCZDB41(A.LSH000,'4',2,8) QTZD8, SF_BA_GETLCZDB41(A.LSH000,'4',0,8) JBDM8, SF_BA_GETLCZDB41(A.LSH000,'4',1,8) RYBQ8, SF_BA_GETLCZDB41(A.LSH000,'4',2,1) QTZD1,--其他诊断3出院西医诊断 SF_BA_GETLCZDB41(A.LSH000,'4',0,1) JBDM1,--主证3疾病编码 SF_BA_GETLCZDB41(A.LSH000,'4',1,1) RYBQ1, --主证3住入院病情 SF_BA_GETLCZDB41(A.LSH000,'4',2,9) QTZD9, --其他诊断4出院西医诊断 SF_BA_GETLCZDB41(A.LSH000,'4',0,9) JBDM9,--主证4疾病编码 SF_BA_GETLCZDB41(A.LSH000,'4',1,9) RYBQ9, --主证4住入院病情 SF_BA_GETLCZDB41(A.LSH000,'4',2,2) QTZD2,--其他诊断5出院西医诊断 SF_BA_GETLCZDB41(A.LSH000,'4',0,2) JBDM2,--主证5疾病编码 SF_BA_GETLCZDB41(A.LSH000,'4',1,2) RYBQ2, --主证5住入院病情 SF_BA_GETLCZDB41(A.LSH000,'4',2,10) QTZD10,--其他诊断6出院西医诊断 SF_BA_GETLCZDB41(A.LSH000,'4',0,10) JBDM10,--主证6疾病编码 SF_BA_GETLCZDB41(A.LSH000,'4',1,10) RYBQ10, --主证6住入院病情 SF_BA_GETLCZDB41(A.LSH000,'4',2,3) QTZD3,--其他诊断7出院西医诊断 SF_BA_GETLCZDB41(A.LSH000,'4',0,3) JBDM3,--主证7疾病编码 SF_BA_GETLCZDB41(A.LSH000,'4',1,3) RYBQ3, --主证7住入院病情 SF_BA_GETLCZDB41(A.LSH000,'4',2,11) QTZD11,--其他诊断7出院西医诊断 SF_BA_GETLCZDB41(A.LSH000,'4',0,11) JBDM11,--主证7疾病编码 SF_BA_GETLCZDB41(A.LSH000,'4',1,11) RYBQ11, --主证7住入院病情 SF_BA_GETLCZDB41(A.LSH000,'4',2,4) QTZD4,--其他诊断7出院西医诊断 SF_BA_GETLCZDB41(A.LSH000,'4',0,4) JBDM4, SF_BA_GETLCZDB41(A.LSH000,'4',1,4) RYBQ4, --主证7住入院病情 SF_BA_GETLCZDB41(A.LSH000,'4',2,12) QTZD12,--其他诊断7出院西医诊断 SF_BA_GETLCZDB41(A.LSH000,'4',0,12) JBDM12,--主证7疾病编码 SF_BA_GETLCZDB41(A.LSH000,'4',1,12) RYBQ12, --主证7住入院病情 SF_BA_GETLCZDB41(A.LSH000,'4',2,5) QTZD5,--其他诊断7出院西医诊断 SF_BA_GETLCZDB41(A.LSH000,'4',0,5) JBDM5,--主证7疾病编码 SF_BA_GETLCZDB41(A.LSH000,'4',1,5) RYBQ5, --主证7住入院病情 SF_BA_GETLCZDB41(A.LSH000,'4',2,13) QTZD13,--其他诊断7出院西医诊断 SF_BA_GETLCZDB41(A.LSH000,'4',0,13) JBDM13,--主证7疾病编码 SF_BA_GETLCZDB41(A.LSH000,'4',1,13) RYBQ13, --主证7住入院病情 SF_BA_GETLCZDB41(A.LSH000,'4',2,6) QTZD6,--其他诊断7出院西医诊断 SF_BA_GETLCZDB41(A.LSH000,'4',0,6) JBDM6,--主证7疾病编码 SF_BA_GETLCZDB41(A.LSH000,'4',1,6) RYBQ6, --主证7住入院病情 SF_BA_GETLCZDB41(A.LSH000,'4',2,7) QTZD7,--其他诊断7出院西医诊断 SF_BA_GETLCZDB41(A.LSH000,'4',0,7) JBDM7,--主证7疾病编码 SF_BA_GETLCZDB41(A.LSH000,'4',1,7) RYBQ7, --主证7住入院病情 /* A.SSZDWY WBYY, --中毒的外部原因 A.SSZDBM H23,--疾病编码 A.BLZD00 BLZD,--病理诊断 A.BLZDBM JBMM,--疾病编码*/ (select SSZDWY from ba_baszyb where LSH000=A.LSH000) WBYY, --中毒的外部原因 (select SSZDBM from ba_baszyb where LSH000=A.LSH000) H23,--疾病编码 (select BLZD00 from ba_baszyb where LSH000=A.LSH000) BLZD,--病理诊断 (select BLZDBM from ba_baszyb where LSH000=A.LSH000) JBMM,--疾病编码 A.Blh000 BLH,--病理号 A.SFYWGM YWGM, --药物过敏 A.GMYW00 GMYW,--过敏药物西医诊断 decode(A.SFSJ00,'是','1','否','2') SWHZSJ,--死亡患者尸检 decode(A.XX0000,'A型','1','B型','2','O型','3','AB型','4','未检','6','A','1','B','2','O','3','AB','4','不详','5','未查','6','6') XX, --血型 decode(A.RH0000,'阴性','1','阳性','2','不详','3','未做','4','阴','1','阳','2','未查','4','4') RH, --RH A.KZRMC0 KZR,--科主任 A.FZRMC0 ZRYS,--副主任 A.ZZYSMC ZZYS,--主治医师 A.ZYYSMC ZYYS,--住院医师 A.ZRHSMC ZRHS, --责任护士 A.JXYSMC JXYS, --进修医师 A.YJSMC0 SXYS,--实习医师 A.SHRXM0 BMY,--编码员 decode(A.BAZL00,'甲','1','乙','2','丙','3') BAZL,--病案质量 A.ZKYSMC ZKYS,--质控医师 A.ZKHSMC ZKHS, --质控护士 A.ZKRQ00 ZKRQ,--质控日期 SF_BA_GETBABRSSXX20(A.LSH000,0,1) SSJCZBM1,--手术操作编码1 SF_BA_GETBABRSSXX20(A.LSH000,1,1) SSJCZRQ1,--手术操作日期1 decode(SF_BA_GETBABRSSXX20(A.LSH000,2,1),'-','','1','1','2','2','3','3','4','4') SSJB1,--手术级别1 SF_BA_GETBABRSSXX20(A.LSH000,3,1) SSJCZMC1,--手术操作名称1 SF_BA_GETBABRSSXX20(A.LSH000,6,1) SZ1,--术者1 SF_BA_GETBABRSSXX20(A.LSH000,7,1) YZ1,--I助1 SF_BA_GETBABRSSXX20(A.LSH000,8,1) EZ1,--II助1 SF_BA_GETBABRSSXX20(A.LSH000,14,1) QKDJ1, SF_BA_GETBABRSSXX20(A.LSH000,13,1) QKYHLB1, SF_BA_GETBABRSSXX20(A.LSH000,16,1) MZFS1,--麻醉方式1 SF_BA_GETBABRSSXX20(A.LSH000,12,1) MZYS1,--麻醉医师1 SF_BA_GETBABRSSXX20(A.LSH000,0,2) SSJCZBM2,--手术操作编码2 SF_BA_GETBABRSSXX20(A.LSH000,1,2) SSJCZRQ2,--手术操作日期2 decode(SF_BA_GETBABRSSXX20(A.LSH000,2,2),'-','','1','1','2','2','3','3','4','4') SSJB2,--手术级别2 SF_BA_GETBABRSSXX20(A.LSH000,3,2) SSJCZMC2,--手术操作名称2 SF_BA_GETBABRSSXX20(A.LSH000,6,2) SZ2,--术者2 P4916 SF_BA_GETBABRSSXX20(A.LSH000,7,2) YZ2,--I助2 P4917 SF_BA_GETBABRSSXX20(A.LSH000,8,2) EZ2,--II助2 SF_BA_GETBABRSSXX20(A.LSH000,14,2) QKDJ2, SF_BA_GETBABRSSXX20(A.LSH000,13,2) QKYHLB2, SF_BA_GETBABRSSXX20(A.LSH000,16,2) MZFS2,--麻醉方式1 SF_BA_GETBABRSSXX20(A.LSH000,12,2) MZYS2,--麻醉医师2 SF_BA_GETBABRSSXX20(A.LSH000,0,3) SSJCZBM3,--手术操作编码3 SF_BA_GETBABRSSXX20(A.LSH000,1,3) SSJCZRQ3,--手术操作日期3 decode(SF_BA_GETBABRSSXX20(A.LSH000,2,3),'-','','1','1','2','2','3','3','4','4') SSJB3,--手术级别3 SF_BA_GETBABRSSXX20(A.LSH000,3,3) SSJCZMC3,--手术操作名称3 SF_BA_GETBABRSSXX20(A.LSH000,6,3) SZ3,--术者3 SF_BA_GETBABRSSXX20(A.LSH000,7,3) YZ3,--I助3 SF_BA_GETBABRSSXX20(A.LSH000,8,3) EZ3,--II助3 SF_BA_GETBABRSSXX20(A.LSH000,14,3) QKDJ3, SF_BA_GETBABRSSXX20(A.LSH000,13,3) QKYHLB3, SF_BA_GETBABRSSXX20(A.LSH000,16,3) MZFS3,--麻醉方式3 SF_BA_GETBABRSSXX20(A.LSH000,12,3) MZYS3,--麻醉医师3 /* SF_BA_GETBABRSSXX(A.LSH000,16,4) SSJCZBM4,--手术操作编码4 SF_BA_GETBABRSSXX(A.LSH000,17,4) SSJCZRQ4,--手术操作日期4 SF_BA_GETBABRSSXX(A.LSH000,2,4) SSJB4,--手术级别4 SF_BA_GETBABRSSXX(A.LSH000,3,4) SSJCZMC4,--手术操作名称4 SF_BA_GETBABRSSXX(A.LSH000,6,4) SZ4,--术者4 SF_BA_GETBABRSSXX(A.LSH000,7,4) YZ4,--I助4 SF_BA_GETBABRSSXX(A.LSH000,8,4) EZ4,--II助4 SF_BA_GETBABRSSXX(A.LSH000,18,4) QKDJ4, SF_BA_GETBABRSSXX(A.LSH000,19,4) QKYHLB4, SF_BA_GETBABRSSXX(A.LSH000,9,4) MZFS4,--麻醉方式4 SF_BA_GETBABRSSXX(A.LSH000,12,4) MZYS4,--麻醉医师4 SF_BA_GETBABRSSXX(A.LSH000,16,5) SSJCZBM5,--手术操作编码5 SF_BA_GETBABRSSXX(A.LSH000,17,5) SSJCZRQ5,--手术操作日期5 SF_BA_GETBABRSSXX(A.LSH000,2,5) SSJB5,--手术级别5 SF_BA_GETBABRSSXX(A.LSH000,3,5) SSJCZMC5,--手术操作名称5 SF_BA_GETBABRSSXX(A.LSH000,6,5) SZ5,--术者5 SF_BA_GETBABRSSXX(A.LSH000,7,5) YZ5,--I助5 SF_BA_GETBABRSSXX(A.LSH000,8,5) EZ5,--II助5 SF_BA_GETBABRSSXX(A.LSH000,18,5) QKDJ5, SF_BA_GETBABRSSXX(A.LSH000,19,5) QKYHLB5, SF_BA_GETBABRSSXX(A.LSH000,9,5) MZFS5,--麻醉方式5 SF_BA_GETBABRSSXX(A.LSH000,12,5) MZYS5,--麻醉医师5 SF_BA_GETBABRSSXX(A.LSH000,16,6) SSJCZBM6,--手术操作编码6 SF_BA_GETBABRSSXX(A.LSH000,17,6) SSJCZRQ6,--手术操作日期6 SF_BA_GETBABRSSXX(A.LSH000,2,6) SSJB6,--手术级别6 P825 SF_BA_GETBABRSSXX(A.LSH000,3,6) SSJCZMC6,--手术操作名称6 SF_BA_GETBABRSSXX(A.LSH000,6,6) SZ6,--术者6 SF_BA_GETBABRSSXX(A.LSH000,7,6) YZ6,--I助6 SF_BA_GETBABRSSXX(A.LSH000,8,6) EZ6,--II助6 SF_BA_GETBABRSSXX(A.LSH000,18,6) QKDJ6, SF_BA_GETBABRSSXX(A.LSH000,19,6) QKYHLB6, SF_BA_GETBABRSSXX(A.LSH000,9,6) MZFS6,--麻醉方式6 SF_BA_GETBABRSSXX(A.LSH000,12,6) MZYS6,--麻醉医师6*/ decode(A.LYFS00,'医嘱离院','1','医嘱转院','2','医嘱转社区卫生服务机构','3','非医嘱离院','4','死亡','5','其他','9') LYFS,--离院方式 -- A.ZYYLJG YZZY_YLJG, --医嘱转院，拟接收医疗机构名称 -- A.ZYYLJG WSY_YLJG,--医嘱转社区卫生服务机构/乡镇卫生院，拟接收医疗机构名称 decode(A.LYFS00,'医嘱转院',A.ZYYLJG,'-') YLJG, --医嘱转院，拟接收医疗机构名称 decode(A.LYFS00,'医嘱转社区卫生服务机构',A.ZYYLJG,'-') WSY_YLJG,--医嘱转社区卫生服务机构/乡镇卫生院，拟接收医疗机构名称 decode(A.ZZYJH0,'有','2','无','1') SFZZYJH, --出院31天内再住院计划 A.ZZYMD0 MD, --出院31天内再住院计划目的 A.HMRYQD RYQ_T,--入院前时间（颅脑损伤患者昏迷时间 天） A.HMRYQH RYQ_XS,--入院前多少小时（颅脑损伤患者昏迷时间 小时） A.HMRYQM RYQ_FZ, --入院前多少小分钟（颅脑损伤患者昏迷时间 分钟） A.HMRYHD RYH_T,--入院后多少天（颅脑损伤患者昏迷时间 天） A.HMRYHH RYH_XS,--入院后多少小时（颅脑损伤患者昏迷时间 小时） A.HMRYHM RYH_FZ,--入院后多少分钟（颅脑损伤患者昏迷时间 分钟） FY.ZYZFY0 ZFY, --总费用 FY.ZFJE00 ZFJE, --自付金额 FY.YBYLFW YLFUF, --一般医疗服务费 FY.YBZLCZ ZLCZF, --一般治疗操作费 FY.HLF000 HLF, --护理费 FY.QTFY00 QTFY, --其他费 FY.BLZDF0 BLZDF, --病理诊断费 FY.SYSZDF SYSZDF, --实验室诊断费 FY.YXXZDF YXXZDF, --影像学诊断费 FY.LCZDXM LCZDXMF, --临床诊断项目费 FY.FSSZLF FSSZLXMF, --非手术治疗项目费 FY.LCWLZL WLZLF, --临床物理治疗费 FY.SSZLF0 SSZLF, --手术治疗费 FY.MZF000 MAF, --麻醉费 FY.SSF000 SSF, --手术费 FY.KFF000 KFF, --康复费 FY.ZYZL00 ZYZLF,--中医治疗 FY.XYF000 XYF, --西药费 FY.KJYWF0 KJYWF, --抗菌药物费 FY.ZCYF00 ZCYF, --中成药费 FY.ZCYF10 ZCYF1, --中草药费 FY.XF0000 XF, --血费 FY.BDBLZP BDBLZPF, --白蛋白类制品费 FY.QDBLZP QDBLZPF, --球蛋白类制品费 FY.NXYZZP NXYZLZPF, --凝血因子类制品费 FY.XBYZZP XBYZLZPF, --细胞因子类制品费 FY.JCYCLF HCYYCLF, --检查用一次性医用材料费 FY.ZLYCLF YYCLF, --治疗用一次性医用材料费 FY.SSYCLF YCXYYCLF, --手术用一次性医用材料费 FY.QTF000 QTF--综合医疗服务类(1)其他费用 from BA_BRZYXX A join BA_SYFYB0 FY on A.LSH000=FY.ZYID00 ; create or replace view vw_ba_sjglyyjxkh as select (select ZZJGDM from ba_yyxx00) ZZJGDM,--组织机构代码 (select ZZJGMC from ba_yyxx00) JGMC, (select ZZJGMC from ba_yyxx00) USERNAME, decode(A.JFFS00,'城镇职工基本医疗保险','11','城镇居民基本医疗保险','21','社会基本医疗保险','8', '新型农村合作医疗','31','新农合医疗','31','贫困救助','4', '商业保险','5','全公费','6','公费医疗','6','全自费','7','自费医疗','7','自费','7','9') YLFKFS,--医疗付款方式 A.JKDAH0 JKKH, --健康卡号 A.ZYCS00 ZYCS,--住院次数 A.BAH000 BAH, --病案号 A.BRXM00 XM, --姓名 decode(A.BRXB00,'女','2','男','1','0') XB,--性别 (substrb(A.BRCSRQ,1,4)||'-'||substrb(A.BRCSRQ,5,2)||'-'||substrb(A.BRCSRQ,7,2)) CSRQ, --出生日期 --trunc(MONTHS_BETWEEN(to_date(A.RYRQ00,'yyMMdd'),to_date(A.BRCSRQ,'yyMMdd'))/12) NL, --年龄 nvl(substr(SF_BQ_CSRQTONL_DZBL(A.BRCSRQ,A.RYRQ00),1,instr(SF_BQ_CSRQTONL_DZBL(A.BRCSRQ,A.RYRQ00),'岁')-1),0) NL, --年龄 nvl((select nvl(GJDM00,'CHN') from XT_GJ0000 where MC0000=A.Gj0000),'CHN') GJ, --国籍 --SF_BA_CSRQTONL20(A.BRCSRQ,A.RYRQ00) BZYZS_NL, --（年龄不足1周岁的）年龄(天) '' BZYZS_NL, --（年龄不足1周岁的）年龄(天) decode(substr(A.YECSTZ,-1,1),0,A.YECSTZ,null,null,substr(A.YECSTZ,1,3)||'0') XSETZ, --新生儿出生体重(克) '' XSETZ2, '' XSETZ3, '' XSETZ4, '' XSETZ5, decode(substr(A.YERYTZ,-1,1),0,A.YERYTZ,null,null,substr(A.YERYTZ,1,3)||'0') XSERYTZ, --新生儿入院体重（克） nvl(substr(A.JGSF00,0,3),'-') CSD, --出生地 nvl((select ZD.DM0000 from XT_ZD0000 ZD where ZD.VALUE0=A.JGSF00 and ZD.ZDBM00='JG0000'),'13') GG,--籍贯 decode((select decode(substr(MZ.DM0000,1,1),'0',substr(MZ.DM0000,2,1),MZ.DM0000) from XT_MZ0000 MZ where MZ.MC0000=A.MZ0000),'',1,(select decode(substr(MZ.DM0000,1,1),'0',substr(MZ.DM0000,2,1),MZ.DM0000) from XT_MZ0000 MZ where MZ.MC0000=A.MZ0000)) MZ, '1' ZJLB, decode(SP_SF_CHECKSFZH(upper(A.SFZH00)),1,upper(A.SFZH00),'-') ZJHM,--身份证号 decode(A.ZY0000,'国家公务员','11','专业技术人员','13','职员','17','企业管理人员','21','工人','24','农民','27','学生','31','现役军人', '37','自由职业者','51','个体经营者','54','无业人员','70','退休人','80','其他','90','90') ZY,--职业 decode(A.HYZK00,'未婚','1','已婚','2','离婚','4','丧偶','3','其他','9','9') HY,--婚姻 nvl(A.JTDZ00,'-') XZZ, --现住址 nvl(A.JTDH00,'-') DH, --电话 nvl(trim(A.JTYB00),'-') YB1, --邮编 (现住址邮政编码) nvl(A.JTDZSF||A.JTDZDQ||A.JTDZXS,'-') HKDZ, --户口地址 nvl(trim(A.JTYB00),'-') YB2, --邮编 '福建省' GZDWJDZ, --工作单位及地址 nvl(trim(A.GZDH00),'-') DWDH, --单位电话 nvl(trim(decode(A.GZYB00,'无','-','--','-',A.GZYB00)),'-') YB3, --邮编 (工作邮政编码) nvl(A.LXRXM0,'-') LXRXM, --联系人姓名 decode(A.LXRGX0,'配偶','1','子','2','女','3','孙子、孙女或外孙子、外孙女','4','父母','5','祖父母或外祖父母','6','兄、弟、姐、妹','7','其他','9','9') GX,--关系 nvl(A.LXRDZ0,'-') DZ,--联系人地址 nvl(substrb(A.LXRDH0,1,30),'-') DH1,--联系人电话 nvl(A.RYTJ00,'9') RYTJ,--入院途径 case when nvl(FY.ZYZL00,0)+nvl(FY.ZYWZ00,0)+nvl(FY.ZYGS00,0)+nvl(FY.ZCYJF0,0)+nvl(FY.ZYTNZL,0)+nvl(FY.ZYGCZL,0)+nvl(FY.ZYTSZL,0)+nvl(FY.ZYQT00,0)+nvl(FY.ZYTSTP,0)+nvl(FY.BZSSF0,0) +nvl(FY.ZCYF00,0)+nvl(FY.ZCYF10,0)=0 then '3' else A.ZLLB00 end ZLLB,--治疗类别 (substrb(A.RYRQ00,1,4)||'-'||substrb(A.RYRQ00,5,2)||'-'||substrb(A.RYRQ00,7,2) ||' '|| A.RYSJ00) RYSJ, --入院时间 substrb(A.RYSJ00,1,2) RYSJ_S,--时 (select nvl(DM0000,'-') from BA_SJJXDY H where H.BH0000=A.RYKB00 and rownum<=1) RYKB,--入院科别 A.RYBF00 RYBF,--入院病房 nvl((select DM0000 from BA_SJJXDY H where H.BH0000=A.ZKKB00 and rownum<=1),'-') ZKKB,--转科科别 (substrb(A.cyrq00,1,4)||'-'||substrb(A.cyrq00,5,2)||'-'||substrb(A.CYRQ00,7,2) ||' '|| A.CYSJ00) CYSJ,--出院时间 substrb(A.CYSJ00,1,2) CYSJ_S,--时 (select DM0000 from BA_SJJXDY H where H.BH0000=A.CYKB00 and rownum<=1) CYKB, --出院科别 A.CYBF00 CYBF,--出院病房 decode(ceil(to_date(A.cyrq00,'yyyy-MM-dd')-to_date(A.RYRQ00,'yyyy-MM-dd')),0,1,ceil(to_date(A.cyrq00,'yyyy-MM-dd')-to_date(A.RYRQ00,'yyyy-MM-dd'))) SJZY, --A.ZYTS00 SJZY,--实际住院(天) nvl(SF_BA_GETLCZDB0(A.LSH000,'d',2,1),'-') MZD_ZYZD,--门(急)诊诊断(中医诊断) nvl(SF_BA_GETLCZDB0(A.LSH000,'d',0,1),'-') JBDM,--疾病代码 SF_BA_GETLCZDB0(A.LSH000,'1',2,1) MZZD_XYZD,--门（急）诊诊断名称 SF_BA_GETLCZDB0(A.LSH000,'1',0,1) JBBM,--门（急）诊诊断编码 nvl(A.LCLJ00,3) SSLCLJ,--实施临床路径 decode(nvl(FY.ZYZJF0,0),'0','2',A.ZZZYZJ) ZYYJ,--使用医疗机构中药制剂 A.ZLSB00 ZYZLSB,--使用中医诊疗设备 A.ZLJS00 ZYZLJS,--使用中医诊疗技术 A.BZSH00 BZSH,--辩证施护 nvl(SF_BA_GETLCZDB0(A.LSH000,'a',2,1),'-') ZB,--主病出院中医诊断 nvl(SF_BA_GETLCZDB0(A.LSH000,'a',0,1),'-') ZB_JBBM,--主病疾病编码 nvl(SF_BA_GETLCZDB0(A.LSH000,'a',1,1),'-') ZB_RYBQ,--主病入院病情 SF_BA_GETLCZDB0(A.LSH000,'3',2,1) ZYZD,--主要诊断出院西医诊断 SF_BA_GETLCZDB0(A.LSH000,'3',0,1) ZYZD_JBBM,--主要诊断疾病编码 SF_BA_GETLCZDB0(A.LSH000,'3',1,1) XY_RYBQ,--主要诊断入院病情 SF_BA_GETLCZDB0(A.LSH000,'3',6,1) XY_CYQK, --出院主要诊断出院情况(西医) nvl(SF_BA_GETLCZDB0(A.LSH000,'b',2,1),'-') ZZ1,--主证1出院中医诊断 nvl(SF_BA_GETLCZDB0(A.LSH000,'b',0,1),'-') ZZ_JBBM1,--主证1疾病编码 nvl(SF_BA_GETLCZDB0(A.LSH000,'b',1,1),'-') ZZ_RYBQ1, --主证1住入院病情 SF_BA_GETLCZDB0(A.LSH000,'4',2,1) QTZD1, --其他诊断1出院西医诊断 SF_BA_GETLCZDB0(A.LSH000,'4',0,1) ZYZD_JBBM1, --其他诊断1疾病编码 SF_BA_GETLCZDB0(A.LSH000,'4',1,1) RYBQ1,--其他诊断入院病情1 SF_BA_GETLCZDB0(A.LSH000,'4',6,1) CYQK1,--出院其他诊断出院情况(西医) SF_BA_GETLCZDB0(A.LSH000,'b',3,2) ZZ2,--主证2出院中医诊断 SF_BA_GETLCZDB0(A.LSH000,'b',0,2) ZZ_JBBM2,--主证2疾病编码 SF_BA_GETLCZDB0(A.LSH000,'b',1,2) ZZ_RYBQ2, --主证2住入院病情 SF_BA_GETLCZDB0(A.LSH000,'4',2,2) QTZD2, --其他诊断2出院西医诊断 SF_BA_GETLCZDB0(A.LSH000,'4',0,2) ZYZD_JBBM2, --其他诊断2疾病编码 SF_BA_GETLCZDB0(A.LSH000,'4',1,2) RYBQ2, --其他诊断2入院病情 SF_BA_GETLCZDB0(A.LSH000,'4',6,2) CYQK2,--出院其他诊断出院情况(西医) SF_BA_GETLCZDB0(A.LSH000,'b',3,3) ZZ3,--主证3出院中医诊断 SF_BA_GETLCZDB0(A.LSH000,'b',0,3) ZZ_JBBM3,--主证3疾病编码 SF_BA_GETLCZDB0(A.LSH000,'b',1,3) ZZ_RYBQ3, --主证3住入院病情 SF_BA_GETLCZDB0(A.LSH000,'4',2,3) QTZD3,--其他诊断3出院西医诊断 SF_BA_GETLCZDB0(A.LSH000,'4',0,3) ZYZD_JBBM3, --其他诊断3疾病编码 SF_BA_GETLCZDB0(A.LSH000,'4',1,3) RYBQ3, --其他诊断入院病情3 SF_BA_GETLCZDB0(A.LSH000,'4',6,3) CYQK3,--出院其他诊断出院情况(西医) SF_BA_GETLCZDB0(A.LSH000,'b',3,4) ZZ4,--主证4出院中医诊断 SF_BA_GETLCZDB0(A.LSH000,'b',0,4) ZZ_JBBM4,--主证4疾病编码 SF_BA_GETLCZDB0(A.LSH000,'b',1,4) ZZ_RYBQ4, --主证4住入院病情 SF_BA_GETLCZDB0(A.LSH000,'4',2,4) QTZD4, --其他诊断4出院西医诊断 SF_BA_GETLCZDB0(A.LSH000,'4',0,4) ZYZD_JBBM4, --其他诊断4疾病编码 SF_BA_GETLCZDB0(A.LSH000,'4',1,4) RYBQ4,--其他诊断入院病情4 SF_BA_GETLCZDB0(A.LSH000,'4',6,4) CYQK4,--出院其他诊断出院情况(西医) SF_BA_GETLCZDB0(A.LSH000,'b',3,5) ZZ5,--主证5出院中医诊断 SF_BA_GETLCZDB0(A.LSH000,'b',0,5) ZZ_JBBM5,--主证5疾病编码 SF_BA_GETLCZDB0(A.LSH000,'b',1,5) ZZ_RYBQ5, --主证5住入院病情 SF_BA_GETLCZDB0(A.LSH000,'4',2,5) QTZD5,--其他诊断5出院西医诊断 SF_BA_GETLCZDB0(A.LSH000,'4',0,5) ZYZD_JBBM5, --其他诊断5疾病编码 SF_BA_GETLCZDB0(A.LSH000,'4',1,5) RYBQ5, --其他诊断入院病情5 SF_BA_GETLCZDB0(A.LSH000,'4',6,5) CYQK5,--出院其他诊断出院情况(西医) SF_BA_GETLCZDB0(A.LSH000,'b',3,6) ZZ6,--主证6出院中医诊断 SF_BA_GETLCZDB0(A.LSH000,'b',0,6) ZZ_JBBM6,--主证6疾病编码 SF_BA_GETLCZDB0(A.LSH000,'b',1,6) ZZ_RYBQ6, --主证6住入院病情 SF_BA_GETLCZDB0(A.LSH000,'4',2,6) QTZD6,--其他诊断6出院西医诊断 SF_BA_GETLCZDB0(A.LSH000,'4',0,6) ZYZD_JBBM6,--其他诊断6疾病编码 SF_BA_GETLCZDB0(A.LSH000,'4',1,6) RYBQ6,--其他诊断入院病情6 SF_BA_GETLCZDB0(A.LSH000,'4',6,6) CYQK6,--出院其他诊断出院情况(西医) SF_BA_GETLCZDB0(A.LSH000,'b',3,7) ZZ7,--主证7出院中医诊断 SF_BA_GETLCZDB0(A.LSH000,'b',0,7) ZZ_JBBM7,--主证7疾病编码 SF_BA_GETLCZDB0(A.LSH000,'b',1,7) ZZ_RYBQ7, --主证7住入院病情 SF_BA_GETLCZDB0(A.LSH000,'4',2,7) QTZD7,--其他诊断7出院西医诊断 SF_BA_GETLCZDB0(A.LSH000,'4',0,7) ZYZD_JBBM7,--其他诊断7疾病编码 SF_BA_GETLCZDB0(A.LSH000,'4',1,7) RYBQ7,--其他诊断入院病情7 SF_BA_GETLCZDB0(A.LSH000,'4',6,7) CYQK7,--出院其他诊断出院情况(西医) a.sszdwy WBYY, --中毒的外部原因 新码 A.SSZDBM JBBM1,--疾病编码 nvl((select nvl(XT.jbmc01,ZD.FZMC00) from bq_brzdxx zd join XT_ICDDYB XT on ZD.FZBM00=XT.ICD100 where XT.YT0000=0 and ZD.ZYID00=A.LSH000 and ZD.ZDLB00='3' and (ZD.ZDM000 like 'C%' or ZD.ZDM000 like 'D%' ) and rownum=1),'-') BLZD,--疾病编码 nvl((select nvl(XT.icd101,ZD.FZBM00) from bq_brzdxx zd join XT_ICDDYB XT on ZD.FZBM00=XT.ICD100 where XT.YT0000=0 and ZD.ZYID00=A.LSH000 and ZD.ZDLB00='3' and (ZD.ZDM000 like 'C%' or ZD.ZDM000 like 'D%' ) and rownum=1),'-') JBBM2,--疾病编码 nvl(A.BLH000,'-') BLH,--病理号 '' BLZDMC1,--其他病理诊断名称 '' BLZDBM1,--其他病理诊断编码 '' BLH1,--其他病理号 '' BLZDMC2,--其他病理诊断名称 '' BLZDBM2,--其他病理诊断编码 '' BLH2,--其他病理号 decode(A.SFYWGM,'无','1','有','2','1') YWGM, --药物过敏 A.GMYW00 GMYW,--过敏药物 decode(A.SFSJ00,'是','1','否','2','2') SJ,--死亡患者尸检 decode(A.XX0000,'A型','1','B型','2','O型','3','AB型','4','未检','6','5') XX, --血型 decode(A.RH0000,'阴性','1','阳性','2','不详','3','未做','4','3') RH, --RH nvl(A.KZRMC0,'-') KZR,--科主任 nvl2(a.kzr000,(select YSZJHM from BM_YGBM00 where ygbh00||''=a.kzr000),'-') KZRBM,--科主任编码 nvl(A.FZRMC0,'-') ZRYS,--副主任 nvl2(a.fzr000,(select YSZJHM from BM_YGBM00 where ygbh00||''=a.fzr000),'-') ZRYSBM,--主任（副主任）医师编码 nvl(A.ZZYSMC,'-') ZZYS,--主治医师 nvl2(a.zzys00,(select YSZJHM from BM_YGBM00 where ygbh00||''=a.zzys00),'-') ZZYSBM,--主治医师编码 nvl(A.ZYYSMC,'-') ZYYS,--住院医师 nvl2(a.zyys00,(select YSZJHM from BM_YGBM00 where ygbh00||''=a.zyys00),'-') ZYYSBM,--住院医师编码 nvl(A.ZRHSMC,'-') ZRHS, --责任护士 nvl2(a.zrhs00,(select YSZJHM from BM_YGBM00 where ygbh00||''=a.zrhs00),'-') ZRHSBM,--责任护士编码 nvl(A.JXYSMC,'-') JXYS, --进修医师 nvl(A.YJSMC0,'-') SXYS,--实习医师 nvl(A.SHRXM0,'-') BMY,--编码员 decode(A.BAZL00,'甲','1','乙','2','丙','3') BAZL,--病案质量 A.ZKYSMC ZKYS,--质控医师 A.ZKHSMC ZKHS, --质控护士 (substrb(A.ZKRQ00,1,4)||'-'||substrb(A.ZKRQ00,5,2)||'-'||substrb(A.ZKRQ00,7,2)) ZKRQ,--质控日期 nvl(SF_BA_GETBABRSSXX(A.LSH000,0,1),'-') SSJCZBM1,--手术操作编码1 nvl(SF_BA_GETBABRSSXX(A.LSH000,3,1),'-') SSJCZMC1,--主要手术操作名称 nvl(SF_BA_GETBABRSSXX(A.LSH000,1,1),'-') SSJCZRQ1,--手术操作日期1 SF_BA_GETBABRSSXX(A.LSH000,2,1) SHJB1,--手术级别1 SF_BA_GETBABRSSXX(A.LSH000,6,1) SZ1,--术者1 nvl(SF_BA_GETBABRSSXX(A.LSH000,7,1),'-') YZ1,--I助1 nvl(SF_BA_GETBABRSSXX(A.LSH000,8,1),'-') EZ1,--II助1 SF_BA_GETBABRSSXX(A.LSH000,13,1) QKDJ1, SF_BA_GETBABRSSXX(A.LSH000,14,1) QKYLB1, SF_BA_GETBABRSSXX(A.LSH000,9,1) MZFS1, nvl(SF_BA_GETBABRSSXX(A.LSH000,12,1),'-') MZYS1, --麻醉医师1 '' SSCZSJ1,--主要手术持续时间 '' MZFJ1,--主要手术麻醉分级 SF_BA_GETBABRSSXX(A.LSH000,0,2) SSJCZBM2,--手术操作编码2 SF_BA_GETBABRSSXX(A.LSH000,0,3) SSJCZBM3,--手术操作编码3 SF_BA_GETBABRSSXX(A.LSH000,0,4) SSJCZBM4,--手术操作编码4 SF_BA_GETBABRSSXX(A.LSH000,3,2) SSJCZMC2,--手术操作名称2 SF_BA_GETBABRSSXX(A.LSH000,3,3) SSJCZMC3,--手术操作名称3 SF_BA_GETBABRSSXX(A.LSH000,3,4) SSJCZMC4,--手术操作名称4 SF_BA_GETBABRSSXX(A.LSH000,1,2) SSJCZRQ2,--手术操作日期2 SF_BA_GETBABRSSXX(A.LSH000,1,3) SSJCZRQ3,--手术操作日期3 SF_BA_GETBABRSSXX(A.LSH000,1,4) SSJCZRQ4,--手术操作日期4 SF_BA_GETBABRSSXX(A.LSH000,2,2) SHJB2,--手术级别2 SF_BA_GETBABRSSXX(A.LSH000,2,3) SHJB3,--手术级别3 SF_BA_GETBABRSSXX(A.LSH000,2,4) SHJB4,--手术级别4 SF_BA_GETBABRSSXX(A.LSH000,6,2) SZ2,--术者2 SF_BA_GETBABRSSXX(A.LSH000,6,3) SZ3,--术者3 SF_BA_GETBABRSSXX(A.LSH000,6,4) SZ4,--术者4 SF_BA_GETBABRSSXX(A.LSH000,7,2) YZ2,--I助2 SF_BA_GETBABRSSXX(A.LSH000,7,3) YZ3,--I助3 SF_BA_GETBABRSSXX(A.LSH000,7,4) YZ4,--I助4 nvl(SF_BA_GETBABRSSXX(A.LSH000,8,2),'-') EZ2,--II助2 nvl(SF_BA_GETBABRSSXX(A.LSH000,8,3),'-') EZ3,--II助3 nvl(SF_BA_GETBABRSSXX(A.LSH000,8,4),'-') EZ4,--II助4 SF_BA_GETBABRSSXX(A.LSH000,13,2) QKDJ2, SF_BA_GETBABRSSXX(A.LSH000,13,3) QKDJ3, SF_BA_GETBABRSSXX(A.LSH000,13,4) QKDJ4, SF_BA_GETBABRSSXX(A.LSH000,14,2) QKYLB2, SF_BA_GETBABRSSXX(A.LSH000,14,3) QKYLB3, SF_BA_GETBABRSSXX(A.LSH000,14,4) QKYLB4, SF_BA_GETBABRSSXX(A.LSH000,9,2) MZFS2, SF_BA_GETBABRSSXX(A.LSH000,9,3) MZFS3, SF_BA_GETBABRSSXX(A.LSH000,9,4) MZFS4, SF_BA_GETBABRSSXX(A.LSH000,12,2) MZYS2, --麻醉医师2 SF_BA_GETBABRSSXX(A.LSH000,12,3) MZYS3, --麻醉医师3 SF_BA_GETBABRSSXX(A.LSH000,12,4) MZYS4, --麻醉医师4 '' SSCZSJ2,-- '' MZFJ2,-- '' SSCZSJ3,-- '' MZFJ3,-- '' SSCZSJ4,-- '' MZFJ4,-- '-' ZYZLCZBM1,--中医治疗性操作编码 '-' ZYZLCZBM2,--中医治疗性操作编码 '-' ZYZLCZBM3,--中医治疗性操作编码 '-' ZYZLCZBM4,--中医治疗性操作编码 '-' ZYZLCZBM5,--中医治疗性操作编码 '-' ZYZLCZBM6,--中医治疗性操作编码 '-' ZYZLCZBM7,--中医治疗性操作编码 '-' ZYZLCZBM8,--中医治疗性操作编码 '-' ZYZLCZBM9,--中医治疗性操作编码 '-' ZYZLCZBM10,--中医治疗性操作编码 '-' ZYZLCZMC1,--中医治疗性操作名称 '-' ZYZLCZMC2,--中医治疗性操作名称 '-' ZYZLCZMC3,--中医治疗性操作名称 '-' ZYZLCZMC4,--中医治疗性操作名称 '-' ZYZLCZMC5,--中医治疗性操作名称 '-' ZYZLCZMC6,--中医治疗性操作名称 '-' ZYZLCZMC7,--中医治疗性操作名称 '-' ZYZLCZMC8,--中医治疗性操作名称 '-' ZYZLCZMC9,--中医治疗性操作名称 '-' ZYZLCZMC10,--中医治疗性操作名称 '' ZYZLCZRQ1,-- '' ZYZLCZRQ2,-- '' ZYZLCZRQ3,-- '' ZYZLCZRQ4,-- '' ZYZLCZRQ5,-- '' ZYZLCZRQ6,-- '' ZYZLCZRQ7,-- '' ZYZLCZRQ8,-- '' ZYZLCZRQ9,-- '' ZYZLCZRQ10,-- '' ZYZLCZJB1,-- '' ZYZLCZJB2,-- '' ZYZLCZJB3,-- '' ZYZLCZJB4,-- '' ZYZLCZJB5,-- '' ZYZLCZJB6,-- '' ZYZLCZJB7,-- '' ZYZLCZJB8,-- '' ZYZLCZJB9,-- '' ZYZLCZJB10,-- '' ZYZLCZSZ1,-- '' ZYZLCZSZ2,-- '' ZYZLCZSZ3,-- '' ZYZLCZSZ4,-- '' ZYZLCZSZ5,-- '' ZYZLCZSZ6,-- '' ZYZLCZSZ7,-- '' ZYZLCZSZ8,-- '' ZYZLCZSZ9,-- '' ZYZLCZSZ10,-- '' ZYZLCZQKDJ1,-- '' ZYZLCZQKDJ2,-- '' ZYZLCZQKDJ3,-- '' ZYZLCZQKDJ4,-- '' ZYZLCZQKDJ5,-- '' ZYZLCZQKDJ6,-- '' ZYZLCZQKDJ7,-- '' ZYZLCZQKDJ8,-- '' ZYZLCZQKDJ9,-- '' ZYZLCZQKDJ10,-- '' ZYZLCZQKYLB1,-- '' ZYZLCZQKYLB2,-- '' ZYZLCZQKYLB3,-- '' ZYZLCZQKYLB4,-- '' ZYZLCZQKYLB5,-- '' ZYZLCZQKYLB6,-- '' ZYZLCZQKYLB7,-- '' ZYZLCZQKYLB8,-- '' ZYZLCZQKYLB9,-- '' ZYZLCZQKYLB10,-- '' ZYZLCZMZFS1,-- '' ZYZLCZMZFS2,-- '' ZYZLCZMZFS3,-- '' ZYZLCZMZFS4,-- '' ZYZLCZMZFS5,-- '' ZYZLCZMZFS6,-- '' ZYZLCZMZFS7,-- '' ZYZLCZMZFS8,-- '' ZYZLCZMZFS9,-- '' ZYZLCZMZFS10,-- '' ZYZLCZMZYS1,-- '' ZYZLCZMZYS2,-- '' ZYZLCZMZYS3,-- '' ZYZLCZMZYS4,-- '' ZYZLCZMZYS5,-- '' ZYZLCZMZYS6,-- '' ZYZLCZMZYS7,-- '' ZYZLCZMZYS8,-- '' ZYZLCZMZYS9,-- '' ZYZLCZMZYS10,-- '' ZYZLCZSJ1,--中医治疗性操作持续时间 '' ZYZLCZSJ2,--中医治疗性操作持续时间 '' ZYZLCZSJ3,--中医治疗性操作持续时间 '' ZYZLCZSJ4,--中医治疗性操作持续时间 '' ZYZLCZSJ5,--中医治疗性操作持续时间 '' ZYZLCZSJ6,--中医治疗性操作持续时间 '' ZYZLCZSJ7,--中医治疗性操作持续时间 '' ZYZLCZSJ8,--中医治疗性操作持续时间 '' ZYZLCZSJ9,--中医治疗性操作持续时间 '' ZYZLCZSJ10,--中医治疗性操作持续时间 '' ZYZLCZMZFJ1,--中医治疗性操作麻醉分级 '' ZYZLCZMZFJ2,--中医治疗性操作麻醉分级 '' ZYZLCZMZFJ3,--中医治疗性操作麻醉分级 '' ZYZLCZMZFJ4,--中医治疗性操作麻醉分级 '' ZYZLCZMZFJ5,--中医治疗性操作麻醉分级 '' ZYZLCZMZFJ6,--中医治疗性操作麻醉分级 '' ZYZLCZMZFJ7,--中医治疗性操作麻醉分级 '' ZYZLCZMZFJ8,--中医治疗性操作麻醉分级 '' ZYZLCZMZFJ9,--中医治疗性操作麻醉分级 '' ZYZLCZMZFJ10,--中医治疗性操作麻醉分级 decode(A.LYFS00,'医嘱离院','1','医嘱转院','2','医嘱转社区卫生服务机构','3','非医嘱离院','4','死亡','5','其他','9','9') LYFS,--离院方式 A.ZYYLJG YZZY_JGMC,--医嘱转社区卫生服务机构/乡镇卫生院，拟接收医疗机构名称 A.ZYYLJG WSY_JGMC, decode(A.ZZYJH0,'有','2','1') ZZYJH, --出院31天内再住院计划 A.ZZYMD0 MD, --出院31天内再住院计划目的 decode(A.HMRYQD,'','0',A.HMRYQD) RYQ_T,--入院前时间（颅脑损伤患者昏迷时间 天） decode(A.HMRYQH,'','0',A.HMRYQH) RYQ_XS,--入院前多少小时（颅脑损伤患者昏迷时间 小时） decode(A.HMRYQM,'','0',A.HMRYQM) RYQ_FZ, --入院前多少小分钟（颅脑损伤患者昏迷时间 分钟） decode(A.HMRYHD,'','0',A.HMRYHD) RYH_T,--入院后多少天（颅脑损伤患者昏迷时间 天） decode(A.HMRYHH,'','0',A.HMRYHH) RYH_XS,--入院后多少小时（颅脑损伤患者昏迷时间 小时） decode(A.HMRYHM,'','0',A.HMRYHM) RYH_FZ,--入院后多少分钟（颅脑损伤患者昏迷时间 分钟） FY.ZYZFY0 ZFY, --总费用 FY.ZFJE00 ZFJE, --自付金额 FY.YBYLFW+nvl(FY.ZYBZLZ,0)+nvl(FY.ZYBZHZ,0) YLFWF, --一般医疗服务费 FY.ZYBZLZ BZLZF, --中医辨证论治费 FY.ZYBZHZ ZYBLZHZF, --中医辨证论治会诊费 FY.YBZLCZ ZLCZF, --一般治疗操作费 FY.HLF000 HLF, --护理费 FY.QTFY00 QTFY,--综合医疗服务类(1)其他费用 FY.BLZDF0 BLZDF, --病理诊断费 FY.SYSZDF ZDF, --实验室诊断费 FY.YXXZDF YXXZDF, --影像学诊断费 FY.LCZDXM LCZDXMF, --临床诊断项目费 nvl(FY.FSSZLF,0)+ nvl(FY.LCWLZL,0) FSSZLXMF, --非手术治疗项目费 nvl(FY.LCWLZL,0) ZLF, --临床物理治疗费 nvl(FY.SSZLF0,0)+nvl(FY.MZF000,0)+nvl(FY.SSF000,0) SSZLF, --手术治疗费 FY.MZF000 MZF, --麻醉费 FY.MZF000 FY.SSF000 SSF, --手术费 FY.SSF000 FY.KFF000 KFF, --康复费 FY.ZYZD00 ZYL_ZYZD,--中医诊断 nvl(FY.ZYZL00,0)+nvl(FY.ZYWZ00,0)+nvl(FY.ZYGS00,0)+nvl(FY.ZCYJF0,0)+nvl(FY.ZYTNZL,0)+nvl(FY.ZYGCZL,0)+nvl(FY.ZYTSZL,0) ZYZL,--中医治疗 FY.ZYWZ00 ZYWZ,--中医外治 FY.ZYGS00 ZYGS,--中医骨伤 FY.ZCYJF0 ZCYJF,--针刺与灸法 FY.ZYTNZL ZYTNZL,--中医推拿治疗 FY.ZYGCZL ZYGCZL,--中医肛肠治疗 FY.ZYTSZL ZYTSZL,--中医特殊治疗 nvl(FY.ZYQT00,0)+nvl(FY.ZYTSTP,0)+nvl(FY.BZSSF0,0) ZYQT,--中医其他 FY.ZYTSTP ZYTSTPJG,--中医特殊调配加工 FY.BZSSF0 BZSS,--辨证施膳 nvl(FY.XYF000,0)+nvl(FY.KJYWF0,0) XYF, --西药费 -- FY.XYF000-nvl(FY.BDBLZP,0)-nvl(FY.QDBLZP,0) XYF, --西药费 FY.KJYWF0 KJYWF, --抗菌药物费 NVL(FY.ZCYF00,0)+nvl(FY.ZYZJF0,0) ZCYF, --中成药费 FY.ZYZJF0 ZYZJF,--医疗机构中药制剂费 FY.ZCYF10 ZCYF1, --中草药费 FY.XF0000 XF, --血费 FY.BDBLZP BDBLZPF, --白蛋白类制品费 FY.QDBLZP QDBLZPF, --球蛋白类制品费 FY.NXYZZP NXYZLZPF, --凝血因子类制品费 FY.XBYZZP XBYZLZPF, --细胞因子类制品费 FY.JCYCLF JCYYCLF, --检查用一次性医用材料费 FY.ZLYCLF YYCLF, --治疗用一次性医用材料费 FY.SSYCLF SSYCXCLF, --手术用一次性医用材料费 FY.QTF000 QTF, --其他费 SF_BA_GETLCZDB0(A.LSH000,'2',1,1) RYQK,--入院时情况 SF_BA_GETLCZDB0(A.LSH000,'2',0,1) RYZDBM,--入院诊断编码 SF_BA_GETLCZDB0(A.LSH000,'2',2,1) RYZDMC,--入院诊断名称 --A.RYQZRQ RYHQZRQ,--入院后确诊日期 '' RYHQZRQ,--入院后确诊日期 '' HBSAG,-- '' HCVAB,-- '' HIVAB,-- --A.TJHLTS TJHLTS,--特级护理天数 '' TJHLTS ,--特级护理天数 --A.YJHLTS YJHLTS,--一级 '' YJHLTS,--一级 --A.EJHLTS EJHLTS,--二级护理天数 '' EJHLTS,--二级护理天数 --A.SJHLTS SJHLTS,--三级护理天数 '' SJHLTS,--三级护理天数 '' SXFY,--输血反应 '' HXB,--红细胞 '' XXB,--血小板 '' XJ,--血浆 '' QX,--全血 --A.ZTXHS0 ZTXHS,--自体血回输 '' ZTXHS,--自体血回输 --nvl(A.RJSS00, '2') SFRJSS,--是否为日间手术 '2' SFRJSS,--是否为日间手术 --A.HXJSJ0 HXJSYSJ,--有创呼吸机使用时间 '' HXJSYSJ,--有创呼吸机使用时间 '' ZZJHSMC1,--重症监护室名称 '' ZZJHSMC2,--重症监护室名称 '' ZZJHSMC3,--重症监护室名称 '' ZZJHSMC4,--重症监护室名称 '' ZZJHSMC5,--重症监护室名称 '' JRSJ1,--进入时间 '' JRSJ2,--进入时间 '' JRSJ3,--进入时间 '' JRSJ4,--进入时间 '' JRSJ5,--进入时间 '' TCSJ1,--退出时间 '' TCSJ2,--退出时间 '' TCSJ3,--退出时间 '' TCSJ4,--退出时间 '' TCSJ5--退出时间 from BA_BRZYXX A join BA_SYFYB0 fy on A.LSH000=FY.ZYID00 join zy_brxxb0 b on a.lsh000=b.zyid00 where b.brzt00<>'9'; create or replace view vw_ba_zybasynbb as select A.LSH000, (select ZZJGMC from ba_yyxx00) yljg00, (select ZZJGDM from ba_yyxx00) zjjgdm,--组织机构代码 decode(A.JFFS00,'城镇职工基本医疗保险','1','城镇居民基本医疗保险','2','新型农村合作医疗','3','贫困救助','4', '商业医疗保险','5','全公费','6','全自费','7','自费医疗','7','其他社会保险','8','9') YLFKFS, --医疗付款方式 A.JKDAH0 jkkh00, --健康卡号 A.ZYCS00 djczy0,--住院次数 A.BAH000 bah000, --病案号 A.BRXM00 xm0000, --姓名 decode(A.BRXB00,'女','2','男','1','0') xb0000,--性别 to_char(to_date(A.BRCSRQ,'yyyymmdd'),'yyyy-mm-dd') csrq00, A.BRNL00 nl0000, A.GJ0000 gj0000, --国籍 substr(SF_BQ_CSRQTONL_DZBL(BRCSRQ,A.RYRQ00),1,instr(SF_BQ_CSRQTONL_DZBL(BRCSRQ,A.RYRQ00),'月')-1) nly000, --（年龄不足1周岁的）年龄(月) A.YECSTZ xsecst, --新生儿出生体重(克) A.YERYTZ xseryt, --新生儿入院体重（克） --nvl(trim(A.CSDS00),'-') csd000, --出生地 nvl(trim(cssf00||csxs00||A.CSDS00),'-') csd000, --出生地 --A.JGSF00 GG0000,--籍贯 JGSF00||JGXS00 GG0000,--籍贯 nvl(A.MZ0000,'汉族') MZ0000, A.SFZH00 sfzh00,--身份证号 decode(A.ZY0000,'国家公务员','11','专业技术人员','13','职员','17','企业管理人员','21','工人','24','农民','27','学生','31','现役军人', '37','自由职业者','51','个体经营者','54','无业人员','70','退休人','80','其他','90','90') ZY0000, decode(A.HYZK00,'未婚','1','已婚','2','离婚','4','丧偶','3','其他','9','9') hy0000, nvl(XZZSF0||XZZDQ0||XZZXS0||A.XZZXC0,'-') xzz000, --现住址 nvl(A.Xzzdh0,'-') xzzdh0, --电话 A.JTYB00 xzzyb0, --A.JTDZ00 hkdz00, JTDZSF||JTDZDQ||JTDZXS||A.JTDZ01 hkdz00, A.JTYB00 hkyb00, --邮编 A.GZDW00 GZDWJDZ, --工作单位及地址 A.GZDH00 DWDH00, --单位电话 A.GZYB00 dwyb00, --邮编 (工作邮政编码) A.LXRXM0 LXRXM0, --联系人姓名 decode(A.LXRGX0,'配偶','1','子','2','女','3','孙子、孙女或外孙子、外孙女','4','父母','5','祖父母或外祖父母','6','兄、弟、姐、妹','7','其他','9','9') ylxrgx, nvl(A.LXRDZ0||A.LXRDZ1,'-') lxrdz0,--联系人地址 A.LXRDH0,--联系人电话 SF_BA_GETYSEORNO(a.lsh000) RJSS00,--日间手术 A.RYTJ00, (substrb(A.RYRQ00,1,4)||substrb(A.RYRQ00,5,2)||substrb(A.RYRQ00,7,2)) RYSJ00, --入院时间 substrb(A.RYSJ00,1,2) RYSJS0,--时 a.rykbmc, A.RYBF00 RYBF00,--入院病房 decode(instr(zkkbmc,';'),0,zkkbmc,substr(zkkbmc,1,instr(zkkbmc,';')-1)) zkkbmc, --substrb(A.CYRQ00,1,4)||substrb(A.CYRQ00,5,2)||substrb(A.CYRQ00,7,2) CYSJ00,--出院时间 A.CYRQ00 CYSJ00,--出院时间 substrb(A.CYSJ00,1,2) CYSJS0,--时 a.cykbmc, A.CYBF00 CYBF,--出院病房 A.ZYTS00 SJZYTS,--实际住院(天) SF_BA_GETLCZDB0(A.LSH000,'1',2,1) MZZD,--门(急)诊诊断(西医诊断) SF_BA_GETLCZDB0(A.LSH000,'1',0,1) JBBM,--疾病编码 SF_BA_GETLCZDB0(A.LSH000,'3',2,1) ZYZD,--主要诊断 SF_BA_GETLCZDB0(A.LSH000,'3',0,1) JBDM,--疾病代码 SF_BA_GETLCZDB0(A.LSH000,'3',1,1) RYBQ,--主诊断院病情 SF_BA_GETLCZDB0(A.LSH000,'4',2,8) QTZD8, SF_BA_GETLCZDB0(A.LSH000,'4',0,8) JBDM8, SF_BA_GETLCZDB0(A.LSH000,'4',1,8) RYBQ8, SF_BA_GETLCZDB0(A.LSH000,'4',2,1) QTZD1,--其他诊断3出院西医诊断 SF_BA_GETLCZDB0(A.LSH000,'4',0,1) JBDM1,--主证3疾病编码 SF_BA_GETLCZDB0(A.LSH000,'4',1,1) RYBQ1, --主证3住入院病情 SF_BA_GETLCZDB0(A.LSH000,'4',2,9) QTZD9, --其他诊断4出院西医诊断 SF_BA_GETLCZDB0(A.LSH000,'4',0,9) JBDM9,--主证4疾病编码 SF_BA_GETLCZDB0(A.LSH000,'4',1,9) RYBQ9, --主证4住入院病情 SF_BA_GETLCZDB0(A.LSH000,'4',2,2) QTZD2,--其他诊断5出院西医诊断 SF_BA_GETLCZDB0(A.LSH000,'4',0,2) JBDM2,--主证5疾病编码 SF_BA_GETLCZDB0(A.LSH000,'4',1,2) RYBQ2, --主证5住入院病情 SF_BA_GETLCZDB0(A.LSH000,'4',2,10) QTZD10,--其他诊断6出院西医诊断 SF_BA_GETLCZDB0(A.LSH000,'4',0,10) JBDM10,--主证6疾病编码 SF_BA_GETLCZDB0(A.LSH000,'4',1,10) RYBQ10, --主证6住入院病情 SF_BA_GETLCZDB0(A.LSH000,'4',2,3) QTZD3,--其他诊断7出院西医诊断 SF_BA_GETLCZDB0(A.LSH000,'4',0,3) JBDM3,--主证7疾病编码 SF_BA_GETLCZDB0(A.LSH000,'4',1,3) RYBQ3, --主证7住入院病情 SF_BA_GETLCZDB0(A.LSH000,'4',2,11) QTZD11,--其他诊断7出院西医诊断 SF_BA_GETLCZDB0(A.LSH000,'4',0,11) JBDM11,--主证7疾病编码 SF_BA_GETLCZDB0(A.LSH000,'4',1,11) RYBQ11, --主证7住入院病情 SF_BA_GETLCZDB0(A.LSH000,'4',2,4) QTZD4,--其他诊断7出院西医诊断 SF_BA_GETLCZDB0(A.LSH000,'4',0,4) JBDM4, SF_BA_GETLCZDB0(A.LSH000,'4',1,4) RYBQ4, --主证7住入院病情 SF_BA_GETLCZDB0(A.LSH000,'4',2,12) QTZD12,--其他诊断7出院西医诊断 SF_BA_GETLCZDB0(A.LSH000,'4',0,12) JBDM12,--主证7疾病编码 SF_BA_GETLCZDB0(A.LSH000,'4',1,12) RYBQ12, --主证7住入院病情 SF_BA_GETLCZDB0(A.LSH000,'4',2,5) QTZD5,--其他诊断7出院西医诊断 SF_BA_GETLCZDB0(A.LSH000,'4',0,5) JBDM5,--主证7疾病编码 SF_BA_GETLCZDB0(A.LSH000,'4',1,5) RYBQ5, --主证7住入院病情 SF_BA_GETLCZDB0(A.LSH000,'4',2,13) QTZD13,--其他诊断7出院西医诊断 SF_BA_GETLCZDB0(A.LSH000,'4',0,13) JBDM13,--主证7疾病编码 SF_BA_GETLCZDB0(A.LSH000,'4',1,13) RYBQ13, --主证7住入院病情 SF_BA_GETLCZDB0(A.LSH000,'4',2,6) QTZD6,--其他诊断7出院西医诊断 SF_BA_GETLCZDB0(A.LSH000,'4',0,6) JBDM6,--主证7疾病编码 SF_BA_GETLCZDB0(A.LSH000,'4',1,6) RYBQ6, --主证7住入院病情 SF_BA_GETLCZDB0(A.LSH000,'4',2,7) QTZD7,--其他诊断7出院西医诊断 SF_BA_GETLCZDB0(A.LSH000,'4',0,7) JBDM7,--主证7疾病编码 SF_BA_GETLCZDB0(A.LSH000,'4',1,7) RYBQ7, --主证7住入院病情 SF_BA_GETLCZDB0(A.LSH000,'4',2,14) QTZD14,--其他诊断7出院西医诊断 SF_BA_GETLCZDB0(A.LSH000,'4',0,14) JBDM14,--主证7疾病编码 SF_BA_GETLCZDB0(A.LSH000,'4',1,14) RYBQ14, --主证7住入院病情 SF_BA_GETLCZDB0(A.LSH000,'4',2,15) QTZD15,--其他诊断7出院西医诊断 SF_BA_GETLCZDB0(A.LSH000,'4',0,15) JBDM15,--主证7疾病编码 SF_BA_GETLCZDB0(A.LSH000,'4',1,15) RYBQ15, --主证7住入院病情 SF_BA_GETLCZDB0(A.LSH000,'1',2,1) MZZD20,--门(急)诊诊断(西医诊断) SF_BA_GETLCZDB0(A.LSH000,'1',0,1) JBBM20,--疾病编码 SF_BA_GETLCZDB0(A.LSH000,'3',2,1) ZYZD20,--主要诊断 SF_BA_GETLCZDB0(A.LSH000,'3',0,1) JBDM20,--疾病代码 SF_BA_GETLCZDB0(A.LSH000,'4',2,1) QTZD120,--其他诊断3出院西医诊断 SF_BA_GETLCZDB0(A.LSH000,'4',0,1) JBDM120,--主证3疾病编码 SF_BA_GETLCZDB0(A.LSH000,'4',2,9) QTZD920, --其他诊断4出院西医诊断 SF_BA_GETLCZDB0(A.LSH000,'4',0,9) JBDM920,--主证4疾病编码 SF_BA_GETLCZDB0(A.LSH000,'4',2,2) QTZD220,--其他诊断5出院西医诊断 SF_BA_GETLCZDB0(A.LSH000,'4',0,2) JBDM220,--主证5疾病编码 SF_BA_GETLCZDB0(A.LSH000,'4',2,10) QTZD1020,--其他诊断6出院西医诊断 SF_BA_GETLCZDB0(A.LSH000,'4',0,10) JBDM1020,--主证6疾病编码 SF_BA_GETLCZDB0(A.LSH000,'4',2,3) QTZD320,--其他诊断7出院西医诊断 SF_BA_GETLCZDB0(A.LSH000,'4',0,3) JBDM320,--主证7疾病编码 SF_BA_GETLCZDB0(A.LSH000,'4',2,11) QTZD1120,--其他诊断7出院西医诊断 SF_BA_GETLCZDB0(A.LSH000,'4',0,11) JBDM1120,--主证7疾病编码 SF_BA_GETLCZDB0(A.LSH000,'4',2,4) QTZD420,--其他诊断7出院西医诊断 SF_BA_GETLCZDB0(A.LSH000,'4',0,4) JBDM420, SF_BA_GETLCZDB0(A.LSH000,'4',2,12) QTZD1220,--其他诊断7出院西医诊断 SF_BA_GETLCZDB0(A.LSH000,'4',0,12) JBDM1220,--主证7疾病编码 SF_BA_GETLCZDB0(A.LSH000,'4',2,5) QTZD520,--其他诊断7出院西医诊断 SF_BA_GETLCZDB0(A.LSH000,'4',0,5) JBDM520,--主证7疾病编码 SF_BA_GETLCZDB0(A.LSH000,'4',2,13) QTZD1320,--其他诊断7出院西医诊断 SF_BA_GETLCZDB0(A.LSH000,'4',0,13) JBDM1320,--主证7疾病编码 SF_BA_GETLCZDB0(A.LSH000,'4',2,6) QTZD620,--其他诊断7出院西医诊断 SF_BA_GETLCZDB0(A.LSH000,'4',0,6) JBDM620,--主证7疾病编码 SF_BA_GETLCZDB0(A.LSH000,'4',2,7) QTZD720,--其他诊断7出院西医诊断 SF_BA_GETLCZDB0(A.LSH000,'4',0,7) JBDM720,--主证7疾病编码 SF_BA_GETLCZDB0(A.LSH000,'4',2,8) QTZD820, SF_BA_GETLCZDB0(A.LSH000,'4',0,8) JBDM820, SF_BA_GETLCZDB0(A.LSH000,'4',2,14) QTZD1420,--其他诊断7出院西医诊断 SF_BA_GETLCZDB0(A.LSH000,'4',0,14) JBDM1420,--主证7疾病编码 SF_BA_GETLCZDB0(A.LSH000,'4',2,15) QTZD1520,--其他诊断7出院西医诊断 SF_BA_GETLCZDB0(A.LSH000,'4',0,15) JBDM1520,--主证7疾病编码 nvl((select jbmc00 from xt_icd900 xt where xt.icd900=a.sszdbm),a.sszdwy) WBYY, A.SSZDBM JBBM1, nvl((select nvl(XT.jbmc01,ZD.FZMC00) from bq_brzdxx zd left join XT_ICDDYB XT on trim(upper(ZD.FZBM00))=XT.ICD100 where XT.YT0000=0 and ZD.ZYID00=A.LSH000 and ZD.ZDLB00='3' and (ZD.ZDM000 like 'C%' or ZD.ZDM000 like 'D%' ) and rownum=1),'-') BLZD,--疾病编码 nvl((select nvl(XT.icd101,ZD.FZBM00) from bq_brzdxx zd left join XT_ICDDYB XT on trim(upper(ZD.FZBM00))=XT.ICD100 where XT.YT0000=0 and ZD.ZYID00=A.LSH000 and ZD.ZDLB00='3' and (ZD.ZDM000 like 'C%' or ZD.ZDM000 like 'D%' ) and rownum=1),'-') JBBM2,--疾病编码 A.Blh000 BLH,--病理号 decode(A.SFYWGM,'无','1','有','2','1') YWGM, A.GMYW00 GMYW,--过敏药物西医诊断 decode(A.SFSJ00,'是','1','否','2','2') SWHZSJ,--死亡患者尸检 decode(A.XX0000,'A型','1','B型','2','O型','3','AB型','4','未检','6','5') XX, --血型 decode(A.RH0000,'阴性','1','阳性','2','不详','3','未做','4','3') RH, --RH A.KZRMC0 KZR,--科主任 A.FZRMC0 ZRYS,--副主任 A.ZZYSMC ZZYS,--主治医师 A.ZYYSMC ZYYS,--住院医师 A.ZRHSMC ZRHS, --责任护士 A.JXYSMC JXYS, --进修医师 A.YJSMC0 SXYS,--实习医师 A.SHRXM0 BMY,--编码员 decode(A.BAZL00,'甲','1','乙','2','丙','3') BAZL00 , A.ZKYSMC ZKYS,--质控医师 A.ZKHSMC ZKHS, --质控护士 A.ZKRQ00 ZKRQ,--质控日期 SF_BA_GETBABRSSXX(A.LSH000,0,1) SSJCZBM1,--手术操作编码1 SF_BA_GETBABRSSXX(A.LSH000,1,1) SSJCZRQ1,--手术操作日期1 SF_BA_GETBABRSSXX(A.LSH000,2,1) SSJB1,--手术级别1 SF_BA_GETBABRSSXX(A.LSH000,3,1) SSJCZMC1,--手术操作名称1 SF_BA_GETBABRSSXX(A.LSH000,6,1) SZ1,--术者1 SF_BA_GETBABRSSXX(A.LSH000,7,1) YZ1,--I助1 SF_BA_GETBABRSSXX(A.LSH000,8,1) EZ1,--II助1 SF_BA_GETBABRSSXX(A.LSH000,14,1) QKDJ1, SF_BA_GETBABRSSXX(A.LSH000,13,1) QKYHLB1, SF_BA_GETBABRSSXX(A.LSH000,16,1) MZFS1,--麻醉方式1 SF_BA_GETBABRSSXX(A.LSH000,12,1) MZYS1,--麻醉医师1 SF_BA_GETBABRSSXX(A.LSH000,0,2) SSJCZBM2,--手术操作编码2 SF_BA_GETBABRSSXX(A.LSH000,1,2) SSJCZRQ2,--手术操作日期2 SF_BA_GETBABRSSXX(A.LSH000,2,2) SSJB2,--手术级别2 SF_BA_GETBABRSSXX(A.LSH000,3,2) SSJCZMC2,--手术操作名称2 SF_BA_GETBABRSSXX(A.LSH000,6,2) SZ2,--术者2 P4916 SF_BA_GETBABRSSXX(A.LSH000,7,2) YZ2,--I助2 P4917 SF_BA_GETBABRSSXX(A.LSH000,8,2) EZ2,--II助2 SF_BA_GETBABRSSXX(A.LSH000,14,2) QKDJ2, SF_BA_GETBABRSSXX(A.LSH000,13,2) QKYHLB2, SF_BA_GETBABRSSXX(A.LSH000,16,2) MZFS2,--麻醉方式1 SF_BA_GETBABRSSXX(A.LSH000,12,2) MZYS2,--麻醉医师2 SF_BA_GETBABRSSXX(A.LSH000,0,3) SSJCZBM3,--手术操作编码3 SF_BA_GETBABRSSXX(A.LSH000,1,3) SSJCZRQ3,--手术操作日期3 SF_BA_GETBABRSSXX(A.LSH000,2,3) SSJB3,--手术级别3 SF_BA_GETBABRSSXX(A.LSH000,3,3) SSJCZMC3,--手术操作名称3 SF_BA_GETBABRSSXX(A.LSH000,6,3) SZ3,--术者3 SF_BA_GETBABRSSXX(A.LSH000,7,3) YZ3,--I助3 SF_BA_GETBABRSSXX(A.LSH000,8,3) EZ3,--II助3 SF_BA_GETBABRSSXX(A.LSH000,14,3) QKDJ3, SF_BA_GETBABRSSXX(A.LSH000,13,3) QKYHLB3, SF_BA_GETBABRSSXX(A.LSH000,16,3) MZFS3,--麻醉方式3 SF_BA_GETBABRSSXX(A.LSH000,12,3) MZYS3,--麻醉医师3 SF_BA_GETBABRSSXX(A.LSH000,0,4) SSJCZBM4,--手术操作编码4 SF_BA_GETBABRSSXX(A.LSH000,1,4) SSJCZRQ4,--手术操作日期4 decode(SF_BA_GETBABRSSXX(A.LSH000,2,4),'-','','1','1','2','2','3','3','4','4') SSJB4,--手术级别4 SF_BA_GETBABRSSXX(A.LSH000,3,4) SSJCZMC4,--手术操作名称4 SF_BA_GETBABRSSXX(A.LSH000,6,4) SZ4,--术者4 SF_BA_GETBABRSSXX(A.LSH000,7,4) YZ4,--I助4 SF_BA_GETBABRSSXX(A.LSH000,8,4) EZ4,--II助4 SF_BA_GETBABRSSXX(A.LSH000,14,4) QKDJ4, SF_BA_GETBABRSSXX(A.LSH000,13,4) QKYHLB4, SF_BA_GETBABRSSXX(A.LSH000,16,4) MZFS4,--麻醉方式4 SF_BA_GETBABRSSXX(A.LSH000,12,4) MZYS4,--麻醉医师4 SF_BA_GETBABRSSXX(A.LSH000,0,5) SSJCZBM5, SF_BA_GETBABRSSXX(A.LSH000,1,5) SSJCZRQ5, decode(SF_BA_GETBABRSSXX(A.LSH000,2,5),'-','','1','1','2','2','3','3','4','4') SSJB5, SF_BA_GETBABRSSXX(A.LSH000,3,5) SSJCZMC5, SF_BA_GETBABRSSXX(A.LSH000,6,5) SZ5, SF_BA_GETBABRSSXX(A.LSH000,7,5) YZ5, SF_BA_GETBABRSSXX(A.LSH000,8,5) EZ5, SF_BA_GETBABRSSXX(A.LSH000,14,5) QKDJ5, SF_BA_GETBABRSSXX(A.LSH000,13,5) QKYHLB5, SF_BA_GETBABRSSXX(A.LSH000,16,5) MZFS5, SF_BA_GETBABRSSXX(A.LSH000,12,5) MZYS5, SF_BA_GETBABRSSXX(A.LSH000,0,6) SSJCZBM6, SF_BA_GETBABRSSXX(A.LSH000,1,6) SSJCZRQ6, decode(SF_BA_GETBABRSSXX(A.LSH000,2,6),'-','','1','1','2','2','3','3','4','4') SSJB6, SF_BA_GETBABRSSXX(A.LSH000,3,6) SSJCZMC6, SF_BA_GETBABRSSXX(A.LSH000,6,6) SZ6, SF_BA_GETBABRSSXX(A.LSH000,7,6) YZ6, SF_BA_GETBABRSSXX(A.LSH000,8,6) EZ6, SF_BA_GETBABRSSXX(A.LSH000,14,6) QKDJ6, SF_BA_GETBABRSSXX(A.LSH000,13,6) QKYHLB6, SF_BA_GETBABRSSXX(A.LSH000,16,6) MZFS6, SF_BA_GETBABRSSXX(A.LSH000,12,6) MZYS6, SF_BA_GETBABRSSXX(A.LSH000,0,7) SSJCZBM7, SF_BA_GETBABRSSXX(A.LSH000,1,7) SSJCZRQ7, decode(SF_BA_GETBABRSSXX(A.LSH000,2,7),'-','','1','1','2','2','3','3','4','4') SSJB7, SF_BA_GETBABRSSXX(A.LSH000,3,7) SSJCZMC7, SF_BA_GETBABRSSXX(A.LSH000,6,7) SZ7, SF_BA_GETBABRSSXX(A.LSH000,7,7) YZ7, SF_BA_GETBABRSSXX(A.LSH000,8,7) EZ7, SF_BA_GETBABRSSXX(A.LSH000,14,7) QKDJ7, SF_BA_GETBABRSSXX(A.LSH000,13,7) QKYHLB7, SF_BA_GETBABRSSXX(A.LSH000,16,7) MZFS7, SF_BA_GETBABRSSXX(A.LSH000,12,7) MZYS7, SF_BA_GETBABRSSXX(A.LSH000,0,8) SSJCZBM8, SF_BA_GETBABRSSXX(A.LSH000,1,8) SSJCZRQ8, decode(SF_BA_GETBABRSSXX(A.LSH000,2,8),'-','','1','1','2','2','3','3','4','4') SSJB8, SF_BA_GETBABRSSXX(A.LSH000,3,8) SSJCZMC8, SF_BA_GETBABRSSXX(A.LSH000,6,8) SZ8, SF_BA_GETBABRSSXX(A.LSH000,7,8) YZ8, SF_BA_GETBABRSSXX(A.LSH000,8,8) EZ8, SF_BA_GETBABRSSXX(A.LSH000,14,8) QKDJ8, SF_BA_GETBABRSSXX(A.LSH000,13,8) QKYHLB8, SF_BA_GETBABRSSXX(A.LSH000,16,8) MZFS8, SF_BA_GETBABRSSXX(A.LSH000,12,8) MZYS8, ----- SF_BA_GETBABRSSXX(A.LSH000,0,1) SSJCZBM120, SF_BA_GETBABRSSXX(A.LSH000,3,1) SSJCZMC120, SF_BA_GETBABRSSXX(A.LSH000,0,2) SSJCZBM220, SF_BA_GETBABRSSXX(A.LSH000,3,2) SSJCZMC220, SF_BA_GETBABRSSXX(A.LSH000,0,3) SSJCZBM320, SF_BA_GETBABRSSXX(A.LSH000,3,3) SSJCZMC320, SF_BA_GETBABRSSXX(A.LSH000,0,4) SSJCZBM420, SF_BA_GETBABRSSXX(A.LSH000,3,4) SSJCZMC420, SF_BA_GETBABRSSXX(A.LSH000,0,5) SSJCZBM520, SF_BA_GETBABRSSXX(A.LSH000,3,5) SSJCZMC520, SF_BA_GETBABRSSXX(A.LSH000,0,6) SSJCZBM620, SF_BA_GETBABRSSXX(A.LSH000,3,6) SSJCZMC620, SF_BA_GETBABRSSXX(A.LSH000,0,7) SSJCZBM720, SF_BA_GETBABRSSXX(A.LSH000,3,7) SSJCZMC720, SF_BA_GETBABRSSXX(A.LSH000,0,8) SSJCZBM820, SF_BA_GETBABRSSXX(A.LSH000,3,8) SSJCZMC820, -- decode(A.LYFS00,'医嘱离院','1','医嘱转院','2','医嘱转社区卫生服务机构','3','非医嘱离院','4','死亡','5','其他','9','9') LYFS00, decode(A.LYFS00,'医嘱转院',A.ZYYLJG,'-') YLJG, --医嘱转院，拟接收医疗机构名称 decode(A.LYFS00,'医嘱转社区卫生服务机构',A.ZYYLJG,'-') WSY_YLJG,--医嘱转社区卫生服务机构/乡镇卫生院，拟接收医疗机构名称 decode(A.ZZYJH0,'有','2','1') SFZZYJH, --出院31天内再住院计划 A.ZZYMD0 MD, --出院31天内再住院计划目的 A.HMRYQD RYQ_T,--入院前时间（颅脑损伤患者昏迷时间 天） A.HMRYQH RYQ_XS,--入院前多少小时（颅脑损伤患者昏迷时间 小时） A.HMRYQM RYQ_FZ, --入院前多少小分钟（颅脑损伤患者昏迷时间 分钟） A.HMRYHD RYH_T,--入院后多少天（颅脑损伤患者昏迷时间 天） A.HMRYHH RYH_XS,--入院后多少小时（颅脑损伤患者昏迷时间 小时） A.HMRYHM RYH_FZ,--入院后多少分钟（颅脑损伤患者昏迷时间 分钟） nvl(FY.ZYZFY0,0) ZFY, --总费用 nvl(FY.ZFJE00,0) ZFJE, --自付金额 nvl(FY.YBYLFW,0) YLFUF, --一般医疗服务费 nvl(FY.YBZLCZ,0) ZLCZF, --一般治疗操作费 nvl(FY.HLF000,0) HLF, --护理费 nvl(FY.QTFY00,0) QTFY, --其他费 nvl(FY.BLZDF0,0) BLZDF, --病理诊断费 nvl(FY.SYSZDF,0) SYSZDF, --实验室诊断费 nvl(FY.YXXZDF,0) YXXZDF, --影像学诊断费 nvl(FY.LCZDXM,0) LCZDXMF, --临床诊断项目费 nvl(FY.FSSZLF,0) FSSZLXMF, --非手术治疗项目费 nvl(FY.LCWLZL,0) WLZLF, --临床物理治疗费 nvl(FY.SSZLF0,0) SSZLF, --手术治疗费 nvl(FY.MZF000,0) MAF, --麻醉费 nvl(FY.SSF000,0) SSF, --手术费 nvl(FY.KFF000,0) KFF, --康复费 nvl(FY.ZYZL00,0) ZYZLF,--中医治疗 nvl(FY.XYF000,0) XYF, --西药费 nvl(FY.KJYWF0,0) KJYWF, --抗菌药物费 nvl(FY.ZCYF00,0) ZCYF, --中成药费 nvl(FY.ZCYF10,0) ZCYF1, --中草药费 nvl(FY.XF0000,0) XF, --血费 nvl(FY.BDBLZP,0) BDBLZPF, --白蛋白类制品费 nvl(FY.QDBLZP,0) QDBLZPF, --球蛋白类制品费 nvl(FY.NXYZZP,0) NXYZLZPF, --凝血因子类制品费 nvl(FY.XBYZZP,0) XBYZLZPF, --细胞因子类制品费 nvl(FY.JCYCLF,0) HCYYCLF, --检查用一次性医用材料费 nvl(FY.ZLYCLF,0) YYCLF, --治疗用一次性医用材料费 nvl(FY.SSYCLF,0) YCXYYCLF, --手术用一次性医用材料费 nvl(FY.QTF000,0) QTF,--综合医疗服务类(1)其他费用 a.rykb00, decode(instr(zkkb00,';'),0,zkkb00,substr(zkkb00,1,instr(zkkb00,';')-1)) zkkb00, '' zkkbmc2, '' zkkb02, '' zkkbmc3, '' zkkb03, a.cykb00, '' ptcwf0, '' zzjhcwf, '' hxjsysj, '' zzjhbflx1, '' jzzjhbjsj1, '' czzjhbfsj1, '' zzjhbflx2, '' jzzjhbjsj2, '' czzjhbfsj2, '' zzjhbflx3, '' jzzjhbjsj3, '' czzjhbfsj3, '' zksjnyr1, '' zksjs1, '' zkbfmc1, '' zksjnyr2, '' zksjs2, '' zkbfmc2, '' zksjnyr3, '' zksjs3, '' zkbfmc3, '' sjhl00, '' ejhl00, '' yjhl00, '' tjhl00, '' sfzzjh, '' cuuxx, '' nicuxx, '' eicuxx, '' sicuxx, '' picuxx, '' qtzzjhxx, '' qjcs00, '' qjcgcs, '' gjjbsy, '' gjjbsybm, '' gjjbsy1, '' gjjbsybm1, '' gjjbsy2, '' gjjbsybm2, '' gjjbsy3, '' gjjbsybm3, '' gjjbsy4, '' gjjbsybm4, '' gjjbsy5, '' gjjbsybm5, '' gjjbsy6, '' gjjbsybm6, '' gjjbsy7, '' gjjbsybm7, '' gjjbsy8, '' gjjbsybm8, '' gjjbsy9, '' gjjbsybm9, '' gjjbsy10, '' gjjbsybm10, '' gjjbsy11, '' gjjbsybm11, '' gjjbsy12, '' gjjbsybm12, '' gjjbsy13, '' gjjbsybm13, '' gjjbsy14, '' gjjbsybm14, '' gjjbsy15, '' gjjbsybm15, '' cchibm1, '' cchimc1, '' cchibm2, '' cchimc2, '' cchibm3, '' cchimc3, '' cchibm4, '' cchimc4, '' cchibm5, '' cchimc5, '' cchibm6, '' cchimc6, '' cchibm7, '' cchimc7, '' cchibm8, '' cchimc8, '' hxjsjf, '' zcf000, '' mjzlczd, '' mjzlczdbm from BA_BRZYXX A join BA_SYFYB0 FY on A.LSH000=FY.ZYID00 join zy_brxxb0 zy on a.lsh000=zy.zyid00 where zy.brzt00<>'9'; create or replace view vw_zybasy_wticd as select icd16.icd016 || icd16.fjbm16 icd016, icd16.jbmc16 jbmc16, icddyb."ICD100",icddyb."JBMC00",icddyb."ICD101",icddyb."JBMC01",icddyb."LB0000",icddyb."LBMC00" from sd_hospital.XT_ICD000 icd16, SD_HOSPITAL.Xt_Icddyb icddyb where icddyb.yt0000=1 and icd16.jbmc16 = icddyb.jbmc01; create or replace function SF_BA_GETZDMRYBQZLXG20 -- MODIFICATION HISTORY -- Person Date Comments -- xuxq 2019.05.10 Create BAGL-20190415-001 -- xuxq 2019.12.03 新增诊断日期 -- shienq 2020.07.20 数据改为取 bq_baszdb -- xuxm 2020.11.03 查询速度优化 ( Pzyid00 in number , -- Pzdlb00 in char , Pfhlx00 in number , --返回类型 0 诊断码 1 入院病情 2 诊断名称 3 治疗效果 5 诊断日期 Pxh0000 in number ) return varchar2 is Vzdmc00 BQ_BASZDB.ZDMC00%type; begin begin if Pfhlx00=0 then--诊断码 select nvl(xt.ICD101,ZDM000) into Vzdmc00 from VW_BA_BASZDB zd left join XT_ICDDYB xt on zd.Zdm000=xt.ICD100 where xt.YT0000=0 and ZYID00=PZYID00 and ZDLB00=PZDLB00 and XH0000=Pxh0000 and rownum<=1; elsif Pfhlx00=1 then --入院病情 select decode(RYBQ00,'有','1','临床未确定','2','情况不明','3','无','4','1','1','2','2','3','3','4','4','3') into Vzdmc00 from VW_BA_BASZDB a where ZYID00=PZYID00 and ZDLB00=PZDLB00 and XH0000=PXH0000; elsif Pfhlx00=2 then --诊断名称 select nvl(xt.JBMC01,ZDMC00) into Vzdmc00 from VW_BA_BASZDB zd left join XT_ICDDYB xt on zd.ZDM000=xt.ICD100 where xt.YT0000=0 and ZYID00=PZYID00 and ZDLB00=PZDLB00 and XH0000=Pxh0000 and rownum<=1; elsif Pfhlx00=3 then --治疗效果 select decode(ZLXG00,'1','1','2','2','3','3','4','4','5','9','6','9','7','9','8','9') into Vzdmc00 from VW_BA_BASZDB a where ZYID00=PZYID00 and ZDLB00=PZDLB00 and XH0000=PXH0000; elsif Pfhlx00=4 then select decode(RYBQ00,'有','1','临床未确定','2','情况不明','3','无','4','3') into Vzdmc00 from VW_BA_BASZDB a where ZYID00=PZYID00 and ZDLB00=PZDLB00 and XH0000=PXH0000; elsif Pfhlx00=5 then --诊断日期 select ZDRQ00 into Vzdmc00 from VW_BA_BASZDB a where ZYID00=PZYID00 and ZDLB00=PZDLB00 and XH0000=PXH0000; elsif Pfhlx00=6 then select decode(ZLXG00,'1','1','2','2','3','3','4','4','9') into Vzdmc00 from VW_BA_BASZDB a where ZYID00=PZYID00 and ZDLB00=PZDLB00 and XH0000=Pxh0000; end if; exception when others then Vzdmc00 := ''; end; return Vzdmc00; end; create or replace function SF_BA_GETLCZDB0 -- MODIFICATION HISTORY -- Person Date Comments -- xuxq 2019.07.23 BAGL-20190612-001 --xuxq 2019.12.03 新增 ( PZYID00 in number , PZDLB00 in char , PFHLX00 in number , --返回类型 0 ZDM000 1 RYBQ00 2 ZDMC00 3 ZLXG00 PXH0000 in number ) return varchar2 is VZDMC00 BQ_BRZDXX.ZDMC00%type; --按顺序号取诊断的编码，主要是BQ_BRZDXX.PLXH00这个字段的值不靠谱，导致传接口有的时候数据传不过去 begin begin if Pfhlx00=0 then select ZDM000 into Vzdmc00 from (select rownum XH0000,a.* from ( -- select decode(ZDM000,'Z03.800y001','Z03.800','Z03.800x001','Z03.800',xt.icd101) ZDM000 from BQ_BRZDXX zd,Xt_Icddyb xt --where ZYID00=Pzyid00 and zdm000 is not null and zd.ZDLB00=Pzdlb00 and xt.icd100=zd.zdm000 order by PLXH00,ID0000) a) where XH0000=Pxh0000 and rownum=1 ; select xt.icd101 ZDM000 from BQ_BRZDXX zd,Xt_Icddyb xt where xt.YT0000=0 and ZYID00=Pzyid00 and zdm000 is not null and zd.ZDLB00=Pzdlb00 and (xt.icd100=zd.zdm000) order by PLXH00,ID0000) a) where XH0000=Pxh0000 and rownum=1 ; elsif Pfhlx00=1 then select decode(RYBQ00,'有','1','临床未确定','2','情况不明','3','无','4','1','1','2','2','3','3','4','4') into Vzdmc00 from (select rownum XH0000,a.* from (select RYBQ00 from BQ_BRZDXX where ZYID00=Pzyid00 and ZDLB00=Pzdlb00 order by PLXH00,ID0000) a) where XH0000=Pxh0000; elsif Pfhlx00=2 then select jbmc00 into Vzdmc00 from (select rownum XH0000,a.* from (select xt.jbmc01 jbmc00 from BQ_BRZDXX zd ,Xt_Icddyb xt where xt.YT0000=0 and ZYID00=Pzyid00 and zd.ZDLB00=Pzdlb00 and (xt.icd100=zd.zdm000) order by PLXH00,ID0000) a) where XH0000=Pxh0000 and rownum=1 ; elsif Pfhlx00=3 then select decode(ZLXG00,'1','1','2','2','3','3','4','4','5','9','6','9','7','9','8','9') into Vzdmc00 from (select rownum XH0000,a.* from (select ZLXG00 from BQ_BRZDXX where ZYID00=Pzyid00 and ZDLB00=Pzdlb00 order by PLXH00,ID0000) a) where XH0000=Pxh0000; elsif Pfhlx00=5 then select ZDRQ00 into Vzdmc00 from (select rownum XH0000,a.* from (select ZDRQ00 from BQ_BRZDXX where ZYID00=PZYID00 and ZDLB00=PZDLB00 order by PLXH00,ID0000) a) where XH0000=PXH0000; elsif Pfhlx00=6 then select decode(ZLXG00,'1','1','2','2','3','3','4','4','9') into Vzdmc00 from (select rownum XH0000,a.ZLXG00 from (select ZLXG00 from BQ_BRZDXX where ZYID00=Pzyid00 and ZDLB00=Pzdlb00 order by PLXH00,ID0000) a) where XH0000=Pxh0000; end if; exception when others then Vzdmc00 := ''; end; return VZDMC00; end;