在 Oracle 10g 中连接 5 个表中没有重复值的数据并获取具有自定义状态的结果

Question

我有 5 个表格，必须使用它们来获取盗窃索赔的一组待处理文件。我使用的是Oracle 10g。表结构如下所示。

1. mo_documents_master    -- master table for all document types
    CREATE TABLE "MO_DOCUMENT_MASTER"
    (
        "DOC_ID"   NUMBER NOT NULL ENABLE,
        "DOC_TYPE_NAME" VARCHAR2(100 BYTE),
        "STATUS"   VARCHAR2(50 BYTE),
        CONSTRAINT "MO_DOCUMENT_MASTER_PK" PRIMARY KEY ("DOC_ID") 
    )

2. mo_theft_pvt_documents   -- Contains list of documents applicable for a theft claim; they can be mandatory or optional
    CREATE TABLE "MO_THEFT_PVT_DOCUMENTS"
    (
        "DOC_ID"       NUMBER NOT NULL ENABLE,
        "STAGE"        VARCHAR2(20 BYTE),
        "IS_MANDATORY" VARCHAR2(10 BYTE),   -- true or false; true means document is mandatory
        CONSTRAINT "MO_THEFT_DOCUMENTS_PK" PRIMARY KEY ("DOC_ID") 
        CONSTRAINT "MO_THEFT_PVT_DOCUMENTS_MO_FK1" FOREIGN KEY ("DOC_ID") REFERENCES "MO_DOCUMENT_MASTER" ("DOC_ID") ENABLE
    )

3. mo_theft_pvt_doc_queue  -- Mapping of which documents are applicable for a particular queue
    CREATE TABLE "MO_THEFT_PVT_DOC_QUEUE"
    (
        "DOC_ID"   NUMBER NOT NULL ENABLE,
        "QUEUE_ID" NUMBER NOT NULL ENABLE,
        CONSTRAINT "MO_THEFT_DOC_QUEUE_PK" PRIMARY KEY ("DOC_ID", "QUEUE_ID") 
        CONSTRAINT "MO_THEFT_DOC_QUEUE_MO_QUE_FK1" FOREIGN KEY ("QUEUE_ID") REFERENCES "MO_QUEUE_MASTER" ("ID") ENABLE,
        CONSTRAINT "MO_THEFT_PVT_DOC_QUEUE_MO_FK1" FOREIGN KEY ("DOC_ID") REFERENCES "MO_THEFT_PVT_DOCUMENTS" ("DOC_ID") ENABLE
    )

4. mo_claim_documents    -- list of uploaded documents for a claim. 
    CREATE TABLE "MO_CLAIM_DOCUMENTS"
    (
        "ID"           NUMBER NOT NULL ENABLE,
        "CLAIM_REF_NO" VARCHAR2(50 BYTE),
        "DOC_ID"       NUMBER,
        "DOCUMENTURL"  VARCHAR2(255 BYTE),
        "FILENAME"     VARCHAR2(255 BYTE),
        "UPLOADED_ON" TIMESTAMP (6),
        "UPLOADED_BY" VARCHAR2(100 BYTE),
        "STATUS"      VARCHAR2(30 BYTE),    -- STATUS can be 'Active' or 'Inactive'
        CONSTRAINT "M0_CLAIM_DOCUMENTS_PK" PRIMARY KEY ("ID") 
        CONSTRAINT "MO_OD_CLAIM_DOCUMENTS_MO__FK1" FOREIGN KEY ("CLAIM_REF_NO") REFERENCES "MO_CLAIM_MASTER" ("CLAIM_REF_NO") ENABLE,
        CONSTRAINT "MO_OD_CLAIM_DOCUMENTS_MO__FK2" FOREIGN KEY ("DOC_ID") REFERENCES "MO_DOCUMENT_MASTER" ("DOC_ID") ENABLE
    )

5. mo_claim_doc_waiver;  -- list of document waiver requests for a claim
    CREATE TABLE "MO_CLAIM_DOC_WAIVER"
    (
        "ID"           NUMBER NOT NULL ENABLE,
        "CLAIM_REF_NO" VARCHAR2(50 BYTE),
        "DOC_ID"       NUMBER,
        "REQUESTED_DATE" TIMESTAMP (6),
        "REQUSETED_BY" VARCHAR2(200 BYTE),
        "ACTION_DATE" TIMESTAMP (6),
        "ACTION_BY"       VARCHAR2(200 BYTE),
        "REQUEST_REMARKS" VARCHAR2(255 BYTE),
        "ACTION_REMARKS"  VARCHAR2(255 BYTE),
        "STATUS"          VARCHAR2(50 BYTE),   -- STATUS can be 'Waiver Requested', 'Waived' or 'Rejected'
        CONSTRAINT "MO_CLAIM_DOC_WAIVER_PK" PRIMARY KEY ("ID") 
        CONSTRAINT "MO_CLAIM_DOC_WAIVER_MO_CL_FK1" FOREIGN KEY ("CLAIM_REF_NO") REFERENCES "MO_CLAIM_MASTER" ("CLAIM_REF_NO") ENABLE,
        CONSTRAINT "MO_CLAIM_DOC_WAIVER_MO_DO_FK1" FOREIGN KEY ("DOC_ID") REFERENCES "MO_DOCUMENT_MASTER" ("DOC_ID") ENABLE
    )

我的要求是找到特定 QUEUE_ID 和 CLAIM_REF_NO 的“剩余”强制文档列表（这两个是查询的输入）。

索赔的“剩余”文档（“CLAIM_REF_NO”）是指对于特定“QUEUE_ID”而言“强制”且尚未上传或放弃的文档。 另外，我需要每个“剩余”文档类型的状态。状态可以是 一个。 “待处理”（文档未上传或已上传但处于非活动状态或豁免请求已被拒绝） b. “已请求豁免”（未上传的文档且状态为“已请求豁免”）

对于特定文档类型，我只需要一种状态。 （例如，如果尚未上传的文档在表“MO_CLAIM_DOC_WAIVER”中有 2 个状态 -“已请求豁免”和“已拒绝”，则查询应返回该文档的状态为“已请求豁免” 提出这样的要求的原因是，如果存在尚未批准或拒绝的豁免请求，则不应允许用户提出新的豁免请求。

我创建了以下查询。但是，当表 MO_CLAIM_DOC_WAIVER 中有 2 条状态为“已请求豁免”和“已拒绝”的记录时，我的查询失败。 请提供使用 SQL 或过程的解决方案。

    select distinct b.doc_id, c.doc_type_name, b.stage, b.is_mandatory, b.queue_id, case when a.status = 'Waiver Requested' then 'Waiver Requested' else 'Pending' end as status
    FROM 
    (SELECT tpd.doc_id, tpd.stage, tpd.is_mandatory, tpdq.queue_id
     FROM mo_theft_pvt_documents tpd , mo_theft_pvt_doc_queue tpdq
     WHERE tpd.is_mandatory='true' and tpd.doc_id  = tpdq.doc_id AND tpdq.queue_id =:queue_id AND tpd.doc_id NOT IN (SELECT doc_id FROM mo_claim_documents where status  ='Active'and claim_ref_no =:claim_ref_no UNION all SELECT doc_id from mo_claim_doc_waiver WHERE claim_ref_no =:claim_ref_no AND status = 'Waived')
    ) b
    LEFT JOIN  mo_claim_doc_waiver a on b.doc_id=a.doc_id
    left join mo_document_master c on b.doc_id=c.doc_id
    order by 1;

Answer 1

您可以根据自己的逻辑对不同的状态进行“排名”，然后从中选择最新的状态。您可以使用类似于下面的逻辑，该逻辑会推倒所有“拒绝”。

如果您关心其余状态的顺序，请确保包含所有其他可能的状态。

with t1 as
( select 1 id, 'Waiver Requested'        status from dual union all
  select 1 id, 'Rejected'        status from dual union all
  select 2 id, 'Waiver Requested'        status from dual union all
  select 2 id, 'Waiver Requested'        status from dual union all
  select 3 id, 'Rejected'                status from dual union all
  select 3 id, 'Rejected'                status from dual union all
  select 4 id, 'Waiver Requested'        status from dual union all
  select 4 id, 'Cancelled'               status from dual
)
select id,
       status,
       max(status) KEEP (DENSE_RANK FIRST
                         order by (case when status ='Rejected' then -1
                                            else 1
                                       end) desc)
          over (partition by id) final_status
  from t1

        ID STATUS           FINAL_STATUS
---------- ---------------- ----------------
         1 Waiver Requested Waiver Requested
         1 Rejected         Waiver Requested
         2 Waiver Requested Waiver Requested
         2 Waiver Requested Waiver Requested
         3 Rejected         Rejected
         3 Rejected         Rejected
         4 Waiver Requested Waiver Requested
         4 Cancelled        Waiver Requested