CREATE OR REPLACE VIEW SD_HOSPITAL.VW_YF_BQLYD_MSQL
 (
 CKDH00, TQDJH0, ZYID00, PLXH00, PLXH01, XM0000, CH0000, NL0000, XB0000, 
ZYH000, FYRQ00, FYSJ00, YSXM00, ZYDJS0, ZYDJF0, YZID00, DQKS00, YPMC00, 
CSL000, JLDW00, PCMC00, YPYFMC, YPZSL0, QLDW00, JE0000, LSDJ00, CLBZ00, 
CLBZMC, ZYJYF0, FJFDJH, JYF000, KDRQ00, BQBMMC

 )
 AS 
SELECT 
A.CKDH00,
DECODE(A.YPDLBH,'2',A.TQDJH0,NULL),
A.ZYID00,
DECODE(A.YPDLBH,'2',A.ZYID00||A.TQDJH0||A.YZID00,A.ZYID00),
A.ZYID00,
B.XM0000,
B.RYCWH0,
SF_YF_CSRQTONL(B.CSRQ00,TO_CHAR(SYSDATE,'YYYYMMDD')) NL0000,
B.XB0000,
B.ZYH000,
(SELECT SUBSTR(FYRQ00,1,4)||'.'||SUBSTR(FYRQ00,5,2)||'.'||SUBSTR(FYRQ00,7,2) FROM YF_YZYPSQ WHERE ZYID00=A.ZYID00 AND CKDH00=A.CKDH00 AND ROWNUM=1),
(SELECT FYSJ00 FROM YF_YZYPSQ WHERE ZYID00=A.ZYID00 AND CKDH00=A.CKDH00 AND ROWNUM=1),
(SELECT ZWXM00 FROM YF_YZYPSQ C,BM_YGBM00 D WHERE C.YSGZH0=D.YGBH00 AND ZYID00=A.ZYID00 AND CKDH00=A.CKDH00 AND ROWNUM=1),
ZYDJS0,
(Select TRIM(TO_CHAR(HJJE00,'99990.99')) from ZY_FYMX00 WHERE DJH000=A.FJFDJH AND XMBH00=(SELECT TRIM(VALUE0) FROM XT_XTCS00 WHERE NAME00='YF_ZYJYF_SFDM') AND ROWNUM=1) ZYDJF0,
A.YZID00,
(SELECT BMMC00 FROM BM_BMBM00 WHERE BMBH00=A.DQKS00),
DECODE(C.YPCZFL,'3','¡ó'||A.YPMC00,A.YPMC00)||'['||A.ypgg00||']',
trim(to_char(CSL000/Z2J000,'99990.99')),  
'['||TRIM(ZYFYDW)||']' JLDW00,
PCMC00, 
YPYFMC||'('||YFSMMC||')', 
YPZSL0*ZHL000/Z2J000||'['||TRIM(ZYFYDW)||']', 
--YPZSL0||'['||TRIM(QLDW00)||']', 
QLDW00, 
ROUND(YPZSL0*LSDJ00,2), 
LSDJ00,
CLBZ00,
DECODE(CLBZ00,'0','³¤Öö','1','¶ÌÖö'),
(Select HJJE00 from ZY_FYMX00 WHERE DJH000=A.FJFDJH AND XMBH00=(SELECT TRIM(VALUE0) FROM XT_XTCS00 WHERE NAME00='YF_ZYJYF_SFDM') AND ROWNUM=1) ZYJYF0,
A.FJFDJH,
--nvl((SELECT SUM(HJJE00) FROM ZY_FYMX00 WHERE DJH000=D.FJFDJH),0) JYF000
(SELECT NVL(SUM(HJJE00),0) FROM ZY_FYMX00 WHERE DJH000 IN
  (SELECT FJFDJH FROM YF_YZYPSQ WHERE YPQLPC=A.YPQLPC AND YPQLPC=
  (SELECT MIN(YPQLPC) FROM YF_YZYPSQ WHERE TQDJH0=A.TQDJH0 AND YZID00=A.YZID00) )),
(SELECT LRRQ00 from bq_ypyz00 where yzid00=A.yzid00 and yzmxid=A.yzmxid and rownum=1),
(SELECT BMMC00 FROM BM_BMBM00 WHERE BMBH00=A.BQBMBH) BQBMMC
FROM YF_YZYPSQ A,ZY_BRXXB0 B,BM_YD0000 C
WHERE A.ZYID00=B.ZYID00 AND A.YPNM00=C.YPNM00
/