CREATE  or replace VIEW SD_HOSPITAL.VW_ZYPY_BQZJLYD_MSQL
 (
DLXH00,YPNM00,QLDW00,DXSYBZ,YPDJ00,YPMC00,YPGG00,YPZSL0,YPZSL1,CFJE00,PYSM00,LSDJ00,YPBM00,CFID00,GZDJ00,GWYPFL,LBBH00,CFMXID,CFXH00,TOTPAG,DKSL00,SFSL00,CFJE01

 )
 AS 
select x."DLXH00",x."YPNM00",x."QLDW00",x."DXSYBZ",x."YPDJ00",x."YPMC00",x."YPGG00",x."YPZSL0",x."YPZSL1",x."CFJE00",x."PYSM00",x."LSDJ00",x."YPBM00",
x."CFID00",x."GZDJ00",x."GWYPFL",x."LBBH00",x."CFMXID",x."CFXH00",x."TOTPAG",x."DKSL00",
case when x.YPZSL1+x.DKSL00>0 then x.YPZSL1+x.DKSL00
     when x.YPZSL1+x.DKSL00<=0 then 0
else 0 end SFSL00,
round((x.YPZSL1-x.DKSL00)*x.LSDJ00,2) CFJE01
 from 
(
select
        C.DLXH00,A.YPNM00,A.QLDW00,B.DXSYBZ,decode(DXSYBZ,'1','大输液药品',decode(sign(GZDJ00),1,'贵重药品',decode(sign(B.GWYPFL),1,'高危药品','普通药品'))) YPDJ00,
        B.YPMC00 YPMC00,
        A.YPGG00,to_char(sum(A.YPZSL0))||'('||trim(A.QLDW00)||')' YPZSL0,
        sum(A.YPZSL0)YPZSL1,
        round(sum(A.YPZSL0*A.LSDJ00),2) CFJE00,B.PYSM00,A.LSDJ00,B.YPBM00,
        (select D.CFID00 from YF_DLCFB0 D,YF_DLCFMX E where C.DLXH00=d.DLXH00 and E.CFID00=D.CFID00 and E.YPNM00=A.YPNM00 and rownum=1) CFID00,
        B.GZDJ00,B.GWYPFL,B.LBBH00,
       (select E.CFMXID from YF_DLCFB0 D,YF_DLCFMX E where C.DLXH00=d.DLXH00 and E.CFID00=D.CFID00 and E.YPNM00=A.YPNM00 and rownum=1) CFMXID,
       (select D.CFXH00 from YF_DLCFB0 D,YF_DLCFMX E where C.DLXH00=d.DLXH00 and E.CFID00=D.CFID00 and E.YPNM00=A.YPNM00 and rownum=1) CFXH00,
       (select MAX(D.CFXH00) from YF_DLCFB0 D,YF_DLCFMX E where C.DLXH00=D.DLXH00 and E.CFID00=D.CFID00 ) TOTPAG,
               (select nvl(sum(d.CXSL00),0) from YF_BQTYJL d where d.BQBMBH=A.BQBMBH
              and d.YFBMBH=A.YFBMBH and d.YPNM00=A.YPNM00 and d.ZT0000='1'
              and d.TYR000='-9'
              and d.CZRQ00=A.FYRQ00) DKSL00
        from YF_YZYPSQ A ,BM_YD0000 B,YF_YZDLDY C where a.YPNM00=b.YPNM00 
        and A.YPQLPC=C.YPQLPC and (a.BYJXX0='' or a.BYJXX0 is null)
        group by C.DLXH00,A.YPNM00,B.YPMC00,A.YPGG00,A.QLDW00,A.LSDJ00,B.PYSM00,B.DXSYBZ,B.GZDJ00,A.BQBMBH,A.YFBMBH,A.FYRQ00,
        decode(DXSYBZ,'1',1,decode(sign(GZDJ00),1,2,3)),B.YPBM00,B.GWYPFL,B.LBBH00  --,A.YPQLPC
)x
/