CREATE VIEW SD_HOSPITAL.VW_ZYPY_BQYZCFMXDY
 (
 TXMNUM, BRID00, CFID00,DLXH00,YZID00, YZMXID, ZYID00, ZYH000, BRXM00, RYCWH0, BQMC00, 
KSMC00, CLBZ00, YPMC00, YPGG00, YPYL00, DYRQ00, ZXSJ00, YPYFMC, YFSMMC, 
CKDH00

 )
 AS 
select TXMNUM,BRID00,CFID00,DLXH00,YZID00,YZMXID,ZYID00,ZYH000,BRXM00,RYCWH0,BQMC00,KSMC00,CLBZ00,YPMC00,YPGG00,YPYL00,DYRQ00,ZXSJ00,YPYFMC,YFSMMC,CKDH00 from (
select trim(CKDH00)||trim(ZYID00)||decode(ZXSJ00,'早餐','00','午餐','01','晚餐','02','睡前','03','')TXMNUM,
x."BRID00",x."CFID00",x."DLXH00",x."YZID00",x."YZMXID",
x."ZYID00",x."ZYH000",x."BRXM00",x."RYCWH0",x."BQMC00",x."KSMC00",x."CLBZ00",x."YPMC00",x."YPGG00",x."YPYL00",x."DYRQ00",x."ZXSJ00",x."YPYFMC",x."YFSMMC",x."CKDH00" from (
select
'' YZID003,
(select brid00 from zy_brxxb0 where zyid00=b.zyid00 ) brid00,--病人id
e.CFID00 CFID00,  --队列拆分ID
a.DLXH00 DLXH00,  --队列序号
b.yzid00 YZID00, --医嘱ID
B.YZMXID YZMXID ,--医嘱明细
b.ZYID00 ZYID00,  --住院ID
trim(b.ZYH000) ZYH000,  --住院号
b.BRXM00 BRXM00,  --病人姓名
trim((select RYCWH0 from ZY_BRXXB0 where ZYID00=b.ZYID00)) RYCWH0,  --床号
(select BMMC00 from BM_BMBM00 where BMBH00=b.BQBMBH) BQMC00,  --病区
(select BMMC00 from BM_BMBM00 where BMBH00=b.DQKS00) KSMC00,  --科室
decode(b.CLBZ00,'0','长嘱','1','临嘱','') CLBZ00,             --长临嘱
b.YPMC00,                                                    --药品名称
b.YPGG00,                                                    --药品规格
trim(trim(to_char(round((b.YPZSL0/(select count(1) from BQ_YZCFMX x where x.YZID00=c.YZID00 and x.ZXRQ00=c.ZXRQ00 )),2),'9990.99'))
||' '||b.QLDW00) YPYL00, --药品用量
substr(c.ZXRQ00,1,4)||'/'||substr(c.ZXRQ00,5,2)||'/'||substr(c.ZXRQ00,7,2) DYRQ00,  --执行日期
decode(c.ZXSJ00,'08:00:00','早餐','12:00:00','午餐','16:00:00','晚餐','20:00:00','睡前',substr(c.ZXSJ00,1,2)||'点') ZXSJ00,  --执行时间
decode(b.CLBZ00,'0',b.XMYFMC,'1','即刻服用') YPYFMC,  --医生嘱附
substrb(b.YFSMMC,1,60) YFSMMC         --嘱附
,trim(b.CKDH00) CKDH00
from YF_YZYPSQ b,BQ_YZCFMX c,YF_YZYPDL a,YF_YZDLDY d,YF_DLCFMX  e where  b.YZID00=c.YZID00
and a.DLXH00=d.DLXH00 and d.YPQLPC=b.YPQLPC and a.CKDH00=b.CKDH00 and b.HZBZ00='1'
and (b.BYJXX0='' or b.BYJXX0 is null) and b.THBZ00='0' and b.CXBZ00='Z'
and e.DLXH00=a.DLXH00 and e.YPNM00=b.YPNM00
and c.ZXRQ00=to_char(sysdate,'YYYYMMDD')) x ) group by 
TXMNUM,BRID00, CFID00,DLXH00,YZID00,YZMXID,ZYID00,ZYH000,BRXM00,RYCWH0,BQMC00,KSMC00,CLBZ00,YPMC00,YPGG00,YPYL00,DYRQ00,ZXSJ00,YPYFMC,YFSMMC,CKDH00
/