JD Edwards E1 (DB2)的复杂SQL查询帮助

Question

首先，我承认我不是数据库专家，也不是SQL专家。我知道一些基本的查询，并且做了一些稍微复杂一些的查询，但这是我所无法理解的。

背景

我公司使用JD Edwards Enterprise One作为他们的ERP应用。下表由本系统中的实际表表示。为了简单起见，我在下面对它们作了解释。后端数据库是运行在AS/400平台上的DB2。

表定义

表:项目分类账(IL)

栏：

Business Unit
Work Center
Order #
Doc Type
Trans. Date
Qty Completed

表: W/O时间事务(WOT)

栏：

Business Unit
Work Center
Order #
Hours Type
G/L Date
Hours Worked

备注：

• 实际上，这些表的列比这里显示的要多很多。
• 查询需要过滤项目分类账，以便文档类型=“IC”。
• 查询需要过滤时间事务，以便小时类型=“3”。

样本数据

注意:对于格式不佳表示歉意，但是似乎没有办法很好地表示这个位置的桌子。

项目分类账(IL)

Business Unit   Work Center Order # Doc Type    Trans. Date Qty Completed
BU-1    WC-1    1   IC  8/7/20  20
BU-1    WC-1    1   IC  8/7/20  40
BU-1    WC-1    2   IC  8/7/20  10
BU-1    WC-2    3   IC  8/7/20  40
BU-1    WC-2    3   IC  8/7/20  10
BU-2    WC-4    6   IC  8/7/20  10
BU-2    WC-5    5   IC  8/7/20  60
BU-1    WC-1    2   IC  8/6/20  50
BU-1    WC-3    4   IC  8/6/20  30
BU-2    WC-5    5   IC  8/5/20  50
BU-2    WC-5    7   IC  8/1/20  20
BU-2    WC-5    8   IC  7/26/20 30
BU-2    WC-5    8   IC  7/25/20 50

时间事务(WOT)

Business Unit   Work Center Order # Hours Type  G/L Date    Hours Worked
BU-2    WC-4    6   3   8/8/20  7.4
BU-1    WC-1    1   3   8/7/20  3.92
BU-1    WC-2    3   3   8/7/20  8
BU-1    WC-2    3   3   8/7/20  7
BU-1    WC-3    4   3   8/7/20  6.2
BU-2    WC-4    6   3   8/7/20  1.1
BU-1    WC-1    2   3   8/6/20  1.57
BU-2    WC-5    7   3   8/1/20  3.9
BU-2    WC-5    8   3   7/25/20 11.3

注意:在时间事务表中没有第5号订单的条目。

目标

目标1：

我们正试图按业务单位(如BU-1)和工作中心(例如WC-1)进行分组，按周(未来:按月添加其他时间范围)计算工时和完成数量。

重要的是要注意的是，在某一天的项目分类账中可能有多个完成，并且可能有一个更小的(有时只是一个)条目的时间交易数的小时工作，对应于这些完成。还有一些情况是，工作时间可能要到第二天才会张贴(这是一个24×7的轮班操作)，在这种情况下，相关的事件(完成和记录给定订单的时间)是在不同的日子。

如果我们将这两个表中的数据合并在一起，其中订单号是常见的联系：

注意:我手动组装了这个，这样可能会出现错误。

Business Unit   Work Center Order # Date    Hours Worked    Qty Completed
BU-2    WC-4    6   8/8/20  7.4 null
BU-1    WC-1    1   8/7/20  3.92    60
BU-1    WC-1    2   8/7/20  null    10
BU-1    WC-2    3   8/7/20  15  50
BU-1    WC-3    4   8/7/20  6.2 null
BU-2    WC-4    6   8/7/20  1.1 10
BU-2    WC-5    5   8/7/20  null    60
BU-1    WC-1    2   8/6/20  1.57    50
BU-1    WC-3    4   8/6/20  null    30
BU-2    WC-5    5   8/5/20  null    50
BU-2    WC-5    7   8/1/20  3.9 20
BU-2    WC-5    8   7/26/20 null    30
BU-2    WC-5    8   7/25/20 11.3    50

现在，按业务单位、工作中心和每周合计：

Week    Business Unit   Work Center Hours Worked    Qty Completed
8/2/20 – 8/8/20             
    BU-2    WC-4    8.5 10
    BU-1    WC-1    5.49    120
    BU-1    WC-2    15  50
    BU-1    WC-3    6.2 30
    BU-2    WC-5    0   50
7/26/20 – 8/1/20                
    BU-2    WC-5    3.9 50
7/19/20 – 7/25/20               
    BU-2    WC-5    11.3    50

我不知道如何使用适当的SQL查询来实现这个结果。我认为这需要一个完全的外部连接，但我尝试过的一切似乎都会为结果集带来某种笛卡尔积，这只是将两个表中的数据组合在一起的第一步，更不用说最后的聚合和分组了。

对于如何编写查询(或者多个查询)，有什么帮助和建议吗？如果能取得这样的结果，我们将不胜感激。

更新 (8/14/20)：

我试图实现一个基于https://stackoverflow.com/users/10418264/mark-barinstein (Mark )建议的查询。我已经分析了建议的查询，我想我理解它的大部分。

我很难把JD爱德华兹朱利安的日期变成一种可以用来计算一年中的每周的表格。这是五月的最新尝试。注意，这里使用的是实际的表名和列名。

SELECT
  COALESCE(ILG.Week, WOG.Week) AS WEEK,
  COALESCE(ILG.BU, WOG.BU) AS BU,
  COALESCE(ILG.WC, WOG.WC) AS WC,
  COALESCE(WOG.Hours, 0) AS HOURS,
  COALESCE(ILG.Qty, 0) AS QTY
FROM
  (
    SELECT
      F31122.WTMMCU AS BU,
      F31122.WTMCU AS WC,
      WEEK(DATE(CAST(1900 + F31122.WTDGL/1000 AS CHAR(4)) || '-01-01') + (MOD(F31122.WTDGL, 1000) - 1) DAYS) AS Week,
      SUM(F31122.WTHRW) AS Hours
    FROM PROD2DTA.F31122 AS F31122
    WHERE
      (F31122.WTTYR IN ('3')) AND
      (F31122.WTDGL BETWEEN 120200 AND 120201)
    GROUP BY
      F31122.WTMMCU,
      F31122.WTMCU
  ) WOG
FULL JOIN
  (
    SELECT
      F4111.ILMCU AS BU,
      F4111.ILLOCN AS WC,
      WEEK(DATE(CAST(1900 + F4111.ILTRDJ/1000 AS CHAR(4)) || '-01-01') + (MOD(F4111.ILTRDJ, 1000) - 1) DAYS) AS Week,
      SUM(F4111.ILTRQT) AS Qty
    FROM PROD2DTA.F4111 AS F4111
    WHERE
      (F4111.ILDCT IN ('IC')) AND
      (F4111.ILTRDJ BETWEEN 120200 AND 120201)
    GROUP BY
      F4111.ILMCU,
      F4111.ILLOCN
  ) ILG
ON ILG.BU = WOG.BU AND ILG.WC = WOG.WC AND ILG.WEEK = WOG.WEEK
ORDER BY 1 DESC, 2, 3

是否有一种方法可以简化将JDE朱利安日期转换为周号的逻辑？我明白正在做什么，但希望我能把它分解为一个函数，并称之为它。

不过，我还是会犯错误：

[SQL0122] Column WTDGL or expression in SELECT list not valid. (JdbcException)

不同的问题:在GROUP子句中可以使用列“别名”(例如BU而不是F4111.ILMCU)吗？它将使它更加可读性。

Answer 1

试试这个：

SELECT
  COALESCE(ILG.WEEK, WOG.WEEK) AS WEEK
, COALESCE(ILG.BU, WOG.BU) AS BU
, COALESCE(ILG.WC, WOG.WC) AS WC
, COALESCE(WOG.Hours, 0) AS HOURS
, COALESCE(ILG.Qty, 0) AS QTY
FROM
(
SELECT BU, WC, WEEK(TO_DATE(GL_Date, 'MM/DD/YY')) AS Week, SUM(Hours) AS Hours
FROM WO
GROUP BY BU, WC, WEEK(TO_DATE(GL_Date, 'MM/DD/YY'))
) WOG
FULL JOIN
(
SELECT 
  BU, WC
, WEEK(TO_DATE(Trans_Date, 'MM/DD/YY')) AS Week
, SUM(Qty) AS Qty
FROM IL
GROUP BY BU, WC, WEEK(TO_DATE(Trans_Date, 'MM/DD/YY'))
) ILG ON ILG.BU = WOG.BU AND ILG.WC = WOG.WC AND ILG.WEEK = WOG.WEEK
ORDER BY 1 DESC, 2, 3

db<>fiddle链接来测试它。