oracle视图举例之一

最新推荐文章于 2020-05-16 13:45:05 发布

海蓝树

最新推荐文章于 2020-05-16 13:45:05 发布

阅读量321

点赞数

分类专栏： Oracle 相关

本文链接：https://blog.csdn.net/xwnxwn/article/details/79768859

版权

Oracle 相关专栏收录该内容

99 篇文章 0 订阅

订阅专栏

CREATE OR REPLACE VIEW V_ALARM_REPORT_USER_R AS
SELECT AAR.REPORT_ID,
       AAR.ALARM_TYPE_ID,
       AAR.ALARM_TYPE_NAME,
       AAR.ALARM_CONTENT,
       AAR.LINE_CODE,
       AAR.STATION,
       AAR.REPORTER_CODE,
       AAR.REPORTER_NAME, --提报人姓名
       AAW.AD_FLOW_ID,
       AAW.AD_RECEIVER_ROLE_ID, --哪个角色的人接收
       SUW.USER_AD_ROLES USER_AD_ROLES, --接收人具有AD角色
       SUW.USER_CODE AS RECEIVER_CODE,
       SUW.USER_NAME AS RECEIVER_NAME,
       AAW.AD_IF_REPORT_ANSWER,
       AAR.REPORT_TIME,
       AAW.AD_RESPONSE_INTERVAL,
       AAR.REPORT_TIME + (AAW.AD_RESPONSE_INTERVAL / 24 / 60) AS RESPONSE_EVENT_TIME,
       AAW.AD_COMPLETE_INTERVAL,
       AAR.RESPONSE_TIME,
       AAR.RESPONSE_TIME + (AAW.AD_COMPLETE_INTERVAL / 24 / 60) AS COMPLETE_EVENT_TIME,
       /*(SELECT PT1.MODE_ID
        FROM AD_REPORT_ROLE PT1
       WHERE PT1.ROLE_ID = AAW.AD_RECEIVER_ROLE_ID
         AND PT1.STAGE_ID = 'RESPONSE')*/
       T4.MODE_ID RESPONSE_MODE_ID, --响应通讯方式
       /* (SELECT PT1.MODE_ID
        FROM AD_REPORT_ROLE PT1
       WHERE PT1.ROLE_ID = AAW.AD_RECEIVER_ROLE_ID
         AND PT1.STAGE_ID = 'COMPLETE') COMPLETE_MODE_ID,*/ --完成通讯方式
       SUW.LINE_CODE AS USER_LINE_CODE
  FROM AD_ALARM_REPORT   AAR, --预警记录表
       AD_ALARM_WORKFLOW AAW, --流程表
       SYS_USER          SUW, --获取接收人
       AD_REPORT_ROLE    T4 --根据角色获取通讯方式(一角色多个通讯方式)
 WHERE AAR.ALARM_TYPE_ID = AAW.ALARM_TYPE_ID
   AND AAW.AD_RECEIVER_ROLE_ID = T4.ROLE_ID
   AND T4.STAGE_ID = 'RESPONSE'
   AND SUW.IF_REPONSER = 'true'
   AND (SUW.LINE_CODE = AAR.LINE_CODE OR SUW.LINE_CODE = 'ANDON-LINE-ALL')
   AND INSTR(',' || SUW.USER_AD_ROLES || ',',
             ',' || AAW.AD_RECEIVER_ROLE_ID || ',') > 0
;