Oracle 10g:聚合

Question

得到了以下表格：

表T

DATE_A    | DATE_B    | ERRCODE 
----------+-----------+--------
01/MAY/13 | 01/JUN/15 | X
01/DEC/17 | 01/FEB/18 | Y

表U

ERRCODE | ERRDESC
--------+-------------------------------------------
X       | Conflicting from : {1} and to Period : {2}
Y       | Periods : {1} and {2} overlap

以下代码：

select period, wm_concat(errcode) as issues
from ((select DATE_A as period, errcode from T where DATE_A is not null) union all
      (select DATE_B, errcode from T where DATE_B is not null)
     ) di
group by period
order by period;

将按以下方式转换Table T：

PERIOD    | ISSUES
----------+--------
01/MAY/13 | X
01/JUN/15 | X
01/DEC/17 | Y
01/FEB/18 | Y

我想将上面的代码转换为：

• 它将表T中的ERRCODE替换为表U中的相应ERRDESC
• 将ERRDESC中的{1}和{2}分别替换为DATE_A和DATE_B
• 在Table V中插入结果

所以我试了一下：

INSERT INTO v (
    period,
    issues
)
    SELECT
        period,
        wm_concat(issue) AS issues
    FROM
        (
            SELECT
                t.date_a AS period,
                replace( (
                    SELECT
                        u.errdesc AS issue
                    FROM
                        u
                    WHERE
                        t.errcode = u.errcode
                ),'{1}', t.date_a) AS issue
            FROM
                t
            WHERE
                t.date_a IS NOT NULL
            UNION ALL
            SELECT
                t.date_b,
                replace( (
                    SELECT
                        u.errdesc AS issue
                    FROM
                        u
                    WHERE
                        t.errcode = u.errcode
                ),'{2}', t.date_b)
            FROM
                t
            WHERE
                t.date_b IS NOT NULL
        ) di
    GROUP BY
        period;

但我得到了这个(Table V)：

PERIOD    | ISSUES
----------+--------
01/MAY/13 | Conflicting from : 01/MAY/13 and to Period : {2}
01/JUN/15 | Conflicting from : {1} and to Period : 01/JUN/15
01/DEC/17 | Periods : 01/DEC/17 and {2} overlap
01/FEB/18 | Periods : {1} and 01/FEB/18 overlap

而不是我正在寻找的结果(Table V)：

PERIOD    | ISSUES
----------+--------
01/MAY/13 | Conflicting from : 01/MAY/13 and to Period : 01/JUN/15
01/JUN/15 | Conflicting from : 01/MAY/13 and to Period : 01/JUN/15
01/DEC/17 | Periods : 01/DEC/17 and 01/FEB/18 overlap
01/FEB/18 | Periods : 01/DEC/17 and 01/FEB/18 overlap

原因是联盟之前的select都不知道date_b，select后不知道date_a。

问题

如何修改最新的代码以获得预期的结果？

Note

CREATE TABLE T
   (     
    "DATE_A" DATE, 
    "DATE_B" DATE, 
    "ERRCODE" VARCHAR2(2)
   )  ;

Insert into T (DATE_A,DATE_B,ERRCODE) values (to_date('01/MAY/13','DD/MON/RR'),to_date('01/JUN/15','DD/MON/RR'),'X');
Insert into T (DATE_A,DATE_B,ERRCODE) values (to_date('01/DEC/17','DD/MON/RR'),to_date('01/FEB/18','DD/MON/RR'),'Y');

CREATE TABLE U
   (     
    "ERRCODE"  VARCHAR2(2), 
    "ERRDESC"  VARCHAR2(100)
   )  ;

Insert into U (ERRCODE, ERRDESC) values ('X','Conflicting from : {1} and to Period : {2}');
Insert into U (ERRCODE, ERRDESC) values ('Y','Periods : {1} and {2} overlap');

CREATE TABLE V
   (     
    "PERIOD"  DATE, 
    "ISSUES"  VARCHAR2(100)
   )  ;

commit;

Answer 1

我要做的方法是首先连接两个表并在error description列中填充值，然后将其分割为两行，如下所示：

INSERT INTO v (period, issues)
WITH t AS (SELECT to_date('01/05/2013', 'dd/mm/yyyy') date_a, to_date('01/06/2015', 'dd/mm/yyyy') date_b, 'X' errcode FROM dual UNION ALL
           SELECT to_date('01/12/2017', 'dd/mm/yyyy') date_a, to_date('01/02/2018', 'dd/mm/yyyy') date_b, 'Y' errcode FROM dual),
     u AS (SELECT 'X' errcode, 'Conflicting from : {1} and to Period : {2}' errdesc FROM dual UNION ALL
           SELECT 'Y' errcode, 'Periods : {1} and {2} overlap' errdesc FROM dual),
 dummy AS (SELECT LEVEL rn FROM dual CONNECT BY LEVEL <= 2),
   res AS (SELECT t.date_a,
                  t.date_b,
                  REPLACE(REPLACE(u.errdesc, '{1}', to_char(t.date_a, 'dd/MON/yyyy', 'nls_date_language = english')), '{2}', to_char(t.date_b, 'dd/MON/yyyy', 'nls_date_language = english')) errdesc
           FROM   t
                  INNER JOIN u ON t.errcode = u.errcode)
SELECT CASE WHEN d.rn = 1 THEN res.date_a
            WHEN d.rn = 2 THEN res.date_b
       END period,
       errdesc
FROM   res
       CROSS JOIN dummy d
ORDER BY res.date_a, d.rn;

PERIOD      ERRDESC
----------- --------------------------------------------------------------------------------
01/05/2013  Conflicting from : 01/MAY/2013 and to Period : 01/JUN/2015
01/06/2015  Conflicting from : 01/MAY/2013 and to Period : 01/JUN/2015
01/12/2017  Periods : 01/DEC/2017 and 01/FEB/2018 overlap
01/02/2018  Periods : 01/DEC/2017 and 01/FEB/2018 overlap

这是一种不枢轴；如果您处于11g或更高的位置，则可以利用UNPIVOT命令将行拆分为两行。它的工作方式是创建一个虚拟子查询，该查询包含要为每个输入行输出的所需的行数--在您的示例中，为2。

然后，我们可以将它连接到主结果集，这意味着行是重复的。然后，这只是一个计算出哪些列显示在每一行，和瞧！

只是几个关于日期的笔记：

• 请始终显式地将您的日期转换为字符串，而不是依赖nls_date_format设置；您在会话中可能拥有的内容可能与其他人的完全不同。
• 年有四位数。请不要使用2位数字，并强迫Oracle猜测，特别是当您事先知道信息！有一段时间，您可能会从使用“RR”格式掩码中得到意想不到的结果。

Answer 2

让它与简单的解决方案一起工作(如下)：

INSERT INTO v (
    period,
    issues
)
    SELECT
        period,
        wm_concat(issue) AS issues
    FROM
        (
            SELECT
                date_a AS period,
                replace(replace( (
                    SELECT 
                        u.errdesc AS issue
                    FROM
                        u
                    WHERE
                        t.errcode = u.errcode
                ),'{1}',t.date_a),'{2}',t.date_b) AS issue
            FROM
                t
            WHERE
                date_a IS NOT NULL
            UNION ALL
            SELECT
                date_b,
                replace(replace( (
                    SELECT 
                        u.errdesc AS issue
                    FROM
                        u
                    WHERE
                        t.errcode = u.errcode
                ),'{1}',t.date_a),'{2}',t.date_b)
            FROM
                t
            WHERE
                date_b IS NOT NULL
        ) di
    GROUP BY
        period;

编辑：

然而，Boneist的解决方案更好。