CREATE OR REPLACE VIEW VW_XK_JCPXJL AS
SELECT a.sqdh00, c.xm0000, c.xb0000, a.sxcf00, SF_XT_CSRQTONL (c.csrq00) nl0000,
       c.zdxx00, mrn000, a.ksmc00, a.ch0000, a.sxsz00, shysxm, pxfhxm, qxrxm0,qxrq00, qxsj00,
       --FJRXM0,
       (SELECT zwxm00 FROM bm_ygbm00 WHERE ygbh00 = a.fxrbh0) fxrxm0,
       (select dzqm00 from BM_ZJ0000 d,bm_ygbm00 e WHERE d.ygbh00=e.YGBH00 and e.zwxm00=FJRXM0 and rownum=1) FJRXM0,
       --(SELECT zwxm00 FROM bm_ygbm00 WHERE ygbh00 = a.pxygbh) pxrxm0,
       (SELECT dzqm00 FROM BM_ZJ0000 WHERE ygbh00 = a.pxygbh) pxrxm0,
       (select TYKTSC from xk_brxx00 where brid00 = a.brid00 and TYKTSC is not null and rownum=1 ) KTSXJG,
       DECODE(FFID00,NULL,'',(SELECT MC0000 FROM BM_TYZD00 WHERE ZDMC00='交叉配血方法' AND YXBZ00='1' AND NBBH00=B.FFID00)||';')||
       DECODE(FFID01,NULL,'',(SELECT MC0000 FROM BM_TYZD00 WHERE ZDMC00='交叉配血方法' AND YXBZ00='1' AND NBBH00=B.FFID01)||';')||
       DECODE(FFID02,NULL,'',(SELECT MC0000 FROM BM_TYZD00 WHERE ZDMC00='交叉配血方法' AND YXBZ00='1' AND NBBH00=B.FFID02)) JCPXFF,
       DECODE(A.MZZYBZ,'住院','住院号:'||C.zyh000,'门诊','病历号:'||(select brblh0 from bm_brxxb0 where brid00=c.brid00 and rownum=1),' ') ZYHBLH,b.XDBH00 XDBH00,
       RHXX00,'血型:'||c.zdxx00||chr(10)||'RH(D):'||c.RHXX00 ZDXX01,
       c.ZYH000,(SELECT ZWXM00 FROM BM_YGBM00 C WHERE C.YGBH00(+)=A.SQYSBH) SQYSXM,   --申请医生姓名
       A.PXBGRQ,A.PXBGSJ,A.SXSL00||A.SXDW00 SXL000,a.SQDFZH
  FROM xk_sxsq00 a, xk_jcpxbg b, xk_brxx00 c
 WHERE a.brid00 = c.brid00 AND a.sqdh00 = b.sqdh00
;