注册

物化视图创建成功,但刷新报错 [-6605]:违反非空约束

Link 2023/12/25 834 7

为提高效率,提问时请提供以下信息,问题描述清晰可优先响应。
【DM版本】: DM Database Server 64 V8 8.1 企业版 DB Version: 0x7000c
【操作系统】: 麒麟
【CPU】: Hygon C86 7360 24-core Processor
【问题描述】*: 物化视图创建成功,刷新报错 [-6605]:违反非空约束

现象描述:

  1. 创建物化视图成功
  2. 查询出物化视图中有数据
  3. 执行物化视图刷新,报错违反非空约束
  4. 再次查询物化视图数据,数据全部丢失

具体操作命令与截图如下:

-- 删除物化视图
drop MATERIALIZED VIEW cfn.mv_quality_details;
-- 创建物化视图
CREATE MATERIALIZED VIEW cfn.mv_quality_details BUILD IMMEDIATE STORAGE(ON "MAIN", CLUSTERBTR) REFRESH COMPLETE ON DEMAND WITH PRIMARY KEY AS
   SELECT c.MEDINS_ORGCODE, 
          c.DSCG_CATY, 
          c.CHFDR_NAME, 
          c.ATDDR_NAME, 
          c.IPT_DR_NAME, 
          c.DSCG_WAY, 
          c.ACT_IPT_DAYS,
          c.MEDFEE_SUMAMT, 
          c.MEDCASNO, 
          c.MDTRT_SN, 
          c.VERSION, 
          c.PSN_NAME, 
          c.GEND, 
          c.AGE, 
          c.DSCG_TIME, 
          rd.DIAG_CODE, 
          rd.DIAG_NAME,
          qc.passed, 
          qc.RULE_TYPES, 
          qc.issue_count, 
          qc.id, 
          qc.score, 
          ro.OPRN_OPRT_CODE, 
          ro.OPRN_OPRT_NAME, 
          c.qc_status,
          DATEDIFF( DAY , c.DSCG_TIME, c.CREATE_DT) AS "upload_date_diff"
     FROM cfn.MEDICAL_RECORD_CORE AS c
LEFT JOIN cfn.MEDICAL_RECORD_VERSION AS v
       ON (v.MEDINS_ORGCODE = c.MEDINS_ORGCODE 
                AND v.mdtrt_sn = c.mdtrt_sn 
                AND v.VERSION = c.VERSION )
LEFT JOIN cfn.MEDICAL_QC_RESULT AS qc 
       ON (qc.MEDINS_ORGCODE = c.MEDINS_ORGCODE 
                AND qc.mdtrt_sn = c.mdtrt_sn
                AND qc.version = c.VERSION ) 
LEFT JOIN cfn.MEDICAL_RECORD_DISEINFO AS rd
       ON (rd.MEDINS_ORGCODE = c.MEDINS_ORGCODE 
                AND rd.MDTRT_SN = c.MDTRT_SN 
                AND rd.VERSION = c.VERSION 
                AND rd.MAIN_DIAG_FLAG = '1' )
LEFT JOIN cfn.MEDICAL_RECORD_OPRNINFO AS ro
       ON (ro.MEDINS_ORGCODE = c.MEDINS_ORGCODE 
                AND ro.MDTRT_SN = c.MDTRT_SN 
                AND ro.VERSION = c.VERSION 
                AND ro.SEQUENCE = '1' )
    WHERE NOT (v.VERSION IS NULL );
-- 刷新物化视图
REFRESH MATERIALIZED VIEW CFN.mv_quality_details COMPLETE;

操作截图如下:
image.png
image.png

回答 0
暂无回答
扫一扫
联系客服