nvl((select t.hosp_name from bas_org@TO_NW t where t.hosp_id = a.jddw01),'NULL') as jddw02,---
nvl(b.csrq01,to_date('2000-01-01','yyyy-mm-dd')),---
p01.ptsjjdm,--PKG_NW_PTJK_COMM.uf_nw2ptzd('GW','','LB001',b.grxb01,'3') as grxb01,--
p01.ptsjjmc,--PKG_NW_PTJK_COMM.uf_nw2ptzd('GW','','LB001',b.grxb01,'4') as grxbmc,---
p22.ptsjjdm,---
p22.ptsjjmc,---
nvl(b.sfzh01,'NULL'),---
b.gzdw01,---
substr(b.grdh01,1,11),---
b.lxr001,---
b.lxdh01,---
p02.ptsjjdm,--PKG_NW_PTJK_COMM.uf_nw2ptzd('GW','','LB002',b.czlx01,'3'),---
p02.ptsjjmc,--PKG_NW_PTJK_COMM.uf_nw2ptzd('GW','','LB002',b.czlx01,'4'),---
p03.ptsjjdm,--PKG_NW_PTJK_COMM.uf_nw2ptzd('GW','','LB004',b.grxx01,'3'),---
p03.ptsjjmc,--PKG_NW_PTJK_COMM.uf_nw2ptzd('GW','','LB004',b.grxx01,'4'),---
p04.ptsjjdm,--PKG_NW_PTJK_COMM.uf_nw2ptzd('GW','','LB005',b.RHYX01,'3'),---
p04.ptsjjmc,--PKG_NW_PTJK_COMM.uf_nw2ptzd('GW','','LB005',b.RHYX01,'4'),---
p05.ptsjjdm,--PKG_NW_PTJK_COMM.uf_nw2ptzd('GW','','LB006',b.whcd01,'3'),---
p05.ptsjjmc,--PKG_NW_PTJK_COMM.uf_nw2ptzd('GW','','LB006',b.whcd01,'4'),---
p06.ywsjjdm,--PKG_NW_PTJK_COMM.uf_nw2ptzd('GW','','LB007',b.grzy01,'3'),---
p06.ywsjjmc,--PKG_NW_PTJK_COMM.uf_nw2ptzd('GW','','LB007',b.grzy01,'4'),---
p07.ptsjjdm,--PKG_NW_PTJK_COMM.uf_nw2ptzd('GW','','LB008',b.hyzk01,'3'),---
p07.ptsjjmc,--PKG_NW_PTJK_COMM.uf_nw2ptzd('GW','','LB008',b.hyzk01,'4'),---
p08.ptsjjdm,--PKG_NW_PTJK_COMM.uf_nw2ptzds('GW','','LB009',b.yblx01,'3'),---
p08.ptsjjmc,--PKG_NW_PTJK_COMM.uf_nw2ptzds('GW','','LB009',b.yblx01,'4'),---
b.YBLXBC,---
p09.ptsjjdm,--PKG_NW_PTJK_COMM.uf_nw2ptzds('GW','','LB011',b.ywgm01,'3'),---
p09.ptsjjmc,--PKG_NW_PTJK_COMM.uf_nw2ptzds('GW','','LB011',b.ywgm01,'4'),---
b.ywgm03,---
(select wm_concat(YYDM01) from gw_jkda03@TO_NW where yylx01 = '10' and grxh01 = a.grxh01) as sjbdm,--- 疾病代码
--PKG_NW_PTJK_GGWS.uf_nw2ptdtzdzh('GW','','LB014','3',' gw_jkda03','10',a.grxh01),
(select wm_concat(YYDM02) from gw_jkda03@TO_NW where yylx01 = '10' and grxh01 = a.grxh01) as sjbmc,--- 疾病名称
--PKG_NW_PTJK_GGWS.uf_nw2ptdtzdzh('GW','','LB014','4',' gw_jkda03','10',a.grxh01),
null,---
(select QZSJ01 from gw_jkda03@TO_NW where yylx01 = '10' and grxh01 = a.grxh01 and rownum = 1),--- 确诊时间
(select wm_concat(YYDM01) from gw_jkda03@TO_NW where yylx01 = '11' and grxh01 = a.grxh01),--- 手术代码
(select wm_concat(YYDM02) from gw_jkda03@TO_NW where yylx01 = '11' and grxh01 = a.grxh01),--('GW','','LB014','4',' gw_jkda03','11',a.grxh01),'|',0),--- 手术名称
(select QZSJ01 from gw_jkda03@TO_NW where yylx01 = '11' and grxh01 = a.grxh01 and rownum = 1), --('GW','','LB014','4',' gw_jkda03','11',a.grxh01),'|',1),---
(select wm_concat(YYDM01) from gw_jkda03@TO_NW where yylx01 = '12' and grxh01 = a.grxh01),---
(select wm_concat(YYDM02) from gw_jkda03@TO_NW where yylx01 = '12' and grxh01 = a.grxh01), --('GW','','LB014','4',' gw_jkda03','12',a.grxh01),'|',0),---
(select QZSJ01 from gw_jkda03@TO_NW where yylx01 = '12' and grxh01 = a.grxh01 and rownum = 1), --('GW','','LB014','4',' gw_jkda03','12',a.grxh01),'|',1),---
(select wm_concat(YYDM01) from gw_jkda03@TO_NW where yylx01 = '13' and grxh01 = a.grxh01),---
(select wm_concat(YYDM02) from gw_jkda03@TO_NW where yylx01 = '13' and grxh01 = a.grxh01),--('GW','','LB014','4',' gw_jkda03','13',a.grxh01),'|',0),--- 输血原因
(select QZSJ01 from gw_jkda03@TO_NW where yylx01 = '13' and grxh01 = a.grxh01 and rownum = 1),--('GW','','LB014','4',' gw_jkda03','13',a.grxh01),'|',1),--- 输血时间
(select wm_concat(jbs001) from gw_jkda04@TO_NW where gxlx01 = '11' and grxh01 = a.grxh01),--('GW','','LB018','3',' gw_jkda04','11',a.grxh01),--- 父亲患病代码
(select wm_concat(jbs002) from gw_jkda04@TO_NW where gxlx01 = '11' and grxh01 = a.grxh01),--('GW','','LB018','4',' gw_jkda04','11',a.grxh01),---
(select wm_concat(jbs001) from gw_jkda04@TO_NW where gxlx01 = '12' and grxh01 = a.grxh01),--('GW','','LB018','3',' gw_jkda04','12',a.grxh01),---
(select wm_concat(jbs002) from gw_jkda04@TO_NW where gxlx01 = '12' and grxh01 = a.grxh01),--('GW','','LB018','4',' gw_jkda04','12',a.grxh01),---
(select wm_concat(jbs001) from gw_jkda04@TO_NW where gxlx01 = '21' and grxh01 = a.grxh01),--('GW','','LB018','3',' gw_jkda04','21',a.grxh01),---
(select wm_concat(jbs002) from gw_jkda04@TO_NW where gxlx01 = '21' and grxh01 = a.grxh01),--('GW','','LB018','4',' gw_jkda04','21',a.grxh01),---
(select wm_concat(jbs001) from gw_jkda04@TO_NW where gxlx01 = '31' and grxh01 = a.grxh01),--('GW','','LB018','3',' gw_jkda04','31',a.grxh01),
(select wm_concat(jbs002) from gw_jkda04@TO_NW where gxlx01 = '31' and grxh01 = a.grxh01),--('GW','','LB018','4',' gw_jkda04','31',a.grxh01),--- 子女患病名称
p15.ptsjjdm,--PKG_NW_PTJK_COMM.uf_nw2ptzd('GW','','LB022',b.ycbs01,'3'),
p15.ptsjjmc,--PKG_NW_PTJK_COMM.uf_nw2ptzd('GW','','LB022',b.ycbs01,'4'),
null,--- 遗传病（补充）
p16.ptsjjdm,--PKG_NW_PTJK_COMM.uf_nw2ptzd('GW','','LB023',b.cjqk01,'3'),
p16.ptsjjmc,--PKG_NW_PTJK_COMM.uf_nw2ptzd('GW','','LB023',b.cjqk01,'4'),
b.CJQK03,
p17.ptsjjdm,--PKG_NW_PTJK_COMM.uf_nw2ptzd('GW','','LB024',b.CFSS01,'3'),
p17.ptsjjmc,--PKG_NW_PTJK_COMM.uf_nw2ptzd('GW','','LB024',b.CFSS01,'4'),---
p18.ptsjjdm,--PKG_NW_PTJK_COMM.uf_nw2ptzd('GW','','LB025',b.rllx01,'3'),---
p18.ptsjjmc,--PKG_NW_PTJK_COMM.uf_nw2ptzd('GW','','LB025',b.rllx01,'4'),---
p19.ptsjjmc,--PKG_NW_PTJK_COMM.uf_nw2ptzd('GW','','LB026',b.ysqk01,'4'),---
p20.ptsjjdm,--PKG_NW_PTJK_COMM.uf_nw2ptzd('GW','','LB027',b.csqk01,'3'),---
p20.ptsjjmc,--PKG_NW_PTJK_COMM.uf_nw2ptzd('GW','','LB027',b.csqk01,'4'),---
p21.ptsjjdm,--PKG_NW_PTJK_COMM.uf_nw2ptzd('GW','','LB028',b.qcqk01,'3'),---
p21.ptsjjmc,--PKG_NW_PTJK_COMM.uf_nw2ptzd('GW','','LB028',b.qcqk01,'4'),---
a.czsj01,---
a.czr001,---
nvl(a.jddw01,&VS_HOSP_ID),---
nvl(p23.hosp_name,''), ---
&VS_HOSP_ID||a.grxh01,
nvl(t9.czsj01,a.CZSJ01),
1
from gw_jkda01@TO_NW a
inner join gw_jkda02@TO_NW b on a.grxh01 = b.grxh01
left join b0101 x1 on (x1.cywrid = &VS_HOSP_ID||a.grxh01)
left join qyptsjj02 p01 on (p01.sxtlb = 'GW' and p01.QYDM = '430682' and p01.SJJDM = 'LB001' and p01.YWSJJDM = b.grxb01)
left join qyptsjj02 p02 on (p02.sxtlb = 'GW' and p02.QYDM = '430682' and p02.SJJDM = 'LB002' and p02.YWSJJDM = b.czlx01)
left join qyptsjj02 p03 on (p03.sxtlb = 'GW' and p03.QYDM = '430682' and p03.SJJDM = 'LB004' and p03.YWSJJDM = b.grxx01)
left join qyptsjj02 p04 on (p04.sxtlb = 'GW' and p04.QYDM = '430682' and p04.SJJDM = 'LB005' and p04.YWSJJDM = b.RHYX01)
left join qyptsjj02 p05 on (p05.sxtlb = 'GW' and p05.QYDM = '430682' and p05.SJJDM = 'LB006' and p05.YWSJJDM = b.whcd01)
left join qyptsjj02 p06 on (p06.sxtlb = 'GW' and p06.QYDM = '430682' and p06.SJJDM = 'LB007' and p06.YWSJJDM = b.grzy01)
left join qyptsjj02 p07 on (p07.sxtlb = 'GW' and p07.QYDM = '430682' and p07.SJJDM = 'LB008' and p07.YWSJJDM = b.hyzk01)
left join qyptsjj02 p08 on (p08.sxtlb = 'GW' and p08.QYDM = '430682' and p08.SJJDM = 'LB009' and p08.YWSJJDM = b.yblx01)
left join qyptsjj02 p09 on (p09.sxtlb = 'GW' and p09.QYDM = '430682' and p09.SJJDM = 'LB011' and p09.YWSJJDM = b.ywgm01)
--疾病代码
left join qyptsjj02 p15 on (p15.sxtlb = 'GW' and p15.QYDM = '430682' and p15.SJJDM = 'LB022' and p15.YWSJJDM = b.ycbs01)
left join qyptsjj02 p16 on (p16.sxtlb = 'GW' and p16.QYDM = '430682' and p16.SJJDM = 'LB023' and p16.YWSJJDM = b.cjqk01)
left join qyptsjj02 p17 on (p17.sxtlb = 'GW' and p17.QYDM = '430682' and p17.SJJDM = 'LB024' and p17.YWSJJDM = b.CFSS01)
left join qyptsjj02 p18 on (p18.sxtlb = 'GW' and p18.QYDM = '430682' and p18.SJJDM = 'LB025' and p18.YWSJJDM = b.rllx01)
left join qyptsjj02 p19 on (p19.sxtlb = 'GW' and p19.QYDM = '430682' and p19.SJJDM = 'LB026' and p19.YWSJJDM = b.ysqk01)
left join qyptsjj02 p20 on (p20.sxtlb = 'GW' and p20.QYDM = '430682' and p20.SJJDM = 'LB027' and p20.YWSJJDM = b.csqk01)
left join qyptsjj02 p21 on (p21.sxtlb = 'GW' and p21.QYDM = '430682' and p21.SJJDM = 'LB028' and p21.YWSJJDM = b.qcqk01)
left join qyptsjj02 p22 on (p22.sxtlb = 'GW' and p22.QYDM = '430682' and p22.SJJDM = 'LB003' and p22.YWSJJDM = b.grmz01)
left join bas_org@to_nw p23 on(p23.hosp_id=a.jddw01)
left join (SELECT grxh01, czsj01 FROM (SELECT ROW_NUMBER() OVER(PARTITION BY grxh01 ORDER BY czsj01 desc) rn, grxh01, czsj01 FROM gw_jkda06@TO_NW )WHERE rn = 1 )t9 on t9.grxh01=a.grxh01
where nvl(x1.cywrid,'@') = '@'作者: congfu 时间: 2016-03-17 16:29
这段代码如何优化呢