CREATE OR REPLACE VIEW SD_HOSPITAL.VW_YF_BQZJLYDNEW_PSQL
 (
 CKDH00, BQBMMC, YFBMMC, ZYID00, LYR000, FYR000, CZRQ00, TITLE0, CZR000

 )
 AS 
select
  A.CKDH00,
  (select BMMC00 from BM_BMBM00 where BMBH00=a.BQBMBH and rownum=1) BQBMMC,
  (select BMMC00 from BM_BMBM00 where BMBH00=a.BQBMBH and rownum=1) YFBMMC,
  A.ZYID00,
  (select ZWXM00 from BM_YGBM00 where YGBH00=a.LYR000 and rownum=1 ) LYR000,    
  (select ZWXM00 from BM_YGBM00 where YGBH00=a.FYR000 and rownum=1 ) FYR000,
  to_char(SYSDATE,'YYYY-MM-DD HH24:MI:SS') CZRQ00,  
  (select trim(b.YYQC00)||trim(c.BMMC00) from XT_YYXX00 b,BM_BMBM00 c where b.YYID00=c.YYID00 and c.BMBH00=a.YFBMBH and rownum=1)||decode(a.HZBZ00,0,'针剂领药单',1,'口服领药单') TITLE0,
  (select ZWXM00 from BM_YGBM00 where YGBH00=a.FYR000 and rownum=1 ) CZR000
from YF_YZYPSQ A
/