基于confirmation_number的列值串联-解网

问：

我有以下两张表

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需要在以下基础上进行组合相同的 Confirmation# 列。您给了我查询，该查询将相同的 Confirmation# 的注释连接起来，并与 work_request_note 连接。输出按升序连接每个确认#的注释，但我们需要按升序连接每个确认的这些注释.....笔记可以按 Confrimation# 列合并，并按 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 工作请求已分配给 Mary Bang MBCorpCB1 10/04/2023 13：27：57 工作请求由 Mary Bang MBCorpCB1 完成 2023 年 10 月 11 日 08：58：59 工作请求已重新分配给 Mona Lash f2326COcorp 2023 年 10 月 11 日 08：59：01 Mona Lash f2326COcorp 将工作请求重置为新工作请求”

所需值应按降序排列，如下所示 “2023 年 10 月 11 日 08：59：01 Mona Lash f2326COcorp 将工作请求重置为新工作请求 08：58：59 工作请求已重新分配给 Mona Lash f2326COcorp 2023 年 10 月 04 日 13：27：57 工作请求由 Mary Bang MBCorpCB1 完成 2023 年 10 月 04 日 13：27：55 工作请求已分配给 Mary Bang MBCorpCB1”

SQL Oracle PLSQL

你的意思是只是在你的列表调用中更改为吗？（您可能想探索为将来的问题创建一个最小的可重现示例 - 当您只是呈现一个几乎没有相关上下文的大型查询时，很难看出问题是什么，似乎有很多不相关的代码和信息，并且没有数据......ORDER BY "Date Submitted"ORDER BY "Date Submitted" DESC

0赞 Vimal 11/10/2023

我更新了脚本，实际上我使用了 ORDER BY ORDER BY TO_CHAR（“创建日期”， 'MM/DD/YYYY HH24：MI：SS'） DESC 但仍然不起作用，无论是 DESC 还是 ASC。它只是按 ASC 顺序组合 NOTES 列

0赞 Alex Poole 11/10/2023

在使用日期对某些东西进行排序之前，不要将日期转换为字符串（除非是 ISO 格式，即使那样，这也是一个毫无意义的步骤）。

0赞 Vimal 11/10/2023

所以我只是取“创建日期”？

答：

1赞 MT0 11/10/2023 #1

对日期使用ISO8601格式（可以作为字符串排序），或者将日期作为日期传递，而不是子查询分解子句（子句）中的格式化字符串。YYYY-MM-DD HH24:MI:SSWITH

如果您以该格式传递字符串并尝试对它们进行排序，那么您将按月、日和年进行排序。MM/DD/YYYY HH24:MI:SS

因此，不要在子查询分解子句中将日期格式化为字符串，只需将它们保留为日期（并且不要将消息和注释日期连接到文本中），然后在使用日期值对查询进行排序后再进行格式设置：

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;

@Vimal 请确保使用正确的标识符。您在子查询分解子句（ clause）中使用的标识符与最终 .我的答案在子句中定义了一列，然后在最终使用该列，因此，如果您使用的是 SELECT'，那么您将收到错误，因为尚未定义标识符。WITHSELECTnote_textWITHSELECT"Notes" in the final

上一个：如何从 oracle 中的 OR 条件获取数据

下一个：Oracle ORA_HASH功能。结果会是恒定的吗？[复制]

基于confirmation_number的列值串联

Concatenation of column values on bases of confirmation_number

评论

评论