create or replace view vw_bq_ypyzxg_zydzbl
(yzid00, yplbbh, lbmc00, dmbz00, clbz00, yexm00, pxsj00, pxsj11, kzys00, kzysxm, lrrq00, lrsj00, lrrxm0, qyrq00, qysj00, zqs000, zqcd00, zqdw00, zqcs00, zqcyts, jycs00, pcid00, pcmc00, yfid00, ypyfmc, yyzf00, yzzt00, bzid00, bzmc00, bz0000, tzrq00, tzsj00, yfdyjl, srcs00, byjbz0, dycjsl, dycjql, decjsl, decjql, yyrq00, cfmc00, zymrcs, yzcllb, jyfs00)
as
select  A.YZID00,
             A.YPLBBH,
             C.LBMC00,
             A.DMBZ00,
             A.CLBZ00,
             A.YEXM00,
             A.PXSJ00,
             to_date(A.PXSJ00,'HH24:MI:SS'),
             A.KZYS00,
             B.ZWXM00,
             A.LRRQ00,
             A.LRSJ00,
             A.LRRXM0,
             A.QYRQ00,
             A.QYSJ00,
             --to_date(A.QYRQ00,'YYYYMMDD'),to_date(A.QYSJ00,'HH24:MI:SS'),
             --A.QYRQ00||' '||substr(A.QYSJ00,1,5),
             A.ZQS000,
             A.ZQCD00,
             A.ZQDW00,
             D.ZQCS00,
             A.ZQCYTS,
             A.JYCS00,
             A.PCID00,
             decode(A.YPLBBH,'2','',A.PCMC00),
             A.YFID00,
             A.YPYFMC,
             A.YYZF00,
             A.YZZT00,
             A.BZID00,
             E.BZMC00,
             A.BZ0000,
             a.TZRQ00,
             a.TZSJ00,
             nvl((select YFDYJL from BM_YPYF00 where YFID00=a.YFID00 and rownum=1),1) as YFDYJL,
             a.SRCS00,
             decode(KFBZ00,'Y',(select count(*) from BQ_YPYZMX f,BM_YD0000 g where YZID00=a.YZID00 and g.YPNM00=f.YPNM00 and g.YPCZFL='3'),'0') BYJBZ0,
             a.DYCJSL,
             a.DYCJQL,
             a.DECJSL,
             a.DECJQL,
             YYRQ00, --用药日期
             CFMC00,  --处方名称
             ZYMRCS,
             (select YZCLLB from BQ_YPYZMX where YZID00=A.YZID00 and rownum=1) YZCLLB,
             A.JYFS00
    from BQ_YPYZ00 a,BM_YGBM00 b,BM_YPLB00 c,BM_PC0000 d,BM_DMZD00 e
      where b.YGBH00 = a.KZYS00 and c.LBBH00 = a.YPLBBH and d.PCID00(+) = a.PCID00
      and e.BZID00(+) = a.BZID00
;