-- Start of DDL Script for View SD_HOSPITAL.VW_ZS_QJCGLCX
-- Generated 2020-03-13 16:09:47 from SD_HOSPITAL@FW2

CREATE OR REPLACE VIEW vw_zs_qjcglcx (
   yyid00,
   bmmc00,
   dqks00,
   zyid00,
   qjrs00,
   swrs00,
   sjcyrq,
   sjryrq )
AS
select Z.YYID00,BMMC00,DQKS00,ZYID00,QJRS00,SWRS00,SJCYRQ,SJRYRQ
 from (select A.DQKS00,A.ZYID00,A.SJCYRQ,A.SJRYRQ,
             (select nvl(count(distinct(H.YZMXID)),0)
                from BQ_YJYZ00 H
               where (H.ZLXMJC like '%抢救%' and H.ZLXMJC not like '%取消%')
                 and H.ZYID00 = A.ZYID00  and YZZT00<>'4') QJRS00,
             /*(select nvl(count(1),0)
                from BQ_YJYZ00 H
               where (H.ZLXMJC like '%死亡%' and H.ZLXMJC not like '%取消%') --and (H.ZLXMJC like '%抢救%' and H.ZLXMJC not like '%取消%')
                 and H.ZYID00 = A.ZYID00  and YZZT00<>'4') SWRS00*/
               (select nvl(count(1),0)
               from BQ_YJYZ00 J WHERE ZYID00 IN
                 (select zyid00
                from BQ_YJYZ00 H
               where (H.ZLXMJC like '%抢救%' and H.ZLXMJC not like '%取消%')
                 and H.ZYID00 = A.ZYID00  and YZZT00<>'4') AND (J.ZLXMJC like '%死亡%' and J.ZLXMJC not like '%取消%')
                 AND  J.ZYID00 = A.ZYID00  and YZZT00<>'4') SWRS00
        from ZY_BRXXB0 A) K,BM_BMBM00 Z
where K.DQKS00 = Z.BMBH00
 and exists (select SSKSBH from ZS_YSZ000 where SSKSBH = Z.BMBH00)  
 and (QJRS00 <> '0' or SWRS00 <> '0')
/


-- End of DDL Script for View SD_HOSPITAL.VW_ZS_QJCGLCX