我正在创建一个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