尝试在 DB2 SQL 中合并字段时出错

Question

我有一个查询，可以从我们的数据库中获取当前的库存水平。基本查询工作没有问题，但我需要在Ship_From字段中添加。问题是，如果我只是将其添加到选择查询中，它会导致每个位置出现重复的行(位置是它们之间唯一不同的内容(。我试图用这个参考和这个SO问题作为指导来弄清楚。不幸的是，我遇到了查询(下面(给我的错误：

"运行查询时出错。

非法使用关键字WITH，令牌QUERYNO具有组顺序相交的地方是预期的。

(SQL 代码 = -199，SQL 状态 = 42601(">

我正在使用 QMF for Windows V8.1 FP15(Unicode(来执行查询。唯一有重复项的字段是Ship_from，我想将这些值合并到一行中以删除重复的数据，如以下示例所示(删除一些列，因为它们除了Ship_From字段外都相同(：

当前：

SUPPLIER_NAME|CONTAINER_CODE|ALLOC_QTY|   SOH   | %_Fill|Ship_from|
Supplier A   |   Pallet     |   100   |   96    |  96%  |  IOSL1  |
Supplier A   |   Pallet     |   100   |   96    |  96%  |  IHMI1  |
Supplier A   |   Pallet     |   100   |   96    |  96%  |  IHMI2  |

期望：

SUPPLIER_NAME|CONTAINER_CODE|ALLOC_QTY|   SOH   | %_Fill|     Ship_from     |
Supplier A   |   Pallet     |   100   |   96    | 96%   |IOSL1, IHMI1, IHMI2|

SQL代码：

with X(Sup_No, Supplier_name, CONTAINER_CODE, Alloc_Qty, SAFE_STOCK_QTY, TOT_ALLOC_REQ_QTY, SOH, SIE, Disc_Qty, "%_Fill", Ship_from) as (
select distinct
ProcFlowPlus.Sup_No,
ProcFlowPlus.Supplier_name,
ProcFlowPlus.CONTAINER_CODE,
int(Allocation.ALLOC_QTY) as Alloc_Qty,
Allocation.SAFE_STOCK_QTY,
int(Allocation.TOT_ALLOC_REQ_QTY) as TOT_ALLOC_REQ_QTY,
BuckStat.CONTAINER_TYPE_QTY as SOH,
SIE_Bucket.SIE,
int((BuckStat.CONTAINER_TYPE_QTY + SIE_Bucket.SIE) - Allocation.TOT_ALLOC_REQ_QTY) as Disc_Qty,
concat (int((CONTAINER_TYPE_QTY + SIE_Bucket.SIE)/ Allocation.TOT_ALLOC_REQ_QTY * 100), '%') as "%_Fill",
ProcFlowPlus.PROC_FLO_DESC as Ship_from
from
(select distinct
ProcFlow.PROC_FLO_ID,
ContTrans.CONTAINER_TYPE_ID,
ContTrans.CONTAINER_CODE,
SupLookup.Sup_No,
SupName.Supplier_name,
ProcFlow.PROC_FLO_DESC
from 
(select distinct
ProcFlow.SUPPLIER_ID,
ProcFlow.PROC_FLO_ID,
ProcFlow.PROC_FLO_DESC,
ProcFlow.PROC_FLO_NAME
from rcx.RXPRF1 ProcFlow
where PLANT_ID = '50000036') as ProcFlow
left join (select distinct
SUPPLIER_ID,
SUPPLIER_NO concat SUPPLIER_LOCATION as Sup_No
from rcx.RXPIR1 Suplookup
where PLANT_ID = '50000036'
) as SupLookup on ProcFlow.SUPPLIER_ID = SupLookup.SUPPLIER_ID
left join (select distinct
SupInfo.Supplier,
SupInfo.Supplier_name,
suplookup.SUPPLIER_ID
from 
(select distinct
SUPPLIER_ID,
concat(SUPPLIER_NO, SUPPLIER_LOCATION) as Sup_Num
from rcx.RXPIR1
where COMPANY_CODE like('HMI%')
and not SUPPLIER_NO in ('JN9999','272180','506776','081583','504880')
) as SupLookup
left join rcx.RXSIV1 SupInfo on SupLookup.Sup_Num = SupInfo.SUPPLIER
where COMPANY_CODE like('HMI%')
) as SupName on SupLookup.SUPPLIER_ID = SupName.SUPPLIER_ID
left join rcx.RXPFC1 flowcont on ProcFlow.PROC_FLO_ID = flowcont.PROC_FLO_ID 
left join rcx.RXCTY1 ContTrans on flowcont.CONTAINER_TYPE_ID = ContTrans.CONTAINER_TYPE_ID
where not ProcFlow.PROC_FLO_NAME like '%RPR%'
and (right(ProcFlow.PROC_FLO_DESC, 5) like ('IHMI%') 
or right(ProcFlow.PROC_FLO_DESC, 5) like ('%IOSL%'))
) as ProcFlowPlus
left join (select distinct
SupLookup.Sup_Num,
Suplookup.CONTAINER_CODE,
AllocationType.ALLOC_TYPE_DESC,
Allocation.ALLOC_QTY,
Allocation.SAFE_STOCK_QTY,
Allocation.ALLOC_QTY + Allocation.SAFE_STOCK_QTY as TOT_ALLOC_REQ_QTY
from RCX.RXSAL1 Allocation
inner join rcx.RXALT1 AllocationType on Allocation.ALLOC_TYPE_ID = AllocationType.ALLOC_TYPE_ID
left join (select distinct
SUPPLIER_ID,
concat(SUPPLIER_NO, SUPPLIER_LOCATION) as Sup_Num,
CONTAINER_CODE,
CONTAINER_TYPE_ID
from rcx.RXPIR1    
where PLANT_ID= '50000036'
) as SupLookup on Allocation.SUPPLIER_ID = suplookup.SUPPLIER_ID and allocation.CONTAINER_TYPE_ID = suplookup.CONTAINER_TYPE_ID
where Allocation.PLANT_ID= '50000036'
) as Allocation on ProcFlowPlus.Sup_No = Allocation.Sup_Num and ProcFlowPlus.CONTAINER_CODE = Allocation.CONTAINER_CODE
left join (select distinct
ProcFlowPlus.Sup_No,
ProcFlowPlus.CONTAINER_CODE,
BuckStat.CONTAINER_TYPE_QTY as SIE
from (select distinct
ProcFlow.PROC_FLO_ID,
ContTrans.CONTAINER_TYPE_ID,
ContTrans.CONTAINER_CODE,
SupLookup.Sup_No,
ProcFlow.PROC_FLO_DESC
from (select distinct
ProcFlow.SUPPLIER_ID,
ProcFlow.PROC_FLO_ID,
ProcFlow.PROC_FLO_DESC,
ProcFlow.PROC_FLO_NAME
from rcx.RXPRF1 ProcFlow
where PLANT_ID = '50000036') as ProcFlow
left join (select distinct
SUPPLIER_ID,
SUPPLIER_NO concat SUPPLIER_LOCATION as Sup_No
from rcx.RXPIR1 Suplookup
where PLANT_ID = '50000036'
) as SupLookup on ProcFlow.SUPPLIER_ID = SupLookup.SUPPLIER_ID
left join (select distinct
SupInfo.Supplier,
suplookup.SUPPLIER_ID
from (select distinct
SUPPLIER_ID,
concat(SUPPLIER_NO, SUPPLIER_LOCATION) as Sup_Num
from rcx.RXPIR1
where COMPANY_CODE like('HMI%')
and not SUPPLIER_NO in ('JN9999','272180','506776','081583','504880')
) as SupLookup
left join rcx.RXSIV1 SupInfo on SupLookup.Sup_Num = SupInfo.SUPPLIER
where COMPANY_CODE like('HMI%')
) as SupName on SupLookup.SUPPLIER_ID = SupName.SUPPLIER_ID
left join rcx.RXPFC1 flowcont on ProcFlow.PROC_FLO_ID = flowcont.PROC_FLO_ID 
left join rcx.RXCTY1 ContTrans on flowcont.CONTAINER_TYPE_ID = ContTrans.CONTAINER_TYPE_ID
where not ProcFlow.PROC_FLO_NAME like '%RPR%'
and (right(ProcFlow.PROC_FLO_DESC, 5) like ('IHMI%') or right(ProcFlow.PROC_FLO_DESC, 5) like ('%IOSL%'))) as ProcFlowPlus
left join rcx.RXRLN1 buckets on ProcFlowPlus.PROC_FLO_ID = buckets.PROC_FLO_ID
left join rcx.RXBKT1 BucketNames on buckets.TO_BUCKET_ID = BucketNames.BUCKET_ID
left join rcx.RXBTC1 BuckStat on buckets.TO_BUCKET_ID = BuckStat.BUCKET_ID
where ProcFlowPlus.Sup_No is not null
and BuckStat.CONTAINER_TYPE_ID = ProcFlowPlus.CONTAINER_TYPE_ID
and BucketNames.BUCKET_NAME like '%SIE%'
) as SIE_Bucket on ProcFlowPlus.Sup_No = SIE_Bucket.Sup_No and ProcFlowPlus.CONTAINER_CODE = SIE_Bucket.CONTAINER_CODE
left join rcx.RXRLN1 buckets on ProcFlowPlus.PROC_FLO_ID = buckets.PROC_FLO_ID
left join rcx.RXBKT1 BucketNames on buckets.TO_BUCKET_ID = BucketNames.BUCKET_ID
left join rcx.RXBTC1 BuckStat on buckets.TO_BUCKET_ID = BuckStat.BUCKET_ID
where ProcFlowPlus.Supplier_name is not null
and BuckStat.CONTAINER_TYPE_ID = ProcFlowPlus.CONTAINER_TYPE_ID
and BucketNames.BUCKET_NAME like '%SOH%'
and Allocation.ALLOC_QTY is not null
order by 
ProcFlowPlus.Sup_No asC,
ProcFlowPlus.CONTAINER_CODE asc
) 
select Sup_No, Supplier_name, CONTAINER_CODE, Alloc_Qty, SAFE_STOCK_QTY, TOT_ALLOC_REQ_QTY, SOH, SIE, Disc_Qty, "%_Fill", 
LISTAGG(Ship_from, ', ') within group (order by Ship_from) as Ship_from
from X
group by Sup_No, Supplier_name, CONTAINER_CODE, Alloc_Qty, SAFE_STOCK_QTY, TOT_ALLOC_REQ_QTY, SOH, SIE, Disc_Qty, "%_Fill"

---

-编辑-

更新了代码以按照 @P.Vernon 的建议使用 LISTAGG 函数。这会导致 LISTAGGWithin Group子句中的关键字Group(非法使用关键字(出错。如果我删除 LISTAGG 函数并将其替换为仅Ship_from或完全删除Ship_from，则查询将按预期运行。

Answer 1

使用 LISTAGG 而不是递归来聚合字符串。 例如

with X(Sup_No, Supplier_name, CONTAINER_CODE, Alloc_Qty, SAFE_STOCK_QTY, TOT_ALLOC_REQ_QTY, SOH, SIE, Disc_Qty, "%_Fill", Ship_from, curr, prev) as 
( values (1,1,1,1,1,1,1,1,1,1,'IOSL1',1,1)
,(1,1,1,1,1,1,1,1,1,1,'IHMI1',1,1)
,(1,1,1,1,1,1,1,1,1,1,'IHMI2',1,1))
select Sup_No, Supplier_name, CONTAINER_CODE, Alloc_Qty, SAFE_STOCK_QTY, TOT_ALLOC_REQ_QTY, SOH, SIE, Disc_Qty, "%_Fill"
,      LISTAGG(Ship_from, ', ') within group (order by Ship_from DESC) as Ship_from
from X
group by Sup_No, Supplier_name, CONTAINER_CODE, Alloc_Qty, SAFE_STOCK_QTY, TOT_ALLOC_REQ_QTY, SOH, SIE, Disc_Qty, "%_Fill"

会回来

SUP_NO    SUPPLIER_NAME   CONTAINER_CODE  ALLOC_QTY   SAFE_STOCK_QTY  TOT_ALLOC_REQ_QTY   SOH     SIE     DISC_QTY    %_Fill  SHIP_FROM
------     -------------   --------------  ---------   --------------  -----------------   ---     ---     --------    ------  -------------------
1                 1                1          1                1                  1     1       1            1         1  IOSL1, IHMI2, IHMI1