oracle 基于confirmation_number的列值连接

kzipqqlq  于 2023-11-17  发布在  Oracle
关注(0)|答案(1)|浏览(111)

下面有两张table
Work_Request表

ASSIGNED_TO_USER_ID NUMBER (22,10)  
COMPANY_ID  NUMBER (5)  
COMPLETE_DATE   DATE    
CORP_ID CHAR (2 Byte)   
CREATE_DATE DATE    
CREATE_USER_ID  NUMBER (22,10)  
FINANCIAL_INSTITUTION_ID    CHAR (2 Byte)   
MODIFY_DATE DATE    
MODIFY_USER_ID  NUMBER (22,10)  
STATUS_ID   INTEGER 
TOKEN_NUMBER    VARCHAR2 (16 Byte)  
WORK_REQUEST_DESC   VARCHAR2 (500 Byte) 
WORK_REQUEST_DETAILS    CLOB    
WORK_REQUEST_ID VARCHAR2 (36 Byte)  
WORK_REQUEST_NUMBER VARCHAR2 (25 Byte)  
WORK_REQUEST_TYPE_ID    VARCHAR2 (36 Byte)

字符串
Work_Request_Note表

CREATE_DATE DATE    
CREATE_USER_ID  NUMBER (22,10)  
NOTE_TEXT   VARCHAR2 (4000 Byte)    
WORK_REQUEST_ID VARCHAR2 (36 Byte)  
WORK_REQUEST_NOTE_ID    VARCHAR2 (36 Byte)

型
我从你那里得到的脚本，我有一个列在工作请求表名称为笔记，需要合并的基础上相同的确认#列。你给了我查询，其中串联的笔记相同的确认#和加入工作请求_笔记。输出串联的笔记为每个确认#按升序排列，但我们需要串联这些笔记为每个确认按升序排列.....笔记可以合并确认#按work_request_note.create_date降序排列的列和顺序这里是我的查询，它是根据升序排列的确认#合并注解
我试过下面的脚本：

WITH CombinedNotes AS (
       SELECT
        CASE
            WHEN Lpad(DBMS_LOB.SubStr(work_request_details, 
                    DBMS_LOB.InStr(work_request_details, '"', DBMS_LOB.InStr(work_request_details, '"CompanyNumber":"') + Length('"CompanyNumber":"'), 1) - (DBMS_LOB.InStr(work_request_details, '"CompanyNumber":"') + Length('"CompanyNumber":"')), 
                    DBMS_LOB.InStr(work_request_details, '"CompanyNumber":"') + Length('"CompanyNumber":"')
                ), 5, 0) = '0Text'
                THEN Lpad(TO_CHAR(COMPANY.COMPANY_ID), 5, 0)
            ELSE Lpad(DBMS_LOB.SubStr(work_request_details, 
                    DBMS_LOB.InStr(work_request_details, '"', DBMS_LOB.InStr(work_request_details, '"CompanyNumber":"') + Length('"CompanyNumber":"'), 1) - (DBMS_LOB.InStr(work_request_details, '"CompanyNumber":"') + Length('"CompanyNumber":"')), 
                    DBMS_LOB.InStr(work_request_details, '"CompanyNumber":"') + Length('"CompanyNumber":"')
                ), 5, 0)
        END AS "COMPANY NUMBER",
        COMPANY.COMPANY_NAME AS "COMPANY NAME",
        work_request_number AS "Confirmation #",
        work_request_type.WORK_REQUEST_TYPE_NAME AS "Request Type",
        --work_request.WORK_REQUEST_DESC AS "Detail Description",
        regexp_replace(work_request.WORK_REQUEST_DESC, '<.+?>') "Detail Description",
        TO_CHAR(work_request.CREATE_DATE, 'MM/DD/YYYY HH24:MI') AS "Date Submitted",
        TO_CHAR(work_request.COMPLETE_DATE, 'MM/DD/YYYY HH24:MI') AS "Date Completed",
        ROUND(TO_NUMBER(work_request.COMPLETE_DATE - work_request.CREATE_DATE)) AS "Days to Complete",
        CMS_USER.USER_NAME AS "Assigned To Id",
        CMS_USER.USER_DESCRIPTION AS "Assigned To NAME",
        deleted_cms_user.USER_NAME AS "Deleted Assigned To ID",
        deleted_cms_user.USER_DESCRIPTION AS "Deleted Assigned To Name",
        CASE
            WHEN (DBMS_LOB.SubStr(work_request_details, 
                DBMS_LOB.InStr(work_request_details, '"', DBMS_LOB.InStr(work_request_details, '"IsComplaint":') + Length('"IsComplaint":'), 1) - (DBMS_LOB.InStr(work_request_details, '"IsComplaint":') + Length('"IsComplaint":,')),
                DBMS_LOB.InStr(work_request_details, '"IsComplaint":') + Length('"IsComplaint":')
            )) = 'true' THEN 'Y'
            ELSE 'N'
        END AS "Is Complaint",
        CASE
            WHEN (DBMS_LOB.SubStr(work_request_details, 
                DBMS_LOB.InStr(work_request_details, '"', DBMS_LOB.InStr(work_request_details, '"CallResultedInCustomerComplaint":"') + Length('"CallResultedInCustomerComplaint":"'), 1) - (DBMS_LOB.InStr(work_request_details, '"CallResultedInCustomerComplaint":"') + Length('"CallResultedInCustomerComplaint":"')),
                DBMS_LOB.InStr(work_request_details, '"CallResultedInCustomerComplaint":"') + Length('"CallResultedInCustomerComplaint":"')
            )) = 'Yes' THEN 'Y'
            ELSE 'N'
        END AS "Results in Complaint",
        TO_CHAR(work_request_note.create_date, 'MM/DD/YYYY HH24:MI:SS') || ' ' || REPLACE(REPLACE(SUBSTR(work_request_note.note_text, 1, 10000), '(', ''), ')', '') || ' '  AS "Notes",
        TO_CHAR(work_request_message.create_date, 'MM/DD/YYYY HH24:MI:SS') || ' ' || REPLACE(REPLACE(SUBSTR(work_request_message.message_text, 1, 10000), '(', ''), ')', '') || ' '  AS "Messages",
        --SUBSTR(work_request_message.message_text, 1, 10000) AS "Messages",
        work_request_status.STATUS AS "Status",
        work_request.create_date As "Creation Date"
    FROM work_request
    LEFT JOIN work_request_note ON work_request.WORK_REQUEST_ID = work_request_note.WORK_REQUEST_ID
    LEFT JOIN work_request_message ON work_request.WORK_REQUEST_ID = work_request_message.WORK_REQUEST_ID
    LEFT JOIN work_request_type ON work_request.WORK_REQUEST_TYPE_ID = work_request_type.WORK_REQUEST_TYPE_ID
    LEFT JOIN CMS_USER ON work_request.ASSIGNED_TO_USER_ID = CMS_USER.SEQ_USER_ID
    LEFT JOIN deleted_cms_user ON work_request.ASSIGNED_TO_USER_ID = deleted_cms_user.USER_ID
    LEFT JOIN COMPANY ON work_request.company_id = company.company_id AND company.corp_ID IN ('CB', 'CC')
    LEFT JOIN work_request_status ON work_request.STATUS_ID = work_request_status.STATUS_ID
    WHERE (work_request.corp_ID IN ('CB', 'CC') OR work_request.financial_institution_id = 'CO')
        AND work_request_type.WORK_REQUEST_TYPE_NAME = 'Client Notification Exception Request'
        AND TRUNC(work_request.create_date) BETWEEN TO_DATE('10/01/2023', 'MM/DD/YYYY') AND TO_DATE('11/01/2023', 'MM/DD/YYYY')
        AND ((work_request.create_date > sysdate-30) AND ( work_request.STATUS_ID = 90))
        OR work_request.STATUS_ID IN (0,10)
    UNION
    SELECT
        CASE
            WHEN Lpad(DBMS_LOB.SubStr(work_request_details, 
                    DBMS_LOB.InStr(work_request_details, '"', DBMS_LOB.InStr(work_request_details, '"CompanyNumber":"') + Length('"CompanyNumber":"'), 1) - (DBMS_LOB.InStr(work_request_details, '"CompanyNumber":"') + Length('"CompanyNumber":"')), 
                    DBMS_LOB.InStr(work_request_details, '"CompanyNumber":"') + Length('"CompanyNumber":"')
                ), 5, 0) = '0Text'
                THEN Lpad(TO_CHAR(COMPANY.COMPANY_ID), 5, 0)
            ELSE Lpad(DBMS_LOB.SubStr(work_request_details, 
                    DBMS_LOB.InStr(work_request_details, '"', DBMS_LOB.InStr(work_request_details, '"CompanyNumber":"') + Length('"CompanyNumber":"'), 1) - (DBMS_LOB.InStr(work_request_details, '"CompanyNumber":"') + Length('"CompanyNumber":"')), 
                    DBMS_LOB.InStr(work_request_details, '"CompanyNumber":"') + Length('"CompanyNumber":"')
                ), 5, 0)
        END AS "COMPANY NUMBER",
        COMPANY.COMPANY_NAME AS "COMPANY NAME",
        work_request_number AS "Confirmation #",
        work_request_type.WORK_REQUEST_TYPE_NAME AS "Request Type",
        --work_request.WORK_REQUEST_DESC AS "Detail Description",
        regexp_replace(work_request.WORK_REQUEST_DESC, '<.+?>') "Detail Description",
        TO_CHAR(work_request.CREATE_DATE, 'MM/DD/YYYY HH24:MI') AS "Date Submitted",
        TO_CHAR(work_request.COMPLETE_DATE, 'MM/DD/YYYY HH24:MI') AS "Date Completed",
        ROUND(TO_NUMBER(work_request.COMPLETE_DATE - work_request.CREATE_DATE)) AS "Days to Complete",
        CMS_USER.USER_NAME AS "Assigned To Id",
        CMS_USER.USER_DESCRIPTION AS "Assigned To NAME",
        deleted_cms_user.USER_NAME AS "Deleted Assigned To ID",
        deleted_cms_user.USER_DESCRIPTION AS "Deleted Assigned To Name",
        'N' AS "Is Complaint",
        'N' AS "Results in Complaint",
        TO_CHAR(work_request_note.create_date, 'MM/DD/YYYY HH24:MI:SS') || ' ' || REPLACE(REPLACE(SUBSTR(work_request_note.note_text, 1, 10000), '(', ''), ')', '') || ' '  AS "Notes",
        TO_CHAR(work_request_message.create_date, 'MM/DD/YYYY HH24:MI:SS') || ' ' || REPLACE(REPLACE(SUBSTR(work_request_message.message_text, 1, 10000), '(', ''), ')', '') || ' '  AS "Messages",
        --SUBSTR(work_request_message.message_text, 1, 10000) AS "Messages",
        work_request_status.STATUS As "Status",
        work_request.create_date As "Creation Date"
    FROM work_request
    LEFT JOIN work_request_note ON work_request.WORK_REQUEST_ID = work_request_note.WORK_REQUEST_ID
    LEFT JOIN work_request_message ON work_request.WORK_REQUEST_ID = work_request_message.WORK_REQUEST_ID
    LEFT JOIN work_request_type ON work_request.WORK_REQUEST_TYPE_ID = work_request_type.WORK_REQUEST_TYPE_ID
    LEFT JOIN CMS_USER ON work_request.ASSIGNED_TO_USER_ID = CMS_USER.SEQ_USER_ID
    LEFT JOIN deleted_cms_user ON work_request.ASSIGNED_TO_USER_ID = deleted_cms_user.USER_ID
    LEFT JOIN COMPANY ON work_request.company_id = company.company_id AND company.corp_ID IN ('CB', 'CC')
    LEFT JOIN work_request_status ON work_request.STATUS_ID = work_request_status.STATUS_ID
    WHERE (work_request.corp_ID IN ('CB', 'CC') OR work_request.financial_institution_id = 'CO')
        AND work_request_type.WORK_REQUEST_TYPE_NAME <> 'Client Notification Exception Request'
        AND TRUNC(work_request.create_date) BETWEEN TO_DATE('10/01/2023', 'MM/DD/YYYY') AND TO_DATE('11/09/2023', 'MM/DD/YYYY')
        AND ((work_request.create_date > sysdate-30) AND ( work_request.STATUS_ID = 90))
        OR work_request.STATUS_ID IN (0,10)
),
NotesWithStatus AS (
    SELECT
        "COMPANY NUMBER",
        "COMPANY NAME",
        "Confirmation #",
        "Request Type",
        "Detail Description",
        "Date Submitted",
        "Date Completed",
        "Days to Complete",
        "Assigned To Id",
        "Assigned To NAME",
        "Deleted Assigned To ID",
        "Deleted Assigned To Name",
        "Is Complaint",
        "Results in Complaint",
        --MAX("Messages") AS "Messages", -- Use MAX to include Messages
        LISTAGG("Messages", ' ' ON OVERFLOW TRUNCATE '...' ) WITHIN GROUP (ORDER BY ORDER BY TO_CHAR("Creation Date", 'MM/DD/YYYY HH24:MI:SS') DESC) AS "Messages",
        LISTAGG("Notes", ' ' ON OVERFLOW TRUNCATE '...' ) WITHIN GROUP (ORDER BY ORDER BY TO_CHAR("Creation Date", 'MM/DD/YYYY HH24:MI:SS') DESC) AS "Notes",
        "Status",
        "Creation Date"
    FROM CombinedNotes
    GROUP BY
        "COMPANY NUMBER",
        "COMPANY NAME",
        "Confirmation #",
        "Request Type",
        "Detail Description",
        "Date Submitted",
        "Date Completed",
        "Days to Complete",
        "Assigned To Id",
        "Assigned To NAME",
        "Deleted Assigned To ID",
        "Deleted Assigned To Name",
        "Is Complaint",
        "Results in Complaint",
        "Status",
        "Creation Date"
)
SELECT
    "COMPANY NUMBER",
    "COMPANY NAME",
    "Confirmation #",
    "Request Type",
    "Detail Description",
    "Date Submitted",
    "Date Completed",
    "Days to Complete",
    "Assigned To Id",
    "Assigned To NAME",
    "Deleted Assigned To ID",
    "Deleted Assigned To Name",
    "Is Complaint",
    "Results in Complaint",
    "Notes",
    "Messages",
    "Status"
FROM NotesWithStatus
ORDER BY TO_CHAR("Creation Date", 'MM/DD/YYYY HH24:MI:SS');

型
我发现注解列中的值按升序排列，如下所示“10/04/2023 13：27：55 Work Request was assigned to玛丽邦MBCorpCB 1 10/04/2023 13：27：57工作申请由玛丽邦MBCorpCB 1完成2023年10月11日08：58：59工作请求被重新分配给莫纳Lash f2326 COcorp 2023年10月11日08：59：01工作请求已由莫纳Lash f2326 COcorp重置为新“
所需值应按如下方式降序：“10/11/2023 08：59：01 Work Request was reset to new by莫纳Lash f2326 COcorp 08：58：59 Work Request was reassigned to莫纳Lash f2326 COcorp 10/04/2023 13：27：57 Work Request was completed by玛丽邦MBCorpCB 1 10/04/2023 13：27：55 Work Request was assigned to玛丽邦MBCorpCB 1”

oracle

1条答案

按热度按时间
laik7k3q

laik7k3q1#

对日期YYYY-MM-DD HH24:MI:SS（可以作为字符串排序）使用ISO8601格式，或者将日期作为日期传递，而不是子查询因子子句（WITH子句）中的格式化字符串。
如果你传递MM/DD/YYYY HH24:MI:SS的字符串，并试图对它们进行排序，那么你将按月、日和年进行排序。
因此，不要在子查询factoring子句中将日期格式化为字符串，只需将它们保留为日期（并且不要将消息和注解日期连接到文本中），然后在使用日期值对查询进行排序之后进行格式化：

WITH CombinedNotes AS (
  SELECT lots_of_columns,
         work_request.CREATE_DATE,    -- Don't format here
         work_request.COMPLETE_DATE,  -- Don't format here
         more_columns,
         work_request_note.create_date AS note_create_date,
                                      -- Don't format or concatenate here
         TRANSLATE(SUBSTR(work_request_note.note_text, 1, 10000), 'A()', 'A')
           AS note_text,
         work_request_message.create_date AS message_create_date,
                                      -- Don't format or concatenate here
         TRANSLATE(SUBSTR(work_request_message.message_text, 1, 10000), 'A()', 'A')
           AS message_text,
         even_more_columns
  FROM   many_tables
)
SELECT lots_of_columns,
       -- Format the dates at the last point you are going to need them for
       -- ordering
       TO_CHAR(create_date, 'MM/DD/YYYY HH24:MI:SS') AS "Date Submitted",
       TO_CHAR(create_date, 'MM/DD/YYYY HH24:MI:SS') AS "Date Completed",
       more_columns,
       -- Use the date in the ORDER BY and not a formatted string
       LISTAGG(
         TO_CHAR(message_create_date, 'MM/DD/YYYY HH24:MI:SS')
         || message_text,
         ' ' ON OVERFLOW TRUNCATE '...'
       ) WITHIN GROUP (ORDER BY message_create_date DESC) AS "Messages",
       -- Use the date in the ORDER BY and not a formatted string
       LISTAGG(
         TO_CHAR(note_create_date, 'MM/DD/YYYY HH24:MI:SS')
         || note_text,
         ' ' ON OVERFLOW TRUNCATE '...'
       ) WITHIN GROUP (ORDER BY note_create_date DESC) AS "Notes",
         even_more_columns
FROM   CombinedNotes
GROUP BY
       lots_of_columns,
       more_columns,
       even_more_columns
ORDER BY
       -- Use the date in the ORDER BY and not a formatted string
       create_date;

字符串

赞(0）分享  回复(0）举报  2023-11-17
我来回答

相关问题

    查看更多

    热门标签

    更多
    JavaquerypythonNode开发语言requestUtil数据库Table后端算法LoggerMessageElementParser

    最新问答

    更多
    • 蜀ICP备13028337号-1 大数据知识库 https://www.saoniuhuo.com © All rights reserved
    • 本站内容来源互联网,如果侵犯您的权益请联系我们删除, 联系方式：448109455@qq.com