DB2 SQL -在子查询中获取特定的row_number值

dzhpxtsq 于 2022-11-07 发布在 DB2

关注(0)|答案(1)|浏览(165)

我正在创建一个DB2 SQL查询，该查询通过子查询从另一个表中获取SUM（CHARGE_AMOUNT）。
现在，我需要分解第1、第2和第3个CHARGE_AMOUNT字段，以便查看：明细行标识，额外费用合计，额外费用1，额外费用2，额外费用3
我无法使其工作，似乎是因为TLORDER.DETAIL_LINE_ID不在我的子子查询的范围内。
SQL error I get
下面的SQL ...请建议是否有更好的方法来执行此查询。

SELECT "TLORDER"."DETAIL_LINE_ID",
"TLORDER"."BILL_TO_NAME", 
"TLORDER"."TOTAL_CHARGES", 

-- THIS WORKS: Get the sum of the 3 extra charges from table: ACHARGE_TLORDER 
(SELECT SUM(CHARGE_AMOUNT)
FROM TMWIN.ACHARGE_TLORDER 
WHERE ACODE_ID NOT LIKE 'FSC%' AND DETAIL_LINE_ID = TLORDER.DETAIL_LINE_ID) "TOTAL XTRA CHARGES",

-- NOT WORKING: Get the most recent extra charge
(SELECT CHARGE_AMOUNT "XTRA CHARGE 1" FROM
(
SELECT CHARGE_AMOUNT, ROW_NUMBER() OVER (PARTITION BY DETAIL_LINE_ID ORDER BY ACT_ID DESC) AS rn 
FROM TMWIN.ACHARGE_TLORDER WHERE ACODE_ID <> '' AND ACODE_ID NOT LIKE 'FSC%' 
AND DETAIL_LINE_ID = TLORDER.DETAIL_LINE_ID
) 
WHERE rn = 1),

-- NOT WORKING: Get the 2nd most recent extra charge
(SELECT CHARGE_AMOUNT "XTRA CHARGE 2" FROM
(
SELECT CHARGE_AMOUNT, ROW_NUMBER() OVER (PARTITION BY DETAIL_LINE_ID ORDER BY ACT_ID DESC) AS rn 
FROM TMWIN.ACHARGE_TLORDER WHERE ACODE_ID <> '' AND ACODE_ID NOT LIKE 'FSC%' 
AND DETAIL_LINE_ID = TLORDER.DETAIL_LINE_ID
) 
WHERE rn = 2),

-- NOT WORKING: Get the 3rd most recent extra charge
(SELECT CHARGE_AMOUNT "XTRA CHARGE 3" FROM
(
SELECT CHARGE_AMOUNT, ROW_NUMBER() OVER (PARTITION BY DETAIL_LINE_ID ORDER BY ACT_ID DESC) AS rn 
FROM TMWIN.ACHARGE_TLORDER WHERE ACODE_ID <> '' AND ACODE_ID NOT LIKE 'FSC%' 
AND DETAIL_LINE_ID = TLORDER.DETAIL_LINE_ID
) 
WHERE rn = 3)

FROM   "TMWIN"."TLORDER" "TLORDER"
WHERE  "TLORDER"."BILL_TO_CODE"!='' 
AND ("TLORDER"."PICK_UP_BY_END">='9-12-2021')

下面是一个简化的数据示例：Sample Data Image

db2

来源：https://stackoverflow.com/questions/69485549/db2-sql-get-specific-row-number-values-in-sub-queries

1条答案

按热度按时间

bqucvtff1#

我不知道如何连接订单表和行表--但假设有一种方法可以执行此查询，如下所示：

WITH LINE_WITH_ROW AS (
  SELECT
     ORDER_ID, 
     CHARGE_AMOUNT, 
     ROW_NUMBER() OVER (PARTITION BY DETAIL_LINE_ID ORDER BY ACT_ID DESC) AS rn 
  FROM TMWIN.ACHARGE_TLORDER 
  WHERE ACODE_ID <> '' AND ACODE_ID NOT LIKE 'FSC%' 
)
SELECT
  TLORDER.ORDER_ID, 
  --TLORDER.DETAIL_LINE_ID, -- can't work not in the table.
  TLORDER.BILL_TO_NAME, 
  TLORDER.TOTAL_CHARGES, 
  R1.CHARGE_AMOUNT as CHARGE1,
  R2.CHARGE_AMOUNT as CHARGE2,
  R3.CHARGE_AMOUNT as CHARGE3
FROM   TMWIN.TLORDER TLORDER
LEFT JOIN LINE_WITH_ROW AS R1 ON TLORDER.ORDER_ID = R1.ORDER_ID AND RN =1
LEFT JOIN LINE_WITH_ROW AS R2 ON TLORDER.ORDER_ID = R2.ORDER_ID AND RN =2
LEFT JOIN LINE_WITH_ROW AS R3 ON TLORDER.ORDER_ID = R3.ORDER_ID AND RN =3
WHERE  TLORDER.BILL_TO_CODE!='' AND (TLORDER.PICK_UP_BY_END>='9-12-2021')

赞(0）回复(0）举报 2022-11-07

我来回答

DB2 SQL -在子查询中获取特定的row_number值

1条答案

相关问题

热门标签

最新问答