materialized view

物化视图

create materialized view MV_ZM_CASE_CIRCULAR refresh force on demand

start with sysdate next to_date( concat( to_char( sysdate+1,'dd-mm-yyyy'),' 22:00:00'),'dd-mm-yyyy hh24:mi:ss')

as (
SELECT
       SUBSTR(RPRS.CITY_REGIE_NAME,1,3) CITY_NAME,       --市
       SUBSTR(RPRS.COUNTY_REGIE_NAME,4,2) COUNTY_NAME,   --县
       PARTY.PARTY_NAME,                                 --涉案人员名称
       PARTY.BEFORE_NAME,                              --涉案人员曾用名
       PARTY.NICKNAME,                                 --涉案人员外号
       PARTY.PARTY_CARD,                                 --身份证号
       PARTY.NATIVE_PLACE,                               --籍贯
       PARTY.CURR_ADD,                                   --住址
       PARTY.ROSE_TYPE,                                 --角色
       PARTY.TELEPHONE,                                  --手机
       PARTY.IF_HAS_LICENSE,                             --是否有证
       PARTY.LICENSE_CODE,                               --许可证号
       ZC.CASE_TITLE,                                    --案件标题
       ZC.ESTABLISH_CODE,                                --立案编号
       ZC.ESTABLISH_TIME,                                --立案时间
       ZC.REGIE_ID,                                      --立案机关   处罚机关
       ZC.ITEM_QUANTITY,                                 --涉案卷烟数量
       ZC.ITEM_VALUE,                                    --涉案卷烟案值
       ZC.IF_MOVE,                                       --是否移送
       ZC.MOVE_TYPE,                                     --移送机关
       ZC.FINALITY_DATE,                                 --处罚时间
       ZC.PAY_AMERCE,                                    --处罚金额
       ZC.FINISH_DATE,                                   --结案时间
       PENAL.PENAL_NAME,                                 --刑事案件名称
       PENAL.PENAL_CODE,                                 --刑事案件编号
       PENAL.PENAL_EST_TIME,                             --刑事立案时间
       PENAL.CRIMINAL_TIME,                              --刑拘时间
       PENAL.ARREST_TIME,                                --逮捕时间
       PENAL.ARREST_GEAR,                                --逮捕机关
       PENAL.CONDEMN_TIME,                               --判刑时间
       PENAL.CONDEMN_CONDITION,                          --判刑情况
       PENAL.PENAL_FIN_TIME  ,                            --刑事结案时间
    PARTY.INSPECT_TIME
FROM
(
(SELECT
    ZC.INSPECT_TIME INSPECT_TIME,
       ZCP.CASE_PARTY_ID PARTY_ID,
       ZCP.CASE_ID CASE_ID,
       ZCP.MANAGER PARTY_NAME,             --涉案人员姓名
       ZCP.BEFORE_NAME BEFORE_NAME,         --曾用名
       ZCP.NICKNAME NICKNAME,            --外号
       ZCP.MANAGER_CARD PARTY_CARD,        --身份证号
       ZCP.MANAGER_NATIVE_PLACE NATIVE_PLACE,--籍贯
       ZCP.MANAGER_CURR_ADD CURR_ADD,          --现住址
       ZCP.ROSE_TYPE ROSE_TYPE,               --角色
       ZCP.MANAGER_TEL TELEPHONE,               --手机号
       ZCP.IF_HAS_LICENSE IF_HAS_LICENSE,            --是否持证
       ZCP.LICENSE_CODE LICENSE_CODE               --许可证号
       
FROM ZM_CASE_PARTY ZCP
INNER JOIN ZM_CASE ZC ON ZC.CASE_ID=ZCP.CASE_ID
)
UNION ALL
(
SELECT
    ZMPC.PENAL_EST_TIME INSPECT_TIME,
       ZMPP.PENAL_PARTY_ID PARTY_ID,
       ZMPP.CASE_ID CASE_ID,
       ZMPP.PARTY_NAME PARTY_NAME,
       ZMPP.BEFORE_NAME BEFORE_NAME,
       ZMPP.NICKNAME NICKNAME,
       ZMPP.PARTY_CARD PARTY_CARD,
       ZMPP.NATIVE_PLACE NATIVE_PLACE,
       ZMPP.CURR_ADD CURR_ADD,
       ZMPP.ROSE_TYPE ROSE_TYPE,
       ZMPP.TELEPHONE TELEPHONE,
       ZMPP.IF_HAS_LICENSE IF_HAS_LICENSE,
       ZMPP.LICENSE_CODE LICENSE_CODE

FROM ZM_MOVE_PENAL_PARTY ZMPP
INNER JOIN ZM_MOVE_PENAL_CASE ZMPC ON ZMPP.CASE_ID=ZMPC.CASE_ID
)
) PARTY

LEFT JOIN
(
      SELECT
             ZC.CASE_ID CASE_ID,
             MAX(ZC.CASE_TITLE) CASE_TITLE,
             MAX(ZWE.ESTABLISH_CODE) ESTABLISH_CODE,
             MAX(ZWE.ESTABLISH_TIME) ESTABLISH_TIME,
             MAX(ZC.REGIE_ID) REGIE_ID,
             SUM(CASE WHEN ZCI.CHECK_TYPE BETWEEN '0101' AND '0102'
                      THEN ZCI.ITEM_QUANTITY*200/10000
                      ELSE 0
                      END) ITEM_QUANTITY,
             SUM(CASE WHEN ZCI.CHECK_TYPE BETWEEN '0101' AND '0102'
                      THEN ZCI.ITEM_QUANTITY*ZCI.ITEM_PRICE/10000
                      ELSE 0
                      END) ITEM_VALUE,
             MAX(CASE WHEN ZMR.RECORD_ID IS NOT NULL THEN '是'
                  ELSE '否'
                  END) IF_MOVE,
             MAX(ZMR.MOVE_TYPE) MOVE_TYPE,
             MAX(ZCF.FINALITY_DATE) FINALITY_DATE,
             MAX(ZCE.PAY_AMERCE) PAY_AMERCE,
             MAX(ZWF.FINISH_DATE) FINISH_DATE
            
      FROM ZM_CASE ZC
      LEFT JOIN ZM_WT_ESTABLISH ZWE ON ZC.CASE_ID=ZWE.CASE_ID
      LEFT JOIN ZM_MOVE_RECORD ZMR ON ZC.CASE_ID=ZMR.CASE_ID
      LEFT JOIN ZM_CASE_FINALITY ZCF ON ZC.CASE_ID=ZCF.CASE_ID
      LEFT JOIN ZM_CASE_EXECUTE ZCE ON ZC.CASE_ID=ZCE.CASE_ID
      LEFT JOIN ZM_WT_FINISH ZWF ON ZC.CASE_ID=ZWF.CASE_ID
      LEFT JOIN ZM_CASE_ITEM ZCI ON ZC.CASE_ID=ZCI.CASE_ID
      GROUP BY ZC.CASE_ID
) ZC ON ZC.CASE_ID=PARTY.CASE_ID

LEFT JOIN
(
    SELECT
           ZMPP.PENAL_PARTY_ID PARTY_ID,
           ZMPC.PENAL_NAME PENAL_NAME,
           ZMPC.PENAL_CODE PENAL_CODE,
           ZMPC.PENAL_EST_TIME PENAL_EST_TIME,
           ZMPP.CRIMINAL_TIME CRIMINAL_TIME,
           ZMPP.ARREST_TIME ARREST_TIME,
           ZMPP.ARREST_GEAR ARREST_GEAR,
           ZMPP.CONDEMN_TIME CONDEMN_TIME,
           ZMPP.CONDEMN_CONDITION CONDEMN_CONDITION,
           ZMPC.PENAL_FIN_TIME PENAL_FIN_TIME
   
    FROM ZM_MOVE_PENAL_PARTY ZMPP
    INNER JOIN ZM_MOVE_PENAL_CASE ZMPC ON ZMPP.CASE_ID=ZMPC.CASE_ID
) PENAL ON PENAL.PARTY_ID=PARTY.PARTY_ID

LEFT JOIN RM_PUB_REGIE_STRU RPRS ON ZC.REGIE_ID=RPRS.COUNTY_REGIE_ID );

 

 

 

 

create materialized view MV_ZM_CASE refresh force on demand

start with sysdate next to_date( concat( to_char( sysdate+1,'dd-mm-yyyy'),' 22:00:00'),'dd-mm-yyyy hh24:mi:ss')

as SELECT
        ZC.CASE_ID CASE_ID,
    MAX(ZC.REGIE_ID) REGIE_ID,
        MAX(ZC.INSPECT_TIME) INSPECT_TIME,
        MAX(ZC.RELATE_VALUE) RELATE_VALUE,
        MAX(ZC.INSPECT_PLACE) INSPECT_PLACE,
        MAX(ZC.CASE_CHARA) CASE_CHARA,
        MAX(ZC.CASE_TITLE) CASE_TITLE,
    SUM(CASE WHEN ZCI.CHECK_TYPE IN ('0101','0102') AND ZCI.SOURCE IN ('0101','0102')
                        THEN ZCI.ITEM_QUANTITY
                        ELSE 0
                        END) REAL_TOB_QUANTITY,  --真烟数量
    SUM(CASE WHEN ZCI.CHECK_TYPE BETWEEN '0101' AND '0102' AND ZCI.SOURCE IN ('0101','0102')
                      THEN ZCI.ITEM_QUANTITY*ZCI.PRICE_DRTL       
                      ELSE 0
        END) REAL_TOB_VALUE,--真烟案值
        SUM(CASE WHEN ZCI.CHECK_TYPE IN ('0101','0102') AND ZCI.SOURCE IN ('0103','0104')
                        THEN ZCI.ITEM_QUANTITY
                        ELSE 0
                        END) SUMGGLE_TOB_QUANTITY,  --走私烟数量
    SUM(CASE WHEN ZCI.CHECK_TYPE IN ('0101','0102') AND ZCI.SOURCE IN ('0103','0104')
                        THEN ZCI.ITEM_QUANTITY*ZCI.PRICE_DRTL
                        ELSE 0
                        END) SUMGGLE_TOB_VALUE,  --走私烟案值
        SUM(CASE WHEN ZCI.CHECK_TYPE IN ('0201','0202')
                        THEN ZCI.ITEM_QUANTITY
                        ELSE 0
                        END) FAKE_TOB_QUANTITY,        --假烟数量
    SUM(CASE WHEN ZCI.CHECK_TYPE IN ('0201','0202')
                        THEN ZCI.ITEM_QUANTITY*ZCI.PRICE_DRTL
                        ELSE 0
                        END) FAKE_TOB_VALUE,        --假烟案值
        SUM(CASE WHEN ZCI.STRUCTURE_CODE IN ('010302','010303','010307')
                        THEN ZCI.ITEM_QUANTITY
                        ELSE 0
                        END) TOB_LEAF_QUANTITY,        --烟叶数量
    SUM(CASE WHEN ZCI.STRUCTURE_CODE IN ('010302','010303','010307')
                        THEN ZCI.ITEM_QUANTITY*ZCI.PRICE_DRTL
                        ELSE 0
                        END) TOB_LEAF_VALUE,        --烟叶案值
        SUM(CASE WHEN ZCI.STRUCTURE_CODE IN ('010301','010306')
                        THEN ZCI.ITEM_QUANTITY
                        ELSE 0
                        END) TOB_CUT_QUANTITY,        --烟丝数量
    SUM(CASE WHEN ZCI.STRUCTURE_CODE IN ('010301','010306')
                        THEN ZCI.ITEM_QUANTITY*ZCI.PRICE_DRTL
                        ELSE 0
                        END) TOB_CUT_VALUE,        --烟丝案值
        SUM(CASE WHEN ZCI.STRUCTURE_CODE IN ('010201','010202','010203','010204','010205','010206','010207')
                        THEN ZCI.ITEM_QUANTITY
                        ELSE 0
                        END) TOB_MACHINE_QUANTITY,        --烟机数量
   SUM(CASE WHEN ZCI.STRUCTURE_CODE IN ('010201','010202','010203','010204','010205','010206','010207')
                        THEN ZCI.ITEM_QUANTITY*ZCI.PRICE_DRTL
                        ELSE 0
                        END) TOB_MACHINE_VALUE,        --烟机案值
       SUM(CASE WHEN ZCI.STRUCTURE_CODE IN ('010401','010402','010403')
                        THEN ZCI.ITEM_QUANTITY
                        ELSE 0
                        END) TOB_ASSIST_QUANTITY,        --辅料数量
  SUM(CASE WHEN ZCI.STRUCTURE_CODE IN ('010401','010402','010403')
                        THEN ZCI.ITEM_QUANTITY*ZCI.PRICE_DRTL
                        ELSE 0
                        END) TOB_ASSIST_VALUE         --辅料案值
    FROM ZM_CASE ZC,ZM_CASE_ITEM ZCI
    WHERE ZC.CASE_ID=ZCI.CASE_ID
    AND ZC.REGIE_ID=ZCI.REGIE_ID
    GROUP BY ZC.CASE_ID
;