---住院医生站5.0左侧列表跟踪语句-- select * from ( select t1.*,SF_JUDGE_RESULT_ZY(t1.PYRQ00,t1.YBH000) LX0000,SF_JUDGE_RESULT_JZ(t1.PYRQ00,t1.YBH000) SFJJ00,DECODE(t1.SFJM00,'N','Y',DECODE((Select count(*) From VW_JY_YGQXZD where YGBH00='1913' and QXLX00='JGCL' and QXGNDH='CKJM' and YQDH00=t1.YQDH00),0,'N','Y')) YXDY00 from ( select distinct a.brxm00,a.txm000,a.YEXM00,a.sqksmc,a.brlx00,a.ch0000,a.yqdh00,a.yxbz00,a.dycs00,a.ybh000,to_char(to_date(a.pyrq00,'YYYYMMDD'),'YYYY-MM-DD') pyrq01,a.pyrq00,a.jgzt00,a.sftj00,a.SFJM00, decode(A.JGZT00,'6','Y','N') dy0000,a.BGYSZ0,a.SFYB00,a.MZZYBZ,to_char(to_date(a.cbgrq0,'YYYYMMDD'),'YYYY-MM-DD') CBGRQ1,a.CBGRQ0,a.ZYHGHH ,SUBSTR(SF_JY_UNION_XMMC00_BBK(txm000,mzzybz,zyhghh,'','',0,1),1,120) xmmc00,a.sqks00,a.SQBQ00,a.ZLXMZH from VW_JY_YW0000 a where A.JGZT00 in ('9','6','8') and A.PYRQ00 between '20180404' and '20190411' and A.ZYHGHH = '1806974' and ( ( A.SQKS00+0 = '487' or A.SQBQ00+0 = '487') )) t1 ) order by CBGRQ0 desc,yqdh00,ybh000,ch0000 asc ---住院医生站5.0右侧结果跟踪语句-- SELECT * FROM VW_JY_JYJG00 WHERE ybh000='HI0006' and pyrq00='20181106' ORDER BY DYSX00 ---住院医生站5.0视图VW_JY_JYJG00-- CREATE OR REPLACE VIEW SD_HOSPITAL.VW_JY_JYJG00 ( JGID00, JYYWID, YJDJH0, ZLXMID, BRID00, ZLXMJC, YBH000, PYRQ00, DYSX00, BGXMID, BZBM00, BGXMMC, ZWMC00, YWMC00, CKXX00, CKSX00, CKFW00, JGLX00, CKZNL0, CKZXB0, CKZBB0, CKZBL0, JD0000, XTZY00, XMSM00, YSJYJG, JYJG00, JYJG01, OD0000, BGJYJG, JGDW00, JGZCBZ, YQDH00, YQMC00, YQFLZM, YQJC00, YQSM00, SECTOR, CUPNUM, PHOTO1, PHOTO2, PHOTO3, BGRQ00, BGSJ00, SHR000, JGBZ00, JYFF00, YQJCRQ, JJZSX0, JJZXX0, SFJM00, SFCJJZ, FCXH00, CZR000, BBID00, SFKY00, BRXM00, TYDWMC, MZZYBZ, SFTJ00, BRXB00, NL0000, ZYHGHH, BRLX00, NLDX00, SQYSXM, SQKS00, SQKSMC, SQBQ00, SQBQMC, ZDID00, ZDMC00, BBMC00, XB0000, CSRQ00, JGZT00, JGZTMC, JTDAH0, JGHRBZ, SFXSHQ, JKBH00, TXM000, BBSDRQ, BBSDSJ, SJLY00, ZYZBID, CUTOFF, SCO000, BGSX00, BTJC00, WJZSH0 ) AS select A.JGID00, --结果ID:SEQ_JY_JYJG00_JGID00 JYYWID, --检验的业务ID (select YJDJH0 from JY_YW0000 where JYYWID=A.JYYWID) YJDJH0, A.ZLXMID, --申请项目代码 (select BRID00 from JY_YW0000 where YBH000=A.YBH000 and PYRQ00=A.PYRQ00 and rownum=1) BRID00, (select ZLXMJC from BM_ZLZD00 where ZLXMID=A.ZLXMID) ZLXMJC, --项目名称 A.YBH000, A.PYRQ00, B.DYSX00, --打印顺序(报告顺序) A.BGXMID, --项目代码 B.BZBM00, decode((select JGXSFS from JY_JYYQZD where YQDH00=A.YQDH00),'0',NVL(BZBM00,ZWMC00),NVL(ZWMC00,BZBM00))||decode(a.DYYBH0,'','','('||a.DYYBH0||')') BGXMMC, B.ZWMC00, B.YWMC00, A.CKXX00, --参考上限 A.CKSX00, --参考下限 decode(B.CKZBL0,'N',A.CKFW00,substrB(SF_JY_GET_BLCKZ0(A.PYRQ00,A.YBH000,A.BGXMID),1,250)) CKFW00, --参考范围 B.JGLX00, -- B.CKZNL0, -- B.CKZXB0, -- B.CKZBB0, B.CKZBL0, B.JD0000, B.XTZY00, B.XMSM00, A.YSJYJG, -- A.JYJG00, --检验结果值 A.JYJG01, A.OD0000, decode(B.JD0000,-1,SF_JY_CONVERT_RESULT(-1,to_number(trim(JYJG00))), -2,SF_JY_CONVERT_RESULT(-2,to_number(trim(JYJG00))),JYJG00) BGJYJG , --报告检验结果 B.DW0000 JGDW00, --检验结果单位 --decode(B.JGLX00,'L',decode((select trim(VALUE0) from XT_XTCS00 where NAME00='JY_SFDYZCBZ'),'N',null,JGZCBZ),JGZCBZ) JGZCBZ, --结果正常标志 decode (b.jglx00,'L',decode ((select trim (value0) from xt_xtcs00 where name00 = 'JY_SFDYZCBZ'), 'N', null, jgzcbz), decode ( nvl((select INSTR (','||trim (value0)||',',','||a.bgxmid||',') from xt_xtcs00 where name00 = 'JY_XSLJZBGXMID'),0), 0,jgzcbz,decode(NVL((select INSTR (','||trim (value0)||',',','||a.bgxmid||',') from xt_xtcs00 where name00 = 'JY_XSLJZBGXMID' and to_number(a.jyjg00) between to_number(a.ckxx00) and to_number(a.cksx00)),0),0, jgzcbz, (select trim (value0) from xt_xtcs00 where name00 = 'JY_LJZXSFS')) )) jgzcbz, A.YQDH00, --仪器代号,工输入结果时为'手工' (select YQMC00 from JY_JYYQZD where YQDH00=A.YQDH00) YQMC00, (select YQFLZM from JY_JYYQZD where YQDH00=A.YQDH00) YQFLZM, (select YQJC00 from JY_JYYQZD where YQDH00=A.YQDH00) YQJC00, (select YQSM00 from JY_JYYQZD where YQDH00=A.YQDH00) YQSM00, A.SECTOR, A.CUPNUM, A.PHOTO1, A.PHOTO2, A.PHOTO3, A.BGRQ00, --报告日期 A.BGSJ00, --报告时间 A.SHR000, -- A.JGBZ00, --报告备注(对报告结果的说明) nvl(A.JYFF00,B.JYFF00) JYFF00, A.YQJCRQ, A.JJZSX0, A.JJZXX0, (select SFJM00 from JY_YW0000 where YBH000=A.YBH000 and PYRQ00=A.PYRQ00 and rownum=1) SFJM00, A.SFCJJZ, 0 FCXH00, 0 CZR000, (select BBID00 from JY_YW0000 where YBH000=A.YBH000 and PYRQ00=A.PYRQ00 and rownum=1) BBID00, (select SFKY00 from JY_YW0000 where YBH000=A.YBH000 and PYRQ00=A.PYRQ00 and rownum=1) SFKY00, (select BRXM00 from JY_YW0000 where YBH000=A.YBH000 and PYRQ00=A.PYRQ00 and rownum=1) BRXM00, (select TYDWMC from VW_JY_YW0000 where YBH000=A.YBH000 and PYRQ00=A.PYRQ00 and rownum=1) TYDWMC, (select MZZYBZ from JY_YW0000 where YBH000=A.YBH000 and PYRQ00=A.PYRQ00 and rownum=1) MZZYBZ, (select SFTJ00 from JY_YW0000 where YBH000=A.YBH000 and PYRQ00=A.PYRQ00 and rownum=1) SFTJ00, (select decode(XB0000,'M','男','女') from JY_YW0000 where YBH000=A.YBH000 and PYRQ00=A.PYRQ00 and rownum=1) BRXB00, (select to_number(substr(substr(SF_JY_CSRQTONL(CSRQ00,SQRQ00),1,length(SF_JY_CSRQTONL(CSRQ00,SQRQ00)) -1),1,5)) NL0000 from JY_YW0000 where YBH000=A.YBH000 and PYRQ00=A.PYRQ00 and rownum=1) NL0000, --年龄 (select ZYHGHH from JY_YW0000 where YBH000=A.YBH000 and PYRQ00 = A.PYRQ00 and rownum=1) ZYHGHH, (select BRLX00 from VW_JY_YW0000 where YBH000=A.YBH000 and PYRQ00=A.PYRQ00 and rownum=1) BRLX00, (select NL0000||NLDWMC from VW_JY_YW0000 where YBH000=A.YBH000 and PYRQ00=A.PYRQ00 and rownum=1) NLDX00, (select SQYSXM from VW_JY_YW0000 where YBH000=A.YBH000 and PYRQ00 = A.PYRQ00 and rownum=1) SQYSXM, (select SQKS00 from VW_JY_YW0000 where YBH000=A.YBH000 and PYRQ00 = A.PYRQ00 and rownum=1) SQKS00, (select SQKSMC from VW_JY_YW0000 where YBH000=A.YBH000 and PYRQ00 = A.PYRQ00 and rownum=1) SQKSMC, (select SQBQ00 from VW_JY_YW0000 where YBH000=A.YBH000 and PYRQ00 = A.PYRQ00 and rownum=1) SQBQ00, (select SQBQMC from VW_JY_YW0000 where YBH000=A.YBH000 and PYRQ00 = A.PYRQ00 and rownum=1) SQBQMC, (select ZDID00 from VW_JY_YW0000 where YBH000=A.YBH000 and PYRQ00 = A.PYRQ00 and rownum=1) ZDID00, (select ZDMC00 from VW_JY_YW0000 where YBH000=A.YBH000 and PYRQ00 = A.PYRQ00 and rownum=1) ZDMC00, (select BBMC00 from VW_JY_YW0000 where YBH000=A.YBH000 and PYRQ00 = A.PYRQ00 and rownum=1) BBMC00, (select XB0000 from VW_JY_YW0000 where YBH000=A.YBH000 and PYRQ00 = A.PYRQ00 and rownum=1) XB0000, (select CSRQ00 from VW_JY_YW0000 where YBH000=A.YBH000 and PYRQ00 = A.PYRQ00 and rownum=1) CSRQ00, (select JGZT00 from VW_JY_YW0000 where YBH000=A.YBH000 and PYRQ00 = A.PYRQ00 and rownum=1) JGZT00 , (select JGZTMC from VW_JY_YW0000 where YBH000=A.YBH000 and PYRQ00 = A.PYRQ00 and rownum=1) JGZTMC , (select JTDAH0 from VW_JY_YW0000 where YBH000=A.YBH000 and PYRQ00 = A.PYRQ00 and rownum=1) JTDAH0, B.JGHRBZ, B.SFXSHQ, B.JKBH00, (select TXM000 from JY_YW0000 where YBH000=A.YBH000 and PYRQ00=A.PYRQ00 and rownum=1) TXM000, (select BBSDRQ from JY_YW0000 where YBH000=A.YBH000 and PYRQ00=A.PYRQ00 and rownum=1) BBSDRQ, (select BBSDSJ from JY_YW0000 where YBH000=A.YBH000 and PYRQ00=A.PYRQ00 and rownum=1) BBSDSJ, A.SJLY00, --数据来源 (select ZYZBID from JY_JYYQZD where yqdh00=a.YQDH00 and rownum=1) ZYZBID, CUTOFF, SF_JY_HQODCU00(A.OD0000,A.CUTOFF) SCO000, BGSX00, B.BTJC00, --标题简称 decode(WJZSH0,'1','1','0') WJZSH0 from JY_JYJG00 A, JY_BGXMZD B where A.BGXMID = B.BGXMID and not exists (select 1 from jy_jyyqzd c where a.yqdh00=c.yqdh00 and c.yqmc00 like 'BICLIAISON%') /